My Manage Engine Service Desk Plus SQL Queries

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

4 thoughts on “My Manage Engine Service Desk Plus SQL Queries”

Leave a Reply to Phil Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.