I found a post on the forums the other day for someone looking for a report to track the time a request is in any given status. It certainly piqued my interest, especially after being told it couldn’t be done after all ….

The basic premise seems straightforward enough as all the information is contained in the history tab of a request in ManageEngine ServiceDesk Plus – a copy of the result they were looking to achieve describes the required information nicely:


























The key challenge here relates to the fact that the history detail often contains a range of other detail we do not require as part of this report. There are three principle data tables we require, ‘workorderhistory’ the key table, ‘workorderhistorydiff’ with the history change information and ‘statusdefinition’ with the status labels. 

If you run the following custom query* which joins the ‘workorderhistory’ and ‘workorderhistorydiff’ data tables for a particular request ID in ManageEngine ServiceDesk Plus (just replace the number at the end of the query with your target request ID) you can get a clearer picture of the search functions you’re going to need:

— * Reports->New Query Report, paste in the query and run

SELECT * from workorderhistory woh
LEFT JOIN workorderhistorydiff wohd ON wohd.historyid=woh.historyid
WHERE woh.workorderid=’6′








I worked out the following search criteria was needed to obtain the require records in our report, the two key columns being ‘Operation’ from the ‘workortderhistory’ (woh) data table and ‘Columnname’ from the ‘workorderhistorydiff’ (wohd) data table:


WHERE (((woh.Operation=’CREATE’ AND wohd.Columnname IS NULL) OR woh.Operation=’RESOLVED’ OR woh.Operation=’CLOSE’) OR (woh.Operation=’UPDATE’ AND wohd.Columnname=’STATUSID’))


Once we have the right records we can then look to join the ‘StatusDefinition’ data table on wohd.Current_value. One slight issue that needs to be overcome is the fact that this value is stored as text rather than an integer so it need to be converted in our query.

The other challenge we need to overcome is to include a column in each row that presents the ‘Operationtime’ of the previous row so we can calculate the time between the various states, the first row would contain a NULL value for this.

Depending on the database you are using with ManageEngine ServiceDesk Plus there are going to be differences in the way you handle the challenges above and the date / time formats. Anyhow here are my attempts for MS SQL and PostgreSQL (I’ve limited the report to requests for this week just to be safe but feel free to modify as required!) …


MS SQL 2012 Custom Report

SELECT woh.workorderid ‘Request ID’, 
sd.Statusname ‘Status’,
— these are date conversions for MS SQL, PostGreSQL and MySQL will differ
CONVERT(VARCHAR(20), dateadd(s,datediff(s,getutcdate(),getdate())+((LAG(woh.operationtime) OVER (ORDER BY woh.historyid))/1000),’1970-01-01 00:00:00′), 100) AS “Previous Date”,
CONVERT(VARCHAR(20), dateadd(s,datediff(s,getutcdate(),getdate())+(woh.operationtime/1000),’1970-01-01 00:00:00′), 100) AS “Current Date”,
DATEDIFF(minute, dateadd(s,datediff(s,getutcdate(),getdate())+((LAG(woh.operationtime) OVER (ORDER BY woh.historyid)) /1000),’1970-01-01 00:00:00′), dateadd(s,datediff(s,getutcdate(),getdate())+(woh.operationtime/1000),’1970-01-01 00:00:00′)) as “Minutes taken to Respond” FROM workorderhistory woh
LEFT JOIN workorderhistorydiff wohd ON wohd.Historyid=woh.Historyid 
LEFT JOIN Statusdefinition sd ON sd.Statusid=CAST(wohd.Current_value AS INT) 
LEFT JOIN workorder wo ON wo.workorderid = woh.workorderid
WHERE (((woh.Operation=’CREATE’ AND wohd.Columnname IS NULL) OR woh.Operation=’RESOLVED’ OR woh.Operation=’CLOSE’) OR (woh.Operation=’UPDATE’ AND wohd.Columnname=’STATUSID’))
— you can limit the report to a specific request ID with the following AND statement if required
— AND woh.workorderid=’6′

— alternatively 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)

ORDER BY woh.workorderid, woh.historyid

PostGreSQL Custom Report
SELECT woh.workorderid “Request ID”, 
sd.Statusname “Status”,
— these are date conversions for PostGreSQL, MS SQL and MySQL will differ
to_timestamp((LAG(woh.operationtime) OVER (ORDER BY woh.historyid))/1000)::TIMESTAMP “Previous Date”,
to_timestamp(woh.operationtime/1000)::TIMESTAMP “Current Date”,
((woh.operationtime/1000) – ((LAG(woh.operationtime) OVER (ORDER BY woh.historyid))/1000))/60 “Minutes” FROM workorderhistory woh
LEFT JOIN workorderhistorydiff wohd ON wohd.Historyid=woh.Historyid 
LEFT JOIN Statusdefinition sd ON sd.Statusid=CAST(wohd.Current_value AS INT) 
LEFT JOIN workorder wo ON wo.workorderid = woh.workorderid
WHERE (((woh.Operation=’CREATE’ AND wohd.Columnname IS NULL) OR woh.Operation=’RESOLVED’ OR woh.Operation=’CLOSE’) OR (woh.Operation=’UPDATE’ AND wohd.Columnname=’STATUSID’))
— you can limit the report to a specific request ID with the following AND statement if required
— AND woh.workorderid=’6′
— alternatively 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’)
ORDER BY woh.workorderid, woh.historyid
 
 
Example Output

















Enjoy !

This article is relevant to:
AnalyticsService Desk

You may be interested in these other recent articles

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

25 March 2024

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 ADManager Plus Build Release Information

22 March 2024

Summary details of the current build release information for ManageEngine ADManager Plus. Scroll the above to view more release details. Download the latest service packs…

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

ManageEngine OpManager Build Release Information

15 March 2024

Summary details of the current build release information for ManageEngine OpManager. Scroll the above to view more release details. Download the latest service packs here.…

Read more

ManageEngine ServiceDesk Plus On-Premise Build Release Information

14 March 2024

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