I think it's good fun. There is a lot of strategy to it. Counterstrike is "nothing but a mod" and it's one of the most played games online. I could see how you could get bored quick though. No firearm realism whatsoever though.... trying to think of a game I've played recently that DID have decent firearms representation though. Armed Assualt was good in that aspect, bullet drop and everything.
I am LOVING ArmA! I bought it for $15 a few months back and although it has some VERY irritating quirks(there's no reason for a modern "realistic" game to lack weapon animations, and some of the missions are still broken despite patches), it's still a blast to play.
I will be buying L4D this weekend, because one of my friends is hosting a LAN party. L4D and TrackMania are to be the "main" games played. L4D is fun, but in my opinion not worth $40(I'll still be buying it because I don't want to miss out on the fun). IMO if you're going to charge that much for a game, it'd better either have a storyline or be MASSIVE(i.e. Unreal Tournament). By comparison, Oblivion is still getting $40... and it's two years old. Anyway.
Lots of fun, though, to be the Infected and play against your friends. It's all the better when you're in the same room :D