Microsoft System Center Service Manager 2012 has a console that allows Helpdesk operators to organize and navigate incidents and other work items. SCSM also has an API that allows to enumerate all incidents/open items. You could also just query SQL server database directly, though that isn’t recommended unless you are just reading data and doing it in a way that does not place locks on table rows.
Using T-SQL to List All Open SCSM Incidents
SQL method happens to be the simplest one. Just open up SQL Server Management Studio and run this query in the New Query window:
USE ServiceManager GO SELECT S.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS ID , S.DisplayName AS Alert , E.EnumTypeName AS Status , S.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 FROM MT_System$WorkItem$Incident (NOLOCK) S JOIN EnumType (NOLOCK) E ON S.Status_785407A9_729D_3A74_A383_575DB0CD50ED = E.EnumTypeId WHERE E.EnumTypeName NOT IN ('IncidentStatusEnum.Resolved', 'IncidentStatusEnum.Closed') GO
Note the (nolock) hints – my approach is to make sure that I am not locking the table for reading while my query is running. Depending on the circumstances, SQL server could take some number of seconds to return results here, so you want to make sure that SCSM itself has no issues writing to the same table while your query is running. This may produce less than 100% reliable results on the reading side (if you can’t lock a row and try to read it while it is being written to, you probably won’t get the latest version of the row and so your results may not be accurate), but at least this will ensure that SCSM itself isn’t affected by some extraneous queries running in the Service Manager database.
Using SDK to List All Open SCSM Incidents
The more elegant way to get at the incident data is through SCSM API. Every SCSM installation comes with SDK libraries, by default located in C:\Program Files\Microsoft System Center\Service Manager 2012\SDK Binaries folder. Grab the three assemblies, reference from your Visual Studio project, and you are set:
Dim cr As New EnterpriseManagementObjectCriteria("DisplayName <> ''", classIncident) Dim reader As IObjectReader(Of EnterpriseManagementObject) = mg.EntityObjects.GetObjectReader(Of EnterpriseManagementObject)(cr, ObjectQueryOptions.Default) For Each emo As EnterpriseManagementObject In reader If emo(classIncident, "Status").Value.id <> enumStatusClosed.Id And emo(classIncident, "Status").Value.id <> enumStatusResolved.Id Then 'active incident of class "SCOMIncident" End If Next
That’s all there is to it, however, there is a lot of homework leading up to this simple code snippet. First we need to connect to a management group, then we need to retrieve the GUID of the management pack that contains SCOMIncident object class (which is an extension of the standard SCSM incident class with a dozen or so SCOM-specific fields – more on this in another post at a later time), then you need to retrieve the GUID of the incident object class itself. And finally, if you care to filter out incidents with Closed and Resolved status, like we did in the short and sweet SQL query above, we’ll need to enumerate objects of status class and obtain GUIDs of the statuses we wish to eliminate from the result set…
There are two ways to get the GUIDs of pretty much any object in SCSM. OK maybe three ways, depending on what you are looking for.
- knowing the object name you are after, and the table where it is stored in SCSM SQL, you could query SQL directly to obtain the GUID you are looking for. This is sometimes simpler then the alternatives but kind of contradictory to the SDK approach
- using the API, you can call GetManagementPack, EntityTypes.GetEnumeration, EntityTypes.GetClass, and EntityObjects.GetObject methods of the ManagementGroup object to get the relevant GUIDs without using direct SQL
- knowing the management pack where a class or enumeration is defined, you can export the management pack and open the resulting XML file in notepad – and get at the GUIDs that way. This method wouldn’t work for objects though, as unlike classes, objects are instances of particular types of data and are not stored in management packs
The next few posts will go into the details of connecting to the SCSM management group and getting the GUIDs we need to make this code above work.