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