Sql Query to get the long running & Failed jobs

[VenkataElluru]

Hello Team,

Could you please help me for the analytic query to fetch long running any job type (Except Trigger chain job) report.

Thank you,

Venkata

[armaanzahir · Accepted solution]

Hi @VenkataElluru 

Long Running Jobs (Triggers)

SELECT FROM_UNIXTIME(fired_time / 1000) AS start_time, NOW() AS CurrentTime, TIMESTAMPDIFF(HOUR, FROM_UNIXTIME(fired_time / 1000), NOW()) AS RunningTime_Hrs, trigger_name, job_name FROM qrtz_fired_triggers WHERE DATE_ADD(FROM_UNIXTIME(fired_time / 1000), INTERVAL 4 HOUR) < NOW()

Failed Jobs

select jobid, jobname, jobstartdate, jobenddate, SAVRESPONSE, case when ecm.EXTERNALCONNECTION is not null then (select e.ENDPOINTNAME from endpoints e where e.SECURITYSYSTEMKEY IN (select s.SYSTEMKEY from securitysystems s where s.EXTERNALCONNECTION IN (select e.EXTERNALCONNECTIONKEY from externalconnection e where e.CONNECTIONNAME=ecm.EXTERNALCONNECTION)) limit 1) when ecm.SYSTEMNAME is not null then (select e.ENDPOINTNAME from endpoints e where e.SECURITYSYSTEMKEY = (select s.systemkey from securitysystems s where s.SYSTEMNAME = ecm.SYStemNAME ) limit 1) else null end as ENDPOINTNAME, case when ecm.EXTERNALCONNECTION is not null then (select e.ENDPOINTKEY from endpoints e where e.SECURITYSYSTEMKEY IN (select s.SYSTEMKEY from securitysystems s where s.EXTERNALCONNECTION IN (select e.EXTERNALCONNECTIONKEY from externalconnection e where e.CONNECTIONNAME=ecm.EXTERNALCONNECTION)) limit 1) when ecm.SYSTEMNAME is not null then (select e.ENDPOINTKEY from endpoints e where e.SECURITYSYSTEMKEY = (select s.systemkey from securitysystems s where s.SYSTEMNAME = ecm.SYStemNAME ) limit 1) else null end as endpointKey from ecmimportjob ecm where ecm.SAVRESPONSE !='success';

[VenkataElluru]

Thank you @armaanzahir for sharing the information. This is helpful.

Also, could you please confirm that, can we exclude the trigger chain job type jobs from the below query. Because the case is, trigger chain job contains multiple sub jobs so any way it will take some to complete. 

Long Running Jobs (Triggers)

SELECT FROM_UNIXTIME(fired_time / 1000) AS start_time, NOW() AS CurrentTime, TIMESTAMPDIFF(HOUR, FROM_UNIXTIME(fired_time / 1000), NOW()) AS RunningTime_Hrs, trigger_name, job_name FROM qrtz_fired_triggers WHERE DATE_ADD(FROM_UNIXTIME(fired_time / 1000), INTERVAL 4 HOUR) < NOW()

Thank you,

Venkata

[rushikeshvartak · Accepted solution]

This query does not contains triggerchain. 

Or 

SELECT FROM_UNIXTIME(fired_time / 1000) AS start_time, NOW() AS CurrentTime, TIMESTAMPDIFF(HOUR, FROM_UNIXTIME(fired_time / 1000), NOW()) AS RunningTime_Hrs, trigger_name, job_name FROM qrtz_fired_triggers WHERE DATE_ADD(FROM_UNIXTIME(fired_time / 1000), INTERVAL 4 HOUR) < NOW() and job_name !='TriggerChainJob'

[VenkataElluru]

Thank you @armaanzahir & @rushikeshvartak