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

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

Customise the Layout of a Request in ServiceDesk Plus

5 May 2020

How to customise the layout of a request in ServiceDesk Plus As of ServiceDesk Plus version 11005 it is possible to customise the layout of…

Read more

Remote User Password Reset Management

4 May 2020

Just how do you address the issue of WFH and remote user password reset?

Read more