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

ManageEngine Consultancy, ‘ManageEngine Made Better’

9 November 2020

There’s no doubting the capabilities of the ManageEngine suite of applications. Offering a host of solutions to meet a varied range of IT management, monitoring…

Read more

Cybersecurity? Just what is your weakest link?

4 November 2020

Information Technology is no longer the black art it once was. More and more individuals are able to access and utilise computer technology to improve…

Read more

Do ManageEngine applications work as cloud services?

There are many ways to deploy cloud services to meet your business challenges. ManageEngine has a variety of platforms and solutions to meet those requirements.…

Read more

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