Custom Reports for ServiceDesk Plus - Tracking Request Status
We found a post on the forums recently for someone looking for a report to track the time a request is in any given status. It certainly piqued our 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'
We 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 (We've limited the report to requests for this week just to be safe but feel free to modify as required!) ...
MS SQL 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'
ORDER BY woh.workorderid, woh.historyid
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'
We 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 (We've limited the report to requests for this week just to be safe but feel free to modify as required!) ...
MS SQL 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
ANDdateadd(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
We hope you find this useful.
Best regards
set3 Solutions