Application job status Report

[FormulaHack]

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 !

[Raghu]

@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...

[rushikeshvartak]

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