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 ServiceDesk Plus On-Premise Build Release Information

28 June 2022

Summary details of the current Build Release information for ManageEngine ServiceDesk Plus. To safely upgrade your current instance of ManageEngine ServiceDesk Plus please refer to…

Read more

ManageEngine Endpoint Central (formerly Desktop Central) On-Premise Build Release Information

27 June 2022

Summary details of the current Build Release information for ManageEngine Endpoint Central. Note: Desktop Central changing its name to Endpoint Central will not affect the…

Read more

ManageEngine ServiceDesk Plus Cloud Build Release Information

Summary details of the current Build Release information for ManageEngine ServiceDesk Plus Cloud Edition. All upgrades are performed by the Zoho Cloud team. Should you…

Read more

Get to building your business apps with the new AppCreator

9 June 2022

ManageEngine’s new low-code application development tool Creating an application, building one and deploying it can be expensive and time consuming as you have to make…

Read more

Endpoint Central’s Endpoint Security

31 May 2022

Endpoint Central (formerly Desktop Central) not only got a name change but has also introduced Endpoint Security to help organisations keep their endpoints secured. According…

Read more