Disclaimer. IBM Site Protector is proprietary, so I cannot answer specific questions about it. But I'll share my impression of network-based IDS/IPS systems, in general.
Effectiveness at detecting certain attacks. You asked about how effective IDSs are at detecting certain specific kinds of attacks:
Port scans. I'd expect IDS systems to be pretty effective at detecting most port scans. (It is a separate question whether it is useful to detect port scans.)
SQL scans. I'd expect IDS systems to be effective at detecting basic SQL injection attack attempts, e.g., where someone is poking at your server in a simple way. I would not expect them to be effective at detecting sophisticated SQL injection attacks. It is possible to build SQL injection exploits that evade detection by IDSs.
Credentials obtained. This is too broad; I don't know what path for obtaining credentials you had in mind, so I don't know how to answer it. If you are talking about attackers trying to get access to a web application by guessing usernames and passwords, I would not expect a network-based IDS to detect those attacks.
Installation of SSH. I don't know whether IDSs detect installation of SSH. I'm not clear on how they would distinguish an attacker who maliciously turns on a SSH server vs a sysadmin who turns on a SSH server for legitimate purposes. This seems like something you could test yourself easily.
Remote shell. I would not expect an IDS to be effective at detecting most remote shells. Perhaps sometimes, but once the attacker has the ability to run code on your machine, it is too easy for them to craft their own custom method for remote access that an IDS won't detect. It is possible that some common cases can be detected; I don't know, and don't have enough experience to know.
Other anomalous behavior. To the best of my knowledge, current network-based IDSs usually don't try to detect anomalies in general. Rather, it is more typical to have a signature of specific attack methods that have been observed in the wild. In other words, they try to detect known attacks, rather than unknown ones.
Detecting anomalies is quite challenging, and usually leads to many false positives. For instance, if you suddenly start seeing a lot more traffic somewhere, how do you distinguish the case where you just got Slashdotted (and this is legitimate traffic) from the case where you've had a security breach (and this is illegitimate traffic)? In the general case, it can get pretty tricky.
There's one more you didn't mention, but which I think is important:
- Configuration errors. Configuration mistakes are one significant source of security problems. Examples include running an old piece of software with a known vulnerability and failing to update it, or inadvertently opening a hole in the firewall that exposed a vulnerable service. This is the sort of mistake that an IDS can really help with, by detecting attempts to exploit the vulnerable/misconfigured application.
Additionally, after a security breach occurs, an IDS may make it easier to conduct a forensic examination of the damage done, if it keeps good logs.
Information shown in alerts. The last question you asked has to do with what information is shown in the alert. That's a different question from whether the IDS can detect the attack in the first place. I would guess that most users of IDSs are not in a position to usefully interpret all the technical details of an exploit, so for most users, showing those kinds of details might not be so useful. As far as what information is shown to the user in an alert, that's a user interface question that is going to be dependent upon the specific product, so if this is something that is important to you, I suggest that you ask for a trial of the products you are considering and evaluate this aspect.
Targeted attacks. A quick note: very little of the information above is specific to targeted attacks against your particular company or against a particular machine at your company. The answers for non-targeted attacks are roughly the same (though it is possible an IDS might be somewhat more effective for non-targeted attacks than for targeted attacks).
How useful is an IDS? I don't know if you're going to find an authoritative answer to this question. It is pretty subjective. Personally, I think it is not clear how useful an IDS is, and my impression is that IDSs are often overhyped. My feeling is that IDS's tend to be best at two things: (1) detecting some common, low-sophistication attacks, (2) detecting breaches caused by configuration errors.
I don't recommend IDSs as the primary means of defending your systems. However, there is a plausible argument IDSs might have some value as a secondary defense, e.g., to catch inadvertent configuration errors that open up some hole that your other defenses didn't stop. Whether the IDS makes sense for you is something you'll have to judge for yourself, but generally speaking, an IDS isn't one of the first few things I'd prioritize if I were creating a security program from scratch.
Why use an IDS? Your reaction at this point might be, wait, if IDSs are not so useful, why does anyone use them?
I think one major factor is: an IDS is easy to deploy. You just plug the thing into your network, and you're ready to go. You don't have to push software to your end users, you don't have to update firewall policies, and you don't have to deploy security restrictions that might block legitimate traffic. It feels like a no-pain solution to the security headache.
A possible secondary reason for deployment of IDSs/IPSs is: compliance and checklists. If you have an IDS, you can check it off with your auditor, and if there's a security breach, you'll have some CYA protection with your corporate executives (well, we deployed a state-of-the-art IDS, we did everything we could have). This exploits the fact that a non-technical audience is likely to assume that an Intrusion Prevention System is something that will reliably prevent intrusions.
A third possible reason is: IDSs produce reports, lots of reports, with graphs and pretty charts. Managers likes reports: it helps them feel like they can "manage" security and get a feeling for how they are doing. Also, sometimes security teams like these reports, too: they can be helpful in arguing to upper management why the company really needs to invest in security defenses (we had 10,000 attacks in January alone! we really need to put a firewall in place).