Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
02/22/2024 07:45 AM
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
Solved! Go to Solution.
02/22/2024 07:50 AM
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.
02/22/2024 08:42 PM
can you clarify below
02/22/2024 09:46 PM
Hi @rushikeshvartak,
We need both information in the same report.
Thanks
Gaurav
02/22/2024 10:01 PM
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%'
02/23/2024 03:26 AM
Hi @rushikeshvartak & @naveenss,
Thank you for the queries.
Both queries proved effective in assisting us to accomplish the tasks.
Thanks
Gaurav