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