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