can we create report to identify the jobs that is still running since last 6 hours.

[WeAreVoid]

for proactive monitoring we want to create report and show it on Saviynt dashboard. so we can identify the stuck report. can we create report to identify the jobs that is still running since last 6 hours.

[rushikeshvartak]

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()

Long Running jobs 

[WeAreVoid]

Hi this query is throwing error:

[0]: index [testprameet], type [analytics], id [kW8Vw5EBzguAZzZOjH_K], message [ElasticsearchException[Elasticsearch exception [type=mapper_parsing_exception, reason=failed to parse field [start_time] of type [date] in document with id 'kW8Vw5EBzguAZzZOjH_K']]; nested: ElasticsearchException[Elasticsearch exception [type=illegal_argument_exception, reason=Invalid format: 2024-09-04 21:11:57.846" is malformed at ".846"]];]"

[rushikeshvartak · Accepted solution]

SELECT 
  DATE_FORMAT(FROM_UNIXTIME(fired_time / 1000), '%Y-%m-%d %H:%i:%s') 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()

[stalluri · Accepted solution]

@WeAreVoid 

 We are using minutes to calculate the value of job time and works for us.

SELECT 
    DATE_FORMAT(FROM_UNIXTIME(fired_time / 1000), '%Y-%m-%d %H:%i:%s') AS start_time,
    TIMESTAMPDIFF(MINUTE, FROM_UNIXTIME(fired_time / 1000), NOW()) AS RunningTime_Mins,
    trigger_name,
    job_name
FROM 
    qrtz_fired_triggers
WHERE 
    TIMESTAMPDIFF(MINUTE, FROM_UNIXTIME(fired_time / 1000), NOW()) >= 30 -- Put in minutes values here.