When considering what Windows event logs to incorporate into a SIEM solution, should I be looking at just the Security event logs, or all categories of event log? How useful are the other categories in detecting and responding to security incidents?
It depends on what kind of event you're considering an incident. There is information you can use to report against realtime (connecting USB devices for instance) that won't necessarily report in the security log. Further service restarts and other relevant events that could be related to security events. Logs for antivirus, and firewalls can be sent as well if you're using the right kind of forwarding services.
You should be able to gather much of this from all different categories of event logs. I tend not to focus on a single location as you can quickly find you've got blinders on.
In this day and age most commercial SIEM/SEIM/SIM/SEM/SEEM/CSIM/SCIM/SIM products ship with a large multitude of so called "canned reports". In general each of these reports will be designed to look at specific logs from specific types of systems and process those. For example, given a "Windows - Failed Authentication Attemps" report, one would assume that the EventIDs that map back to authentication events would be processed looking for FAILURE. Whereas a "Windows - Firewall Blocks" would look for EventIDs that map back to the firewall subsystem would be processed.
The real questions you should be asking yourself are these;
The answer to question #1 is really all about framing the situation in your own head. So you can keep it in mind when thinking about the other 2. Once you have answered those questions you can look at the SIEM product you have and determine, "What data is required to produce that information?"
Keep in mind that his can also vary wildly depending on the specific log sources as well. For instance, you may decide that a web server that serves nothing except the contact information for a department only needs to send authentication and IIS logs, whereas you may want all logs from your domain controllers. This can also be greatly influenced by any licensing restrictions as many commercial products will license based on number of log sources and/or events per second (EPS).
Unfortunately, there is no short, clear cut answer. It all depends on what you want, and what your solution is capable of.