I've worked with a lot of monitoring systems at a lot of places. Most of them have already been mentioned. Here are a few that haven't been:
SMARTS - now owned by EMC. Really is the best thing ever for root cause. It's not cheap and support may not be good anymore as it's owned by EMC. We were lucky enough to work with the founders of the company to get it implemented.
Big Brother. Nice and simple, but a bad license. It's also the ugliest web gui I've ever seen, so I had to rewrite it. Never got Big Sister to work.
HP Openview, when engineered, installed and run by a competent engineer can be good. However I've only seen it done right once and wrong more often than I can remember. I would never choose to use it.
BMC Patrol. Just awful. Die, die!
And finally, for logs and tracking down problems you just have to use Splunk. If this had been around 10 years ago I would have saved myself a lot of wasted time.