Friday 14 December 2012

Daily Service Requests Report SCSM 2012



Daily Service Requests 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 Service Requests assigned to Support Engineer's:

·         Active
·         Completed
·         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 ServiceRequest.status
when 'ServiceRequestStatusEnum.Active' THEN 'Active' end )  as Active,
 count ( case ServiceRequest.status
when 'ServiceRequestStatusEnum.Completed' THEN 'Completed' end )  as Completed,
Count ( case ServiceRequest.status
when 'ServiceRequestStatusEnum.Completed' THEN 'Closed' end )  as Closed
From    ServiceRequestDim ServiceRequest JOIN   
WorkItemDim workitem on ServiceRequest.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 ServiceRequest.createddate>= CONVERT(varchar(8), GETDATE()-1, 112)
 group by userdim.DisplayName

Output:



Assigned To
Active
Completed
Closed

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 Service requests

2 comments:

  1. when 'ServiceRequestStatusEnum.Closed' THEN 'Closed' end ?

    ReplyDelete
  2. Should reference views, not actual tables.

    ReplyDelete