Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Analytics to generate a report for recon job data

Gpachori
New Contributor II
New Contributor II

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

5 REPLIES 5

naveenss
All-Star
All-Star

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.

 

Regards,
Naveen Sakleshpur
If this reply answered your question, please click the Accept As Solution button to help future users who may have a similar problem.

rushikeshvartak
All-Star
All-Star

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.

Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.

Gpachori
New Contributor II
New Contributor II

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

  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%' 

rushikeshvartak_0-1708668073771.png

 


Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.

Hi @rushikeshvartak@naveenss

Thank you for the queries. 

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

Thanks

Gaurav