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′
AND
ORDER BY woh.workorderid, woh.historyid
Enjoy !
This article is relevant to:
AnalyticsService DeskOther recent articles in the same category
You may be interested in these other recent articles
Latest Updates for ManageEngine ServiceDesk Plus Cloud
22 October 2024
Discover the latest ServiceDesk Plus Cloud updates, including new features, fixes, and enhancements.
Read moreLatest Updates for ManageEngine ServiceDesk Plus On-Premise
18 October 2024
Discover the latest ServiceDesk Plus updates, including new features, fixes, and enhancements.
Read moreLatest Updates for ManageEngine Endpoint Central
16 October 2024
Discover the latest Endpoint Central updates, including new features, fixes, and enhancements.
Read moreLatest Updates for ManageEngine ADSelfService Plus
15 October 2024
The current build release information for ManageEngine ADSelfService Plus is summarised below. Scroll down for more information. You can download the latest service packs here.…
Read moreManageEngine ADManager Plus Build Release Information
14 October 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