Requirement: Analytic report to identify if job is not running or missed to run on its schedule time

[AshishKRGupta]

Hi Team,
We have a requirement where we want to identify if some critical job are failed to run
on its schedule time or not running since long even if its schedule.

Please let me know if we have any sample query for this.

Thanks,
Ashish Gupta

[rushikeshvartak]

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=concat('Analytics_',a.ANALYTICSKEY,'CRONE')
	and qt.TRIGGER_NAME=qct.TRIGGER_NAME

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

[AshishKRGupta]

Hi @rushikeshvartak ,

Thanks for quick response.

I have gone through this query earlier but its checks any analytic report which failed to run on schedule time. I am looking for all the job which are failed to run on its schedule time.

In the above query we are comparting failure scenario on the basis of lastrun (analyticsconfiges  attribute) and PREV_FIRE_TIME (qrtz_triggers  attribute) but since we need to do it for qrtz jobs we can't use analytics table and we do not have anything same in ecmimport or qrtz_fired_triggers.

Kindly suggest how we can achieve same for jobs.

Thanks,

Ashish Gupta