Retrieve Jaspersoft Schedules using a query on Jaspersoft DB
search cancel

Retrieve Jaspersoft Schedules using a query on Jaspersoft DB

book

Article ID: 238037

calendar_today

Updated On:

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.