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’.
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.
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.
