Came across another request for a custom report in ManageEngine ServiceDesk Plus the other day that Zoho support seemed to suggest wasn’t possible. I thought I’d take a look anyway, just to be sure, and seem to have found a solution.

Basically the client was after a report of incidents by Template Name with an output containing the following field information:

Incident Template Name, Max/Min/Ave Respond Time, Max/Min/Ave Close Time
Not sure why this was meant to be so difficult as both these measurements are available in a request. When a request has been successfully completed the ‘Completed Date’ is updated. ManageEngine also maintains a measure of the first time response for a request. So when a Technician sends an initial email to the Requester a first time response measurement is updated in the ‘Responded Date’ field.
 
 
 
 
 
 
Note that the ‘Responded Date’ filed is not seen if an initial email response has not been made. Please also note that this value is not set by any automatic notifications configured. In addition this value is only set for Incident Requests and not Service Requests.
 
In database terms we see these values in the ‘WorkOrder’ table as ‘CompletedTime’ and ‘RespondedTime’ as shown below (the diagram does not show all the elements of ‘WorkOrder’ data table:
 
 
The only other data we will need is the relevant template name the request has been raised under in ManageEngine ServiceDesk Plus. This is recorded in the ‘RequestTemplate_List’ data table and can easily be linked to the ‘WorkOrder’ data table by means of the ‘TemplateID’.
Here’s my stab at the custom report and an example output is given below:
Please note that the data only looks at closed Incidents with a valid first response time. I’ve also restricted the report to Incidents created in the current week but you can easily change this to suit your requirements.
Enjoy!
MS SQL Custom Report
SELECT (rtl.templatename) “Request Template”,
MAX(DATEDIFF(minute, dateadd(s,datediff(s,getutcdate(),getdate())+(wo.createdtime/1000),’1970-01-01 00:00:00′), dateadd(s,datediff(s,getutcdate(),getdate())+(wo.completedtime/1000),’1970-01-01 00:00:00′))) as “Max Minutes to Complete (min)”,
MIN(DATEDIFF(minute, dateadd(s,datediff(s,getutcdate(),getdate())+(wo.createdtime/1000),’1970-01-01 00:00:00′), dateadd(s,datediff(s,getutcdate(),getdate())+(wo.completedtime/1000),’1970-01-01 00:00:00′))) as “Min Minutes to Complete (min)”,
AVG(DATEDIFF(minute, dateadd(s,datediff(s,getutcdate(),getdate())+(wo.createdtime/1000),’1970-01-01 00:00:00′), dateadd(s,datediff(s,getutcdate(),getdate())+(wo.completedtime/1000),’1970-01-01 00:00:00′))) as “Ave Minutes to Complete (min)”,
MAX(DATEDIFF(minute, dateadd(s,datediff(s,getutcdate(),getdate())+(wo.createdtime/1000),’1970-01-01 00:00:00′), dateadd(s,datediff(s,getutcdate(),getdate())+(wo.respondedtime/1000),’1970-01-01 00:00:00′))) as “Max Minutes to Respond (min)”,
MIN(DATEDIFF(minute, dateadd(s,datediff(s,getutcdate(),getdate())+(wo.createdtime/1000),’1970-01-01 00:00:00′), dateadd(s,datediff(s,getutcdate(),getdate())+(wo.respondedtime/1000),’1970-01-01 00:00:00′))) as “Min Minutes to Respond (min)”,
AVG(DATEDIFF(minute, dateadd(s,datediff(s,getutcdate(),getdate())+(wo.createdtime/1000),’1970-01-01 00:00:00′), dateadd(s,datediff(s,getutcdate(),getdate())+(wo.respondedtime/1000),’1970-01-01 00:00:00′))) as “Ave Minutes to Respond (min)” FROM workorder wo 
LEFT JOIN requesttemplate_list rtl ON rtl.templateid=wo.templateid
WHERE 
— check that the Incident is closed and has a valid first response
(wo.completedtime <> 0 AND wo.respondedtime <> 0)
— limit report to this week 
AND

dateadd(s,datediff(s,getutcdate(),getdate())+(wo.CREATEDTIME/1000),’1970-01-01 00:00:00′) 
>= DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
AND
dateadd(s,datediff(s,getutcdate(),getdate())+(wo.CREATEDTIME/1000),’1970-01-01 00:00:00′) 
< DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)
GROUP BY rtl.templatename
 
PostgreSQL Custom Report
SELECT (rtl.templatename) “Request Template”,
MAX((wo.completedtime-wo.createdtime)/1000/60) “Max Time to Complete (min)”,
MAX((wo.completedtime-wo.createdtime)/1000/60) “Min Time to Complete (min)”,
((AVG(wo.completedtime-wo.createdtime))/1000/60 || ‘ ‘) “Ave Time to Complete (min)”,
MAX((wo.respondedtime-wo.createdtime)/1000/60) “Max Time to Respond (min)”,
MAX((wo.respondedtime-wo.createdtime)/1000/60) “Min Time to Respond (min)”,
((AVG(wo.respondedtime-wo.createdtime))/1000/60 || ‘ ‘) “Ave Time to Respond (min)” FROM workorder wo
LEFT JOIN requesttemplate_list rtl ON rtl.templateid=wo.templateid
WHERE
— check that the Incident is closed and has a valid first response
(wo.completedtime <> 0 AND wo.respondedtime <> 0)
— limit report to this week
AND to_timestamp(wo.createdtime/1000)::TIMESTAMP
>= date_trunc(‘week’,now()::TIMESTAMP)
AND
to_timestamp(wo.createdtime/1000)::TIMESTAMP
< date_trunc(‘week’,now()::TIMESTAMP + INTERVAL ‘1 week + 1 day’)
GROUP BY rtl.templatename
Note: I had an issue with the display capability in ManageEngine ServiceDesk Plus not being able to display a rounded integer value for the average number of minutes for the PostgreSQL report so I had to get creative in order to display a value. Sorry it’s not a rounded integer but the above was as close as I could get with the limitations in place.
This article is relevant to:
AnalyticsService Desk

You may be interested in these other recent articles

Extend the Capabilities of ServiceDesk Plus With Deluge Scripting

17 September 2020

ServiceDesk Plus now offers a new means of extending application capabilities with Deluge, the easy to learn on-line scripting language. The ability to add additional…

Read more

Advanced Analytics for ServiceNow

4 August 2020

ManageEngine have released an official app in the ServiceNow marketplace. ServiceNow users can benefit from ManageEngine’s lightweight, plug-and-play analytics solution Analytics Plus, all directly from…

Read more

Benefit From an OpManager/ServiceDesk Plus Integration

2 June 2020

OpManager can easily be integrated with ServiceDesk Plus to create an efficient network fault management system. Read on to discover some of the benefits of…

Read more

Set3 Solutions and ManageEngine

12 May 2020

Set3 Solutions, a ManageEngine Technology Partner of Zoho Corporation ManageEngine is a division of Zoho Corporation. Set3 Solutions Ltd. are not just a reseller but…

Read more

What Is the Best Way to Update ServiceDesk Plus?

6 May 2020

How to Safely Update ServiceDesk Plus With Service Packs The ServiceDesk Plus team at ManageEngine frequently release new features and fixes, which we post on…

Read more