Useful SQL Report Queries for SCSM

As a PFE I get asked to help create custom reports for SCSM, from time to time, for my customers. I’m going to use this blog post as an ongoing repository of the queries I use. These are to be used in SSRS reports targeted at the DWDataMart. Thanks to Travis Wright for a blog post that got me started on these!

Incidents By Support Group – Last 30 Days

SELECT Strings.DisplayName AS "Support Group", COUNT(*) AS Incidents
FROM IncidentDimvw Incident
Join IncidentTierQueuesvw Tier ON Incident.TierQueue_IncidentTierQueuesId = Tier.IncidentTierQueuesId
Join DisplayStringDimvw Strings ON Tier.EnumTypeId = Strings.BaseManagedEntityId
WHERE Strings.LanguageCode = 'ENU'
and Incident.CreatedDate > DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), dateadd(day, -30, getdate()))
GROUP BY Strings.DisplayName
ORDER BY Incidents DESC

Incidents By Classification (Top 10) – Last 30 Days

SELECT TOP (10) Strings.DisplayName AS Classification, COUNT(*) AS Incidents
FROM IncidentDimvw Incident
Join  IncidentClassificationvw Classification ON Incident.Classification_IncidentClassificationId = Classification.IncidentClassificationId
Join  DisplayStringDimvw Strings ON Classification.EnumTypeId = Strings.BaseManagedEntityId
WHERE Strings.LanguageCode = 'ENU'
and Incident.CreatedDate > DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), dateadd(day, -30, getdate()))
GROUP BY Strings.DisplayName
ORDER BY Incidents DESC

Incidents By Affected User (Top 15) – Last 30 Days

SELECT TOP (15) U.UserDimKey, U.DisplayName, WIAU.IncidentCount
FROM
( SELECT WIAU.WorkItemAffectedUser_UserDimKey, Count(*) AS IncidentCount
FROM WorkItemAffectedUserFactVw AS WIAU
where wiau.CreatedDate > DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), dateadd(day, -30, getdate()))
--and WIAU.WorkItemAffectedUser_UserDimKey != '3515'
--and WIAU.WorkItemAffectedUser_UserDimKey != '3516'
GROUP BY WIAU.WorkItemAffectedUser_UserDimKey

) AS WIAU
INNER JOIN UserDimVw AS U ON U.UserDimKey = WIAU.WorkItemAffectedUser_UserDimKey
--WHERE WIAU.IncidentCount > 2
ORDER BY WIAU.IncidentCount DESC

Incidents By Assigned To User – Last 30 Days

SELECT U.UserDimKey, U.DisplayName, WIAU.IncidentCount
FROM
( SELECT WIAU.WorkItemAssignedToUser_UserDimKey, Count(*) AS IncidentCount
FROM WorkItemAssignedToUserFactvw AS WIAU
where wiau.CreatedDate > DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), dateadd(day, -30, getdate()))
and wiau.DeletedDate is null
GROUP BY WIAU.WorkItemAssignedToUser_UserDimKey

) AS WIAU
INNER JOIN UserDimVw AS U ON U.UserDimKey = WIAU.WorkItemAssignedToUser_UserDimKey
--WHERE WIAU.IncidentCount > 2
ORDER BY WIAU.IncidentCount DESC

Comments

• Anonymous
  October 10, 2014
  This is very helpful, thank you for sharing!
• Anonymous
  October 10, 2014
  Indeed. keep up the great work and updated.
• Anonymous
  November 17, 2015
  This is a useful starting point in what seems to be an arid desert when it comes to developing scsm reports. Your last sample counts work items and labels as incidentcount - my testing has discovered that workitems are not exclusive in this view and your query returns the total of all items - Incidents, service Requests, Manual Activities etc. Useful info but wanted to highlight that its not just incidents to anyone else who takes the code.
• Anonymous
  December 16, 2015
  Could u please send us some SQL queries service request reports