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 Articles

                    • Change module data : Import into Analytics Plus

                      Change module data : Import into Analytics Plus Note: When Analytics Plus officially supports Change module integration Out of the box, then these imported tables and reports might get overwritten. Please ensure to name the table as 'Change_localDB'. ...

                    • Steps to import Archived Request into Analytics Plus

                      By default, we don't import archived request details into Analytics Plus but we are working on a model to import these details out of the box and will be available in one of our future builds. Meanwhile, please follow the below text instructions Step ...

                    • How to import description field details from ServiceDesk Plus to Analytics Plus?

                      Description details from the Requests' module are not available out-of-the box in Analytics Plus as it might contain HTML contents and cannot be displayed properly for reporting. However, if you wish to import description details into Analytics Plus, ...

                    • Why does Analytics plus import less number of tickets from Servicedesk Plus?

                      While integrating Servicedesk Plus with Analytics Plus, only few rows/tickets are imported into Analytics Plus, while the Servicedesk Plus actually has more number of tickets. The primary reason for this behavior is that the date chosen to ...

                    • Workaround to add Region Field in Servicedesk plus data

                      With the out of the box integration, we don't bring 'Region' details from the ServiceDesk Plus. However, you can follow the below steps to import the region details into a new table 1. Import using Local Database by connecting to the Servicedesk Plus ...

                      Related Products