How to bring approval date data from ServiceDesk Plus?

Steps to import approval date

We can fetch the approval date data from ServiceDesk Plus using the following workaround. Below are the steps to be followed to successfully import the data.

Step 1:
Click Import data within the ServiceDesk Plus database in Analytics Plus.



Step 2:
Click Local and Cloud Databases



Step 3:
Enter the database connection details based on the database used by ServiceDesk Plus and hit next. Choose Custom Query and use
any one
the below queries based on the database used by ServiceDesk Plus. 

Then click Next. 


Query for  PGSQL:
select wo.workorderid as "Request ID",to_timestamp(apr.action_date/1000) as "Approval Date" from workorder wo join workorderstates wos on wo.workorderid =
wos.workorderid join approvaldetails apr on wos.appr_statusid = apr.statusid     

Query for  MSSQL:
SELECT wo.workorderid      AS "Request ID", 
       DATEADD(MILLISECOND, apprdet.action_date % 1000, DATEADD(SECOND, apprdet.action_date / 1000, '19700101'))  AS "Approval Date"
FROM   workorder wo
       LEFT JOIN workorderstates wos
              ON wo.workorderid = wos.workorderid
       LEFT JOIN approvaldetails apprdet
         ON wos.appr_statusid = apprdet.statusid
WHERE  ( wo.isparent = '1' ) 


Step 4:
Enter the table name. Choose the relevant data type and click join tables to do a lookup.




Step 5:
Choose the column names from the imported table for the lookup.


Step 6:
Choose the relevant column for the lookup from existing table (RequestID from Request table)



Step 7:
Click Save and Close after combining the new table with the existing table.




Step 8:
Click Next after providing relevant information in the database setup window



Step 9:
Schedule the import based on your convenience.



Now you should be able to create reports using the approval date data from ServiceDesk Plus.

To include the below columns in the query
Template Name
First Name
Last Name
Stage Name
Approver Email
Approval Sent Date
Approved date
Approval Status
Comments

PGSQL Query:- 

select wo.workorderid as "Request ID", asgd.stagename as "StageName", apd.email as "ApprovalEmail",to_timestamp(apd.action_date/1000) as "Approval Date", to_timestamp(ast.sent_date/1000) as "ApprovalSentDate", asd.statusname as "ApprovalStatus", apd.comments as "ApprovalComments" from workorder wo join workorderstates wos on wo.workorderid =wos.workorderid join approvaldetails apd on wos.appr_statusid = apd.statusid left join approvalstage ast on ast.approval_stageid=apd.approval_stageid left join approvalstagedefinition asgd on asgd.stageid=apd.approval_stageid left join sduser sdu on sdu.userid=apd.approverid left join approvalstatusdefinition asd on asd.statusid=apd.statusid;

MSSQL Query:- 


select wo.workorderid as "Request ID", asgd.stagename as "StageName", apd.email as "ApprovalEmail",DATEADD(MILLISECOND, apd.action_date % 1000, DATEADD(SECOND, apd.action_date / 1000, '19700101')) as "Approval Date", DATEADD(MILLISECOND, ast.sent_date % 1000, DATEADD(SECOND, ast.sent_date / 1000, '19700101')) as "ApprovalSentDate", asd.statusname as "ApprovalStatus", apd.comments as "ApprovalComments" from workorder wo join workorderstates wos on wo.workorderid =wos.workorderid join approvaldetails apd on wos.appr_statusid = apd.statusid left join approvalstage ast on ast.approval_stageid=apd.approval_stageid left join approvalstagedefinition asgd on asgd.stageid=apd.approval_stageid left join sduser sdu on sdu.userid=apd.approverid left join approvalstatusdefinition asd on asd.statusid=apd.statusid;

      New to M365 Manager Plus?
      New to M365 Manager Plus?
        New to RecoveryManager Plus?
        New to RecoveryManager Plus?
          New to Exchange Reporter Plus?
          New to Exchange Reporter Plus?
            New to SharePoint Manager Plus?
            New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                      Related Products