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

[WeAreVoid]

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

[rushikeshvartak]

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';

[PremMahadikar · Accepted solution]

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:

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

[rushikeshvartak]

What if report ran report data

[WeAreVoid]

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 

but for 258 report job run status is blank.

[PremMahadikar]

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

[WeAreVoid]

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

 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

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

[PremMahadikar]

@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