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