Need a report for all jobs that are running for more than a specified time

[soumik_das]

We have a requirement that a report is to be generated for all the jobs which is in running state for more than a specified number of hours. Please can you suggest how we can generate this report, we couldn't find any running jobs table.

[Sivagami]

Sample query for jobs running for more than 4 hrs. Alter accordingly per your requirement.

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,
trigger_group,
job_name,
job_group
FROM
qrtz_fired_triggers
where DATE_ADD(FROM_UNIXTIME(fired_time / 1000),
INTERVAL 4 HOUR) < NOW()

 -Siva

[soumik_das]

SELECT command denied to user 'ssmdau'@'100.66.46.230' for table 'qrtz_fired_triggers'

We are getting this error while running the command in data analyzer
Please suggest what to change here and also how to get the time in minutes.

[Sivagami · Accepted solution]

Not all tables are exposed in Data Analyzer. Please create an analytics report with this query & run the report.

[soumik_das]

Thanks a lot Siva