Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry.
Saviynt Copilot Icon

want to create report to identify all reports that was not ran but should have ran today itself.

WeAreVoid
New Contributor III
New Contributor III

want to create report to identify all reports that was not ran but should have ran.

7 REPLIES 7

rushikeshvartak
All-Star
All-Star

Modify query as per requirement 

SELECT TRIGGER_NAME AS 'Job Trigger Name', JOB_GROUP, TRIGGER_STATE, FROM_UNIXTIME(t.NEXT_FIRE_TIME / 1000) AS 'Next Trigger Date', WEEK(FROM_UNIXTIME(t.NEXT_FIRE_TIME / 1000)) AS 'Next Trigger Week', COUNT(*) AS 'Trigger Count' FROM qrtz_triggers t WHERE t.NEXT_FIRE_TIME != 4070945700000 AND FROM_UNIXTIME(t.NEXT_FIRE_TIME / 1000) > SYSDATE() GROUP BY TRIGGER_NAME ,JOB_GROUP,TRIGGER_STATE, 'NextTrigger Date' , 'Next Trigger Week';


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

PremMahadikar
All-Star
All-Star

Hi @WeAreVoid ,

Please use the below query for your use case:

 

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

 

Preview of the results:

PremMahadikar_2-1714306798533.png

Add more filters in where clause to get precise report

 

If you find the above response useful, Kindly Mark it as Accept As Solution and hit Kudos

What if report ran report data


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

hi @PremMahadikar 

what is the different b\w previous fire time and last run.

and is that query fetching all the report in Database or repots those scheduled to run today only or anything else?

because i can see there are 653 records found. out of 

WeAreVoid_1-1714466593707.png

 

but for 258 report job run status is blank.

@WeAreVoid ,

  1. What is the difference b\w previous fire time and last run?
    • lastrun is the column from analyticsconfiges table which contains previous run of the report. This can include manual run of the reports and not just scheduled.
    • Previousfiretime column is from  qrtz_triggers table which contains only scheduled reports previous run time
  2. Is that query fetching all the report in Database or repots those scheduled to run today only or anything else?
    • The query fetches all and only scheduled reports as per your original request

The query was created having few reference data in my system. Please add or remove filters and adjust case statements to get precise report. Please use it as reference! Your report results are in the three tables - analyticsconfiges, qrtz_triggers, qrtz_cron_triggers mentioned above in the query.

 

Hi I want to modify highlighted line in query. i want to check reports for last 24 hours.

WeAreVoid_0-1714827423881.png

 

 date(a.lastrun)!=date(FROM_UNIXTIME(qt.PREV_FIRE_TIME/1000)) and DATEDIFF(hour,date(FROM_UNIXTIME(qt.PREV_FIRE_TIME/1000)),date(now())) < 25

but getting error

WeAreVoid_1-1714827538899.png

could you please check if i am passing the parameter in right way.

@WeAreVoid ,

To get records from last 24 hours, use the condition in where clause:

where 
	qt.TRIGGER_NAME=concat('Analytics_',a.ANALYTICSKEY,'CRONE')
	and qt.TRIGGER_NAME=qct.TRIGGER_NAME
	and FROM_UNIXTIME(qt.PREV_FIRE_TIME/1000) > DATE_SUB(NOW(), INTERVAL 24 HOUR)

 

 

If you find the above response useful, Kindly Mark it as Accept As Solution and hit Kudos