Analytics to generate a report for recon job data

[Gpachori]

Hi Team,

Is it feasible to create a reconciliation data report for target applications whose reconciliation job has recently concluded? If so, could you please provide guidance on exporting the data into a CSV file?

Thanks

Gaurav

[naveenss]

Hi @Gpachori ,

Please find the below sample query to check the job status. This is already part of the OOTB analytics from Saviynt. 

Analytics Name: CC_Monitor - Users Import Duration

Query: SELECT EC.JOBNAME,EC.EXTERNALCONNECTION,EC.SAVRESPONSE AS RESPONSE, EC.JOBSTARTDATE,EC.JOBENDDATE, SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(REPLACE(IL.LOGDATAASXML,'','@'),'','&'),'&',1),'@',-1) AS TOTAL_IMPORT_TIME FROM ECMIMPORTJOB EC, IMPORTLOG IL WHERE EC.JOBID = IL.JOBID AND EC.JOBNAME = 'USERIMPORTJOB' AND EC.SAVRESPONSE = 'SUCCESS';

Make the necessary modifications to this query as per your requirement.

Let me know if you have further questions.

[rushikeshvartak]

can you clarify below

• Do you need what data recently has been imported ?
• Do you need job status and stats about how many account updated/inserted/deleted etc.

[Gpachori]

Hi @rushikeshvartak, 

We need both information in the same report.

• Data recently has been imported
• Need job status and stats about how many account updated/inserted/deleted

Thanks

Gaurav

[rushikeshvartak · Accepted solution]

1. You can get Account to Entitlement

SELECT U.username,
       U.customproperty3,
       U.departmentnumber,
       U.jobdescription,
       U.startdate,
       A.name,
       A.status,
       A.created_on,
       EV.entitlement_value,
       et.entitlementname AS enttype
FROM   users U,
       accounts A,
       account_entitlements1 AE,
       entitlement_values EV,
       entitlement_types et
WHERE  ( U.customproperty3 = 65
          OR U.customproperty3 = 01 )
       AND A.endpointkey = 5
       AND U.statuskey = 1
       AND A.accountid = U.customproperty28
       AND A.created_on <= Date_sub(Now(), interval 7 day)
       AND AE.entitlement_valuekey = EV.entitlement_valuekey
       AND A.accountkey = AE.accountkey
       AND et.entitlementtypekey = ev.entitlementtypekey 

Application Import Stats

SELECT EC.jobname,
       EC.externalconnection,
       EC.savresponse
       AS RESPONSE,
       EC.jobstartdate,
       EC.jobenddate,
Substring_index(Substring_index(
                Replace(Replace(IL.logdataasxml, '', '@'), '', '&'), '&', 1), '@', -1) AS TOTAL_IMPORT_TIME
FROM   ecmimportjob EC,
       importlog IL
WHERE  EC.jobid = IL.jobid
       AND EC.savresponse = 'SUCCESS'
       AND systemname LIKE '%GitHub Stage App%' 

[Gpachori]

Hi @rushikeshvartak & @naveenss, 

Thank you for the queries. 

Both queries proved effective in assisting us to accomplish the tasks.

Thanks

Gaurav