Daily Report by Incidents SCSM 2012

Daily report is something which is used by service delivery managers and there is no out of box reports in SCSM to process this.

Below SQL query will give you the count of following incidents assigned to Support Engineer:

·         Active

·         Pending

·         Resolved

·         Closed

Run this against DWDataMart database as per Microsoft’s suggest no query should be run directly on a transactional database in this case it ServerManager Database.

Select

 distinct userdim.DisplayName as AssignedToUser , count ( case incident.status

when 'IncidentStatusEnum.Active' THEN 'ACTIVE' end )  as Active,

 count ( case incident.status

when 'IncidentStatusEnum.Closed' THEN 'Closed' end )  as Closed,

Count ( case incident.status

when 'IncidentStatusEnum.Resolved' THEN 'Resolved' end )  as Resolved

From    IncidentDim incident JOIN   

WorkItemDim workitem on incident.EntityDimKey = workItem.EntityDimKey JOIN  

 WorkItemAssignedToUserFactvw assignedtouser

on workitem.WorkItemDimKey = assignedtouser.WorkItemDimKey JOIN   

UserDimvw userdim on assignedtouser.WorkItemAssignedToUser_UserDimKey = userdim.UserDimKey

 Where    assignedtouser.DeletedDate is null and incident.createddate>= CONVERT(varchar(8), GETDATE()-1, 112)

 group by userdim.DisplayName, incident.status

Output:

Assigned To	Active	Closed	Resolved
Support Engineer A	2	0	0
Support Engineer B	1	0	0
Support Engineer C	1	0	0
Support Engineer D	2	0	0

 Please note this query will only pull info for Incidents but not Service or Change requests