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

[AshishKRGupta]

Hi Team,
We have a requirement where we want to identify if some critical job are failed to run
on 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

[Dhruv_S]

Hi @AshishKRGupta 

Below query gives the StarTime and end time for job triggers. You can modify as per the requirements.

select triggername,jobstartdate, jobenddate from ecmimportjob where triggername= 'Provisioning04'

Regards,

Dhruv Sharma

[rushikeshvartak]

Sample

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