The company I work for uses Manage Engines Service Desk Plus for it’s ticketing system. It is great product and has some good inbuilt reporting features, however for my needs I use a suite of SQL queries to pull data out into another reporting tool to create a dashboard.
I though I would post my queries in case they help someone else out.
Query 1 – Inbound requests by priority.
Shows: A list of priorities and the number of requests raised against them.
Filtered by: Only shows requests between the start_date and end_date. Only shows requests raised in the groups “ServiceDesk”, “3rd Line Request Queue” and “Development”
DECLARE @start_date as datetime DECLARE @end_date as datetime SET @start_date = '2016-07-01 00:00:01' SET @end_date = '2016-07-31 23:59:59' SELECT PriorityDefinition.PRIORITYNAME, count(*) as 'Total Inbound' FROM WorkOrder LEFT JOIN WorkOrder_Queue ON WorkOrder.WORKORDERID = WorkOrder_Queue.WORKORDERID LEFT JOIN QueueDefinition ON WorkOrder_Queue.QUEUEID = QueueDefinition.QUEUEID LEFT JOIN WorkOrderStates ON WorkOrder.WORKORDERID = WorkOrderStates.WORKORDERID LEFT JOIN PriorityDefinition ON WorkOrderStates.PRIORITYID = PriorityDefinition.PRIORITYID WHERE DATEADD(SECOND, WorkOrder.CREATEDTIME / 1000, '1970-01-01') between @start_date and @end_date AND ( QueueDefinition.QUEUENAME = 'ServiceDesk' OR QueueDefinition.QUEUENAME = '3rd Line Request Queue' OR QueueDefinition.QUEUENAME = 'Development' ) GROUP BY PriorityDefinition.PRIORITYNAME
Query 2 – Closed and resolved requests
Shows: Returns the number of requests closed or resolved.
Filtered by: Only shows requests between the start_date and end_date. Only shows requests raised in the groups “ServiceDesk”, “3rd Line Request Queue” and “Development”
DECLARE @start_date as datetime DECLARE @end_date as datetime SET @start_date = '2016-07-01 00:00:01' SET @end_date = '2016-07-31 23:59:59' SELECT count(*) as 'Completed or Resolved' FROM WorkOrder LEFT JOIN WorkOrder_Queue ON WorkOrder.WORKORDERID = WorkOrder_Queue.WORKORDERID LEFT JOIN QueueDefinition ON WorkOrder_Queue.QUEUEID = QueueDefinition.QUEUEID WHERE ( DATEADD(SECOND, WorkOrder.COMPLETEDTIME / 1000, '1970-01-01') between @start_date and @end_date OR DATEADD(SECOND, WorkOrder.RESOLVEDTIME / 1000, '1970-01-01') between @start_date and @end_date ) AND ( QueueDefinition.QUEUENAME = 'ServiceDesk' OR QueueDefinition.QUEUENAME = '3rd Line Request Queue' OR QueueDefinition.QUEUENAME = 'Development' )
Query 3 -Inbound requests by mode.
Shows: A list of modes and the number of requests raised against them.
Filtered by: Only shows requests between the start_date and end_date. Only shows requests raised in the groups “ServiceDesk”, “3rd Line Request Queue” and “Development”
DECLARE @start_date as datetime DECLARE @end_date as datetime SET @start_date = '2016-07-01 00:00:01' SET @end_date = '2016-07-31 23:59:59' SELECT ModeDefinition.MODENAME, count(*) as 'Total Inbound' FROM WorkOrder LEFT JOIN WorkOrder_Queue ON WorkOrder.WORKORDERID = WorkOrder_Queue.WORKORDERID LEFT JOIN QueueDefinition ON WorkOrder_Queue.QUEUEID = QueueDefinition.QUEUEID LEFT JOIN ModeDefinition ON WorkOrder.MODEID = ModeDefinition.MODEID WHERE DATEADD(SECOND, WorkOrder.CREATEDTIME / 1000, '1970-01-01') between @start_date and @end_date AND ( QueueDefinition.QUEUENAME = 'ServiceDesk' OR QueueDefinition.QUEUENAME = '3rd Line Request Queue' OR QueueDefinition.QUEUENAME = 'Development' ) GROUP BY ModeDefinition.MODENAME
Query 4 – Closed and resolved requests by technicians
Shows: Returns a list of technicians and the number of requests closed or resolved.
Filtered by: Only shows requests between the start_date and end_date. Only shows requests raised in the groups “ServiceDesk”, “3rd Line Request Queue” and “Development”
DECLARE @start_date as datetime DECLARE @end_date as datetime SET @start_date = '2016-07-01 00:00:01' SET @end_date = '2016-07-31 23:59:59' SELECT SDUser.LASTNAME, count(*) as 'Completed or Resolved' FROM WorkOrder LEFT JOIN WorkOrder_Queue ON WorkOrder.WORKORDERID = WorkOrder_Queue.WORKORDERID LEFT JOIN QueueDefinition ON WorkOrder_Queue.QUEUEID = QueueDefinition.QUEUEID LEFT JOIN WorkOrderStates ON WorkOrder.WORKORDERID = WorkOrderStates.WORKORDERID LEFT JOIN SDUser ON WorkOrderStates.OWNERID = SDUser.USERID WHERE ( DATEADD(SECOND, WorkOrder.COMPLETEDTIME / 1000, '1970-01-01') between @start_date and @end_date OR DATEADD(SECOND, WorkOrder.RESOLVEDTIME / 1000, '1970-01-01') between @start_date and @end_date ) AND ( QueueDefinition.QUEUENAME = 'ServiceDesk' OR QueueDefinition.QUEUENAME = '3rd Line Request Queue' OR QueueDefinition.QUEUENAME = 'Development' ) GROUP BY SDUser.LASTNAME
Query 5 – Top departments raising requests
Shows: Returns the top 12 departments and the number of requests raised. Sums all other departments into “All Other Departments”
Filtered by: Only shows requests between the start_date and end_date. Only shows requests raised in the groups “ServiceDesk”, “3rd Line Request Queue” and “Development”
DECLARE @start_date as datetime;
DECLARE @end_date as datetime;
SET @start_date = '2016-06-01 00:00:01';
SET @end_date = '2016-06-30 23:59:59';
With TopItems As
(
SELECT
DepartmentDefinition.DEPTNAME as DEPTNAME,
count(WorkOrder.WORKORDERID) as TotalInbound,
ROW_NUMBER() OVER( ORDER BY count(WorkOrder.WORKORDERID) DESC ) As Num
FROM
WorkOrder
LEFT JOIN WorkOrder_Queue ON WorkOrder.WORKORDERID = WorkOrder_Queue.WORKORDERID
LEFT JOIN QueueDefinition ON WorkOrder_Queue.QUEUEID = QueueDefinition.QUEUEID
Left JOIN DepartmentDefinition ON WorkOrder.DEPTID = DepartmentDefinition.DEPTID
WHERE
DATEADD(SECOND, WorkOrder.CREATEDTIME / 1000, '1970-01-01') between @start_date and @end_date AND
(
QueueDefinition.QUEUENAME = 'ServiceDesk' OR
QueueDefinition.QUEUENAME = '3rd Line Request Queue' OR
QueueDefinition.QUEUENAME = 'Development'
)
GROUP BY DepartmentDefinition.DEPTNAME
)
Select num, DEPTNAME, TotalInbound
From TopItems
Where Num <= 12
Union ALL
Select 0, 'All Other Departments', Sum(TotalInbound)
From TopItems
Where Num > 12
ORDER BY TotalInbound DESC
Query 6 – Number of requests raised and total time spent by category
Shows: Returns the top 12 categories and the number of requests raised and the total time spent. Sums all other departments into “All Other Categories”
Filtered by: Only shows requests between the start_date and end_date. Only shows requests raised in the groups “ServiceDesk”, “3rd Line Request Queue” and “Development”
DECLARE @start_date as datetime;
DECLARE @end_date as datetime;
SET @start_date = '2016-07-01 00:00:01';
SET @end_date = '2016-07-31 23:59:59';
With TopItems As
(
SELECT
CONCAT("scd"."NAME",' ',"icd"."NAME") as "Category",
(sum("ct"."TIMESPENT") /1000) /60 AS "TimeSpent",
ROW_NUMBER() OVER( ORDER BY (sum("ct"."TIMESPENT") /1000) /60 DESC ) As Num
FROM "WorkOrder" "wo"
LEFT JOIN "WorkOrderToCharge" "wotoc" ON "wo"."WORKORDERID"="wotoc"."WORKORDERID"
LEFT JOIN "ChargesTable" "ct" ON "wotoc"."CHARGEID"="ct"."CHARGEID"
LEFT JOIN "SDUser" "rcti" ON "ct"."TECHNICIANID"="rcti"."USERID"
LEFT JOIN "AaaUser" "rctd" ON "rcti"."USERID"="rctd"."USER_ID"
LEFT JOIN "WorkOrderStates" "wos" ON "wo"."WORKORDERID"="wos"."WORKORDERID"
LEFT JOIN "SubCategoryDefinition" "scd" ON "wos"."SUBCATEGORYID"="scd"."SUBCATEGORYID"
LEFT JOIN "ItemDefinition" "icd" ON "wos"."ITEMID"="icd"."ITEMID"
LEFT JOIN "CategoryDefinition" "cd" ON "wos"."CATEGORYID"="cd"."CATEGORYID"
LEFT JOIN "WorkOrder_Queue" "woq" ON "wo"."WORKORDERID"="woq"."WORKORDERID"
LEFT JOIN "QueueDefinition" "qd" ON "woq"."QUEUEID"="qd"."QUEUEID"
WHERE
DATEADD(SECOND, wo.CREATEDTIME / 1000, '1970-01-01') between @start_date and @end_date AND
(
qd.QUEUENAME = 'ServiceDesk' OR
qd.QUEUENAME = '3rd Line Request Queue' OR
qd.QUEUENAME = 'Development'
) GROUP BY CONCAT("scd"."NAME",' ',"icd"."NAME")
)
Select num,Category,TimeSpent
From TopItems
Where Num <= 12
Union ALL
Select 0, 'All Other Categories', sum(TimeSpent)
From TopItems
Where Num > 12
ORDER BY TimeSpent DESC
Query 7 – Pivot of Mode vs Priority
Shows: Returns a pivot of the mode vs priority i.e. how many priority 3 requests were emailed rather than phoned.
Filtered by: Only shows requests between the start_date and end_date. Only shows requests raised in the groups “ServiceDesk”, “3rd Line Request Queue” and “Development”
DECLARE @start_date as datetime
DECLARE @end_date as datetime
SET @start_date = '2016-06-01 00:00:01'
SET @end_date = '2016-06-30 23:59:59'
SELECT *
FROM (
SELECT
ModeDefinition.MODENAME as MODENAME,
PriorityDefinition.PRIORITYNAME as PRIORITYNAME,
count(WorkOrder.WORKORDERID) as WORKORDERID
FROM
WorkOrder
LEFT JOIN WorkOrder_Queue ON WorkOrder.WORKORDERID = WorkOrder_Queue.WORKORDERID
LEFT JOIN QueueDefinition ON WorkOrder_Queue.QUEUEID = QueueDefinition.QUEUEID
LEFT JOIN ModeDefinition ON WorkOrder.MODEID = ModeDefinition.MODEID
LEFT JOIN WorkOrderStates ON WorkOrder.WORKORDERID = WorkOrderStates.WORKORDERID
LEFT JOIN PriorityDefinition ON WorkOrderStates.PRIORITYID = PriorityDefinition.PRIORITYID
WHERE
DATEADD(SECOND, WorkOrder.CREATEDTIME / 1000, '1970-01-01') between @start_date and @end_date AND
(
QueueDefinition.QUEUENAME = 'ServiceDesk' OR
QueueDefinition.QUEUENAME = '3rd Line Request Queue' OR
QueueDefinition.QUEUENAME = 'Development'
)
GROUP BY ModeDefinition.MODENAME, PriorityDefinition.PRIORITYNAME
) as s
PIVOT
(
SUM(WORKORDERID)
FOR [MODENAME] IN ([E-mail],[IT Internal],[Phone Call],[Skype],[Visitor])
) AS pvt
Query 8 – Queue overview
Shows: Returns a pivot of the number of open or on hold requests f each group.
Filtered by: Only shows requests which are not resolved or closed. Only shows requests raised in the groups “ServiceDesk”, “3rd Line Request Queue” and “Development”
SELECT *
FROM (
SELECT
QueueDefinition.QUEUENAME,
StatusDefinition.STATUSNAME,
count(*) as 'TotalInbound'
FROM
WorkOrder
LEFT JOIN WorkOrder_Queue ON WorkOrder.WORKORDERID = WorkOrder_Queue.WORKORDERID
LEFT JOIN QueueDefinition ON WorkOrder_Queue.QUEUEID = QueueDefinition.QUEUEID
LEFT JOIN WorkOrderStates ON WorkOrder.WORKORDERID = WorkOrderStates.WORKORDERID
LEFT JOIN StatusDefinition ON WorkOrderStates.STATUSID = StatusDefinition.STATUSID
WHERE
StatusDefinition.STATUSNAME <> 'Closed' AND StatusDefinition.STATUSNAME <> 'Resolved' AND
(
QueueDefinition.QUEUENAME = 'ServiceDesk' OR
QueueDefinition.QUEUENAME = '3rd Line Request Queue' OR
QueueDefinition.QUEUENAME = 'Development'
)
GROUP BY QueueDefinition.QUEUENAME, StatusDefinition.STATUSNAME
) as s
PIVOT
(
SUM(TotalInbound)
FOR [STATUSNAME] IN ([Open],[OnHold])
) AS pvt
Query 9 – Technician overview
Shows: Returns the number of resolved or closed requests by technician
Filtered by: Only shows requests which are resolved or closed. Only shows requests raised in the groups “ServiceDesk”, “3rd Line Request Queue” and “Development”
SELECT *
FROM (
SELECT
SDUser.LASTNAME,
StatusDefinition.STATUSNAME,
count(*) as 'Count'
FROM
WorkOrder
LEFT JOIN WorkOrder_Queue ON WorkOrder.WORKORDERID = WorkOrder_Queue.WORKORDERID
LEFT JOIN QueueDefinition ON WorkOrder_Queue.QUEUEID = QueueDefinition.QUEUEID
LEFT JOIN WorkOrderStates ON WorkOrder.WORKORDERID = WorkOrderStates.WORKORDERID
LEFT JOIN StatusDefinition ON WorkOrderStates.STATUSID = StatusDefinition.STATUSID
LEFT JOIN SDUser ON WorkOrderStates.OWNERID = SDUser.USERID
WHERE
StatusDefinition.STATUSNAME <> 'Closed' AND StatusDefinition.STATUSNAME <> 'Resolved' AND
(
QueueDefinition.QUEUENAME = 'ServiceDesk' OR
QueueDefinition.QUEUENAME = '3rd Line Request Queue' OR
QueueDefinition.QUEUENAME = 'Development'
)
GROUP BY SDUser.LASTNAME, StatusDefinition.STATUSNAME
) as s
PIVOT
(
SUM(Count)
FOR [STATUSNAME] IN ([Open],[OnHold])
) AS pvt
Query 10 – Oldest requests for a specified group
Shows: Returns the top oldest requests for a specified group
Filtered by: Only shows requests which are not resolved or closed. Only shows requests raised in the group “ServiceDesk”
declare @today smalldatetime = getdate() SELECT Top(11) DATEADD(SECOND, WorkOrder.CREATEDTIME / 1000, '1970-01-01') as 'Created', DATEDIFF(DAY,DATEADD(SECOND, WorkOrder.CREATEDTIME / 1000, '1970-01-01'),@today) as 'Age (Days)', DATEDIFF(DAY,DATEADD(SECOND, WorkOrderStates.LAST_TECH_UPDATE / 1000, '1970-01-01'),@today) as 'Last Updated (Days)', WorkOrder.TITLE as 'Subject', CONCAT(SDUser.FIRSTNAME, ' ', SDUser.LASTNAME) as 'Technician', WorkOrder.WORKORDERID as 'Request ID' FROM WorkOrder LEFT JOIN WorkOrder_Queue ON WorkOrder.WORKORDERID = WorkOrder_Queue.WORKORDERID LEFT JOIN QueueDefinition ON WorkOrder_Queue.QUEUEID = QueueDefinition.QUEUEID LEFT JOIN WorkOrderStates ON WorkOrder.WORKORDERID = WorkOrderStates.WORKORDERID LEFT JOIN StatusDefinition ON WorkOrderStates.STATUSID = StatusDefinition.STATUSID LEFT JOIN SDUser ON WorkOrderStates.OWNERID = SDUser.USERID WHERE StatusDefinition.STATUSNAME <> 'Closed' AND StatusDefinition.STATUSNAME <> 'Resolved' AND QueueDefinition.QUEUENAME = 'ServiceDesk' ORDER BY WorkOrder.CREATEDTIME ASC
Queue 11 – Survey results by group
Shows: Returns the average survey requests by group
Filtered by: Only shows survey requests created between the start_date and end_date. Only shows survey results raised in the groups “ServiceDesk”, “3rd Line Request Queue” and “Development”
DECLARE @start_date as datetime DECLARE @end_date as datetime SET @start_date = '2016-08-01 00:00:01' SET @end_date = '2016-08-31 23:59:59' SELECT QueueDefinition.QUEUENAME, COUNT(SurveyPerRequestExt.SURVEYID) as 'Number of Suveys', CONVERT(DECIMAL(10,1),(AVG(SurveyPerRequestExt.OVERALLRATING) / 30) * 100) as Average FROM SurveyPerRequestExt LEFT JOIN SurveyPerRequest ON SurveyPerRequestExt.SURVEYID = SurveyPerRequest.SURVEYID LEFT JOIN WorkOrderStates ON SurveyPerRequest.WORKORDERID = WorkOrderStates.WORKORDERID LEFT JOIN WorkOrder_Queue ON SurveyPerRequest.WORKORDERID = WorkOrder_Queue.WORKORDERID LEFT JOIN QueueDefinition ON WorkOrder_Queue.QUEUEID = QueueDefinition.QUEUEID WHERE DATEADD(SECOND, SurveyPerRequestExt.CREATEDTIME / 1000, '1970-01-01') between @start_date and @end_date AND ( QueueDefinition.QUEUENAME = 'ServiceDesk' OR QueueDefinition.QUEUENAME = '3rd Line Request Queue' OR QueueDefinition.QUEUENAME = 'Development' ) GROUP BY QueueDefinition.QUEUENAME
Query 12 – Survey results by technician
Shows: Returns the average survey requests by technician
Filtered by: Only shows survey requests created between the start_date and end_date.
DECLARE @start_date as datetime DECLARE @end_date as datetime SET @start_date = '2016-06-01 00:00:01' SET @end_date = '2016-06-30 23:59:59' SELECT SDUser.LASTNAME, COUNT(SurveyPerRequestExt.SURVEYID) as 'Nuber of Suveys', CONVERT(DECIMAL(10,1),(AVG(SurveyPerRequestExt.OVERALLRATING) / 30) * 100) as Average FROM SurveyPerRequestExt LEFT JOIN SurveyPerRequest ON SurveyPerRequestExt.SURVEYID = SurveyPerRequest.SURVEYID LEFT JOIN WorkOrderStates ON SurveyPerRequest.WORKORDERID = WorkOrderStates.WORKORDERID LEFT JOIN SDUser ON WorkOrderStates.OWNERID = SDUser.USERID WHERE DATEADD(SECOND, CREATEDTIME / 1000, '1970-01-01') between @start_date and @end_date GROUP BY SDUser.LASTNAME
thank you!
Really helpful queries, they gave me understanding of the structure of SDP database 🙂
Thanks for the comment, I am glad you found them of use.
Phil
This is helpful!!! I’m looking to get data for producing reports for projects. Any samples to share?
Thank you.
Could you write a query to show all business rules and all criteria and facets of the rule?