We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Sql Query to get the long running & Failed jobs

VenkataElluru
New Contributor II
New Contributor II

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

4 REPLIES 4

armaanzahir
Valued Contributor
Valued Contributor

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

Regards,
Md Armaan Zahir

VenkataElluru
New Contributor II
New Contributor II

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

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'


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

VenkataElluru
New Contributor II
New Contributor II

Thank you @armaanzahir & @rushikeshvartak