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

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

AshishKRGupta
New Contributor II
New Contributor II

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

2 REPLIES 2

rushikeshvartak
All-Star
All-Star
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

rushikeshvartak_0-1716522816708.png

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

 


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

AshishKRGupta
New Contributor II
New Contributor II

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