How to Retrieve Jaspersoft Schedules using a query on Jasper 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 : 15.8.1

Component : PPMJSP

Resolution

1. we can use the following query to get the disabled schedules from Jasper 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';