SCSM Query Operations Database for Report Data: Total Incidents by Category

Author by Nathan Lasnoski

I find sometimes that I am wanting to get data out of the Service Manager operational database instead of having to go to the data warehouse.  This is usually for metrics and dashboards which need to be displayed more rapidly than the transfer and transform is going to be performed.  You can see below an example of a report which brings back the total incidents by category over a period of 30 days directly from the operations database using a SQL query.  This can be used through reporting services as well.   In this case you'll notice a few interesting things. I'm using Aliases (IncidentClassification.DisplayName As Display_Name), etc.  The reason for this is because if we don't use aliases the relationship table joins will cause duplicate names to be returned and we'll receive an error.   I'm using Inner Joins.  This is because in this example I'm interested in only those incidents with all of these fields populated, vs. the ones which are missing a paticular field.   I'm pulling only resolved or closed incidents, as well as filtering on english.  You can alter the report to bring back anything you like.   I'm reporting on average days open.  In some cases people like to switch this to hours.   Here is the query: SELECT IncidentClassification.DisplayName As Display_Name ,COUNT(*) As Total ,AVG([Priority_B930B964_A1C4_0B5A_B2D1_BFBE9ECDC794]) AS Average_Priority ,CONVERT(Decimal(10,2), (AVG (CONVERT(Decimal(10,2), DATEDIFF (HOUR, [CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688], [ResolvedDate_D2A4C73F_01B8_29C5_895B_5BE4C3DFAC4E])))) / 24) As  Average_Days_Open FROM [ServiceManager].[dbo].[MT_System$WorkItem$Incident]   INNER JOIN [ServiceManager].[dbo].[Relationship] AssignedToUserRel ON [ServiceManager].[dbo].[MT_System$WorkItem$Incident].[BaseManagedEntityId] = AssignedToUserRel.[SourceEntityId] AND AssignedToUserRel.[RelationshipTypeId] = '15E577A3-6BF9-6713-4EAC-BA5A5B7C4722' INNER JOIN [ServiceManager].[dbo].[MT_System$Domain$User] AssignedToUser ON AssignedToUserRel.[TargetEntityId] = AssignedToUser.[BaseManagedEntityId]   INNER JOIN [ServiceManager].[dbo].[Relationship] AffectedUserRel ON [ServiceManager].[dbo].[MT_System$WorkItem$Incident].[BaseManagedEntityId] = AffectedUserRel.[SourceEntityId] AND AffectedUserRel.[RelationshipTypeId] = 'DFF9BE66-38B0-B6D6-6144-A412A3EBD4CE' INNER JOIN [ServiceManager].[dbo].[MT_System$Domain$User] AffectedUser ON AffectedUserRel.[TargetEntityId] = AffectedUser.[BaseManagedEntityId]   INNER JOIN [ServiceManager].[dbo].[DisplayStringView] IncidentClassification ON [ServiceManager].[dbo].[MT_System$WorkItem$Incident].[Classification_00B528BF_FB8F_2ED4_2434_5DF2966EA5FA] = IncidentClassification.LTStringId AND IncidentClassification.LanguageCode = 'ENU'   INNER JOIN [ServiceManager].[dbo].[DisplayStringView] IncidentStatus ON [ServiceManager].[dbo].[MT_System$WorkItem$Incident].[Status_785407A9_729D_3A74_A383_575DB0CD50ED] = IncidentStatus.LTStringId AND IncidentStatus.LanguageCode = 'ENU' AND IncidentStatus.DisplayName = 'Closed'   OR  [ServiceManager].[dbo].[MT_System$WorkItem$Incident].[Status_785407A9_729D_3A74_A383_575DB0CD50ED] = IncidentStatus.LTStringId AND IncidentStatus.LanguageCode = 'ENU' AND IncidentStatus.DisplayName = 'Resolved' WHERE DATEDIFF (DAY, [CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688], CURRENT_TIMESTAMP) < 30   GROUP BY IncidentClassification.[DisplayName] ORDER BY Total DESC   I hope this helps you to return information you are interested in from the operations database!   Nathan Lasnoski

Nathan Lasnoski

Chief Technology Officer