Sample SQL Query to List All Report Jobs in JasperReports Server Scheduler

Below is a sample SQL query users can use to get all the report jobs listed by its associated report name, job id, next fire time, and job owner information from the JRS scheduler. The query is sorted by next fire time to help determine how the jobs are clustered for capacity planning.

select TIMEZONE('EDT',TO_TIMESTAMP(q.next_fire_time/1000)) as next_job_run
, TIMEZONE('EDT',TO_TIMESTAMP(q.prev_fire_time/1000)) as prev_job_run
, TIMEZONE('EDT',TO_TIMESTAMP(q.start_time/1000)) as first_job_run
, j.report_unit_uri as job_report_name
, j.id as job_id
, u.username as job_owner
, t.tenantname as job_owner_org
from qrtz_triggers q
inner join jireportjob j on 'job_'||j.id = q.job_name
left outer join jiuser u on j.owner = u.id
left outer join jitenant t on t.id = u.tenantid
order by 1

Sample query result:

Note

The query syntax is for PostgreSQL database. If users' JRS repository is using other DBs, users need to find equivalent DB functions for job name concatenation to couple the Quartz and JRS table integration, and to convert internally stored Epoch time in Quartz to a true timestamp in their respective database.

With this query, users can create and deploy a report to the server and share the job information with non admin users who are not the report job owners.

=================================================

20240426-TTC-2256177