Tuesday 17 July 2012

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

3 comments:

  1. Can you make a copy that will pull Service requests as well, please?

    ReplyDelete
  2. Alright, here you go click on this , created a new one for service requests

    http://sp-vinod.blogspot.co.uk/2012/12/daily-service-requests-report-scsm-2012.html

    ReplyDelete