In this post, I am drilling into Microsoft System Center Service Manager 2012 and more specifically into how it stores Tier Queues in the ServiceManager database.
In my customer’s scenario we had to figure out a way to get this information not via the SCSM SDK API but directly from ServiceManager SQL database. Of course, exercise caution working with this database directly, never modify things, avoid long-running queries with locking, etc. (insert standard disclaimer here).
Support Group and Incident Tier Queue vs. Incident Queue
What we are looking for is an SCSM Incident Support Group – this is the name of the Incident form control that allows one to assign an incident to a support “group”. If you open the Incident Console form in SCSM authoring tool, the name of the field is shown as SupportGroup. Authoring tool shows that this control is bound to TierQueue field. The control is a list box of type Incident Tier Queue.
To add to the confusion, there are actual Incident Queues that one can define in the SCSM UI (Library -> Queues). Incident Queues are used to segment work items between different support groups and/or personnel… For clarity, Support Group and Tier Queue in this post refer to one and the same field on the Incident form, and not the Incident Queues.
How to Find SCSM Data
There are at least 4 different ways to get at this data:
- Using SCSM SDK API
- Using direct querying of the SQL database
- By exporting the associated management pack and searching its XML content
- Using SCSM PowerShell commands
Fundamentally, methods #1 and #4 (probably #4 more so than #1) use extra layers of abstraction and eventually query the same tables as #2. So #4 and #1 should typically be slower than #2. #3 isn’t really suited well to running automation or custom code but is useful in finding and figuring things out. For performance reasons we will cover method #2 here.
The following steps work somewhat broadly and make it easier to locate some other useful data in SCSM, not just Tier Queues.
Step 1: Query SCSM Work Items
Since we are working with the incidents, we’ll query the table that stores this class of objects:
USE ServiceManager SELECT * FROM MT_System$WorkItem$Incident (nolock)
The output will fetch the incidents from your SCSM system. One of the fields will bear resemblance to Tier Queue – in my case the field is named TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C. Find the incident you are looking for and make a note of the value of this TierQueue (it will be a GUID of course). If you have a somewhat large number of incidents in this table, you can filter similar to:
SELECT * FROM MT_System$WorkItem$Incident (nolock) WHERE DisplayName LIKE '%some_string_filter%'
Step 2: Query Enumeration Types
Tier Queue is not a relationship between two different classes of objects in SCSM, it is an enumeration that gets assigned directly as the incident’s tier queue attribute. You can confirm that this is the case by opening SCSM Authoring Tool, looking up Incident class, Support Group attribute:
(Relationship attributes aren’t really attributes, they are associations between two different objects which are maintained in a separate relationship table. These relationships are shown in the authoring tool as dark “reference arrows”).
Had this TierQueue attribute been a relationship, we’d have to take another approach, but since it is an attribute, let’s move on to querying enumerations:
SELECT * FROM EnumType (nolock) WHERE EnumTypeId = 'C7B66B52-3BCD-0968-C7FD-F4BA5D30CC88'
The EnumTypeId value comes from the first query and will be different from one SCSM installation to another, it is the GUID of the SCSM support group that get created in the SCSM UI.
The result of this query gives us EnumTypeName, which in my case is Enum.121dac980e324fce95deb18364ffe63d. Regrettably, this value (or the rest of the SQL query output) does not tell me the display name of the support group.
Step 3: Find Display Name
Next step is to query the LocalizedText table to find out the display name of the TierQueue enumeration we discovered in step 2:
SELECT * FROM LocalizedText (nolock) WHERE ElementName = 'Enum.121dac980e324fce95deb18364ffe63d'
This query returns LTValue field, which contains the display name we’ve been looking for.
Step 3: Alternate Method Using Management Pack XML
Another way to get this Tier Queue display name is to export the associated Management Pack into an XML file and Ctrl+F it for Enum.GUID enumeration name. This is less fun if you want to automate the process, but it can reveal additional information that may accelerate or verify what you are doing in the SQL tables.
Refer to step 2. The query output in that step also provides ManagementPackId GUID value of the management pack that stores this enumeration. All you need to do is query ManagementPack table to find out the name of the MP:
SELECT MPName, MPFriendlyName FROM ManagementPack (nolock) WHERE ManagementPackId ='B1207CE1-D1EE-CD35-65EA-F8B10BEBAC7D'
Now find this Management Pack in the SCSM UI and export it to an XML file. Search for the Enum.GUID value of the TierQueue enumeration and you will see its display name in the XML file.
Step 4: Display Incidents with Support Groups
To bring all queries above into a single SQL statement to show all incidents with support groups / Tier Queues:
SELECT I.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS [ID], I.DisplayName AS [Name], L.LTValue AS [Support Group] FROM MT_System$WorkItem$Incident I (nolock) INNER JOIN EnumType E (nolock) ON I.TierQueue_1E9615C2_3386_2452_BA83_05B2169DF38C = E.EnumTypeId INNER JOIN LocalizedText L (nolock) ON L.ElementName = E.EnumTypeName
If your SCSM installation uses multiple languages, you may have to do additional filtering to weed out potentially duplicate records, but this query gives the idea.