Retrieve Jaspersoft Schedules using a query on Jaspersoft DB

Products

• Clarity PPM On Premise

Issue/Introduction

How to retrieve a list of active or disabled schedules in Jasper Server when we have a lot of them? The list view can only show 15-20 records per page and it's a hassle to view all of them at once or export the data.

Environment

Release : 16.1.2

Component : PPMJSP

Resolution

1. We can use the following query to get the disabled schedules from the Jaspersoft Database.
   
   SELECT job.id
    ,det.job_name
    ,job.Label
    ,job.REPORT_UNIT_URI
    ,usr.username
    ,qrt.trigger_state
FROM qrtz_triggers qrt
JOIN QRTZ_JOB_DETAILS det ON qrt.job_name = det.JOB_NAME
JOIN jireportjob job ON 'job_' || job.id = det.job_name
JOIN jiuser usr ON usr.id = job.OWNER
WHERE qrt.TRIGGER_STATE = 'PAUSED';
   
   
2. The below query will give the active schedules.
   
   SELECT job.id
    ,det.job_name
    ,job.Label
    ,job.REPORT_UNIT_URI
    ,usr.username
    ,qrt.trigger_state
FROM qrtz_triggers qrt
JOIN QRTZ_JOB_DETAILS det ON qrt.job_name = det.JOB_NAME
JOIN jireportjob job ON 'job_' || job.id = det.job_name
JOIN jiuser usr ON usr.id = job.OWNER
WHERE qrt.TRIGGER_STATE = 'WAITING';

Additional Information

Note:

It is not possible to obtain parameter values of these schedules, via a SQL query, because of the way they are stored. Also look at the Jaspersoft Community thread below.

• SQL Query To Show All Report Schedule Parameters