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

Application job status Report

FormulaHack
New Contributor III
New Contributor III

Hello Everyone,

I want to do an analytic report which gives an overview of the status of application jobs from the last run, so you can see the status of the last run.

Does anyone have an idea on how to do this please?

Thank you !

2 REPLIES 2

Raghu
Honored Contributor
Honored Contributor

 

@FormulaHack 

 

select
a.ANALYTICSNAME,
a.CREATEDATE 'Created On',
a.LASTRUN 'Report previous Run',
qt.TRIGGER_NAME 'Job Name',
qct.CRON_EXPRESSION,
FROM_UNIXTIME(qt.NEXT_FIRE_TIME/1000) 'Job next fire time',
FROM_UNIXTIME(qt.PREV_FIRE_TIME/1000) 'Job previous fire time',
case
when FROM_UNIXTIME(qt.Next_FIRE_TIME/1000) > now()=1 and date(FROM_UNIXTIME(qt.Next_FIRE_TIME/1000))=date(now()) then 'Yet to run'
when date(a.lastrun)=date(FROM_UNIXTIME(qt.PREV_FIRE_TIME/1000)) and date(FROM_UNIXTIME(qt.PREV_FIRE_TIME/1000))=date(now()) then 'Report ran successful as scheduled'
when date(a.lastrun)!=date(FROM_UNIXTIME(qt.PREV_FIRE_TIME/1000)) and date(FROM_UNIXTIME(qt.PREV_FIRE_TIME/1000))=date(now()) then 'Report run mismatch'
end as 'Report run status',
a.METADATA 'Run summary details'
from
analyticsconfiges a, qrtz_triggers qt, qrtz_cron_triggers qct
where
qt.TRIGGER_NAME=qct.TRIGGER_NAME

https://forums.saviynt.com/t5/data-access-governance/want-to-create-report-to-identify-all-reports-t...

 


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

rushikeshvartak
All-Star
All-Star

select jobid, jobname, jobstartdate, jobenddate, SAVRESPONSE, case when ecm.EXTERNALCONNECTION is not null then (select e.ENDPOINTNAME from endpoints e where e.SECURITYSYSTEMKEY IN (select s.SYSTEMKEY from securitysystems s where s.EXTERNALCONNECTION IN (select e.EXTERNALCONNECTIONKEY from externalconnection e where e.CONNECTIONNAME=ecm.EXTERNALCONNECTION)) limit 1) when ecm.SYSTEMNAME is not null then (select e.ENDPOINTNAME from endpoints e where e.SECURITYSYSTEMKEY = (select s.systemkey from securitysystems s where s.SYSTEMNAME = ecm.SYStemNAME ) limit 1) else null end as ENDPOINTNAME, case when ecm.EXTERNALCONNECTION is not null then (select e.ENDPOINTKEY from endpoints e where e.SECURITYSYSTEMKEY IN (select s.SYSTEMKEY from securitysystems s where s.EXTERNALCONNECTION IN (select e.EXTERNALCONNECTIONKEY from externalconnection e where e.CONNECTIONNAME=ecm.EXTERNALCONNECTION)) limit 1) when ecm.SYSTEMNAME is not null then (select e.ENDPOINTKEY from endpoints e where e.SECURITYSYSTEMKEY = (select s.systemkey from securitysystems s where s.SYSTEMNAME = ecm.SYStemNAME ) limit 1) else null end as endpointKey from ecmimportjob ecm 


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