I don't play CS, but I'm a big fan of HL and the mods surrounding it. You should be able to find a bot for Counter-Strike rather quickly, and you can start a LAN game and cut your teeth on those.
HL is a whole nother story however, it's a single-player masterpiece. Scary, well-thought out, and so much detail in the environment that you will freak.
As for the mods, Team Fortress Classic is a good pick, as is Deathmatch Classic--especially if you play Quake 1, a lot of the maps are in DMC, and they are BEAUTIFUL!!
Firearms looks fun, but I can't get it to work. The Specialists is great if you like Matrix and the like, but it is very hard to learn. I can beat the Hardcore level on the Quake III demo and I still get nailed by the MP croud.
Steam is only good for Broadband users, if you are stuck with 28.8, your one and only bet is GameSpy or LAN with other people in your house.
Another good link you must be aware of is
Planet Half-LifeLet me know how it goes!
Merlin