Had a client interested in tracking the Approvals Requests made from requests in ManageEngine ServiceDesk Plus the other day. They were interested in tracking the following information:

When the authoriser received the request for approval
When it was approved
How long it has taken the approver to action the request
What is the average time to approve
 
 
Now the Reports module in ManageEngine ServiceDesk Plus doesn’t currently support getting this type of information by using the report wizards so it requires a custom report.
 
The main database table of interest is ‘ApprovalDetails’ as this records all the approvals activity in ManageEngine ServiceDesk Plus.
 
Running ‘SELECT * FROM ApprovalDetails’ in the Query Report Editor of the Reports module yields the following output:
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Now to get a useful report we’ll need to draw on some other tables. The data table ‘ApprovalStageMapping’ cross references if the ‘Approval_stageid’ is mapped to a current request in ManageEngine ServiceDesk Plus by means of the ‘workorderid’:
 
 
 
 
 
 
 
 
 
 
 
 
 
 
The final data table of ‘ApprovalStage’ will provide us with details of when the approval request was raised; we already have the time it was approved, ‘Sent_date’, in the ‘ApprovalDetails’ table.
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
The customer’s database was MS SQL so I managed to create the following query:
 
SELECT asm.workorderid “Request ID”, ad.email “Sent To”,
— used CASE statement to presnet meaningful text rather than a value
  CASE
     WHEN ad.stageid = 1 THEN ‘Stage 1’
     WHEN ad.stageid = 2 THEN ‘Stage 2’
     WHEN ad.stageid = 3 THEN ‘Stage 3’
     WHEN ad.stageid = 4 THEN ‘Stage 4’
     ELSE ‘Stage 5 and above’
  END as “Approval Stage”,
— these are date conversions for MS SQL, PostGreSQL and MySQL will differ
CONVERT(VARCHAR(20), dateadd(s,datediff(s,getutcdate(),getdate())+(aps.sent_date/1000),’1970-01-01 00:00:00′), 100) AS “Sent Date”,
CONVERT(VARCHAR(20), dateadd(s,datediff(s,getutcdate(),getdate())+(ad.action_date/1000),’1970-01-01 00:00:00′), 100) AS “Actioned Date”,
DATEDIFF(minute, dateadd(s,datediff(s,getutcdate(),getdate())+(aps.sent_date/1000),’1970-01-01 00:00:00′), dateadd(s,datediff(s,getutcdate(),getdate())+(ad.action_date/1000),’1970-01-01 00:00:00′)) as “Minutes taken to Respond”,
— used CASE statement to presnet meaningful text rather than a value
  CASE
     WHEN ad.statusid = 1 THEN ‘Requested’
     WHEN ad.statusid = 2 THEN ‘Approved’
     WHEN ad.statusid = 3 THEN ‘Denied’
     WHEN ad.statusid = 4 THEN ‘To be sent’
     ELSE ‘Unspecified’
  END as Status,
ad.comments “Approval Comments” FROM approvaldetails ad
— used RIGHT JOIN so we only pick up Requests with approvals, this includes service requests
RIGHT JOIN approvalstagemapping asm ON asm.Approval_stageid=ad.Approval_stageid
LEFT JOIN approvalstage aps ON aps.approval_stageid=ad.approval_stageid
— although the output does not show True and False values in SD+ report they do exist
— false indicates a previous approval request has been deleted
WHERE ad.isdeleted = ‘False’
ORDER BY asm.workorderid, ad.approvalid
–To use the above custom query cut and paste it into Reports->New Query Report
This gave an output as follows, I’ll let you calculate the average 😉
 
Thought I’d share as it might prove useful to others.
 
Enjoy!
 
 
 
This article is relevant to:
AnalyticsService Desk

You may be interested in these other recent articles

ManageEngine ServiceDesk Plus Cloud Build Release Information

7 July 2024

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

3 July 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 more

ManageEngine Analytics Plus Build Release Information

25 June 2024

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

Read more

ManageEngine Password Manager Pro Build Release Information

21 June 2024

The current build release information for ManageEngine Password Manager Pro is summarised below. Scroll down for more information. You can download the latest service packs…

Read more

Security Advisory – Important Security Fix Released For ManageEngine Solutions

17 June 2024

Product Name Affected Version(s) Fixed Version Fixed on Password Manager Pro 12430 and below 12431 14/06/2024 PAM360 7000 and below 7001 14/06/2024 DetailsAn SQL injection…

Read more