Nika_Hadzhikidi

Jaspersoft Report Scheduler SQL queries

Blog Post created by Nika_Hadzhikidi Employee on May 18, 2017

Hello, everybody,

 

I posted this information as Tech Tip here:

Tech Tip - Jaspersoft Report Scheduler SQL queries 

Wanted to also have it as blog entry and to see which one comes more useful. So will be using this as my first entry for now to see things through.

 

I wanted to share a few SQL queries I wrote when working with a customer on a Jaspersoft Scheduler issue. Basically we needed to find out how many reports are fired/scheduled at a certain time and monitor them.

 

Here is what you could use for this as reference and then modify for your needs :

To be run on the Jaspersoft database:

 
----WAITING --- Times in EST --- this will show all the scheduled reports in the system or "Waiting"
select
j.id,
to_char(to_date('1970-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') + ((1.0 / (24 * 60 * 60 * 1000) * next_fire_time) -(4/24)), 'yyyy-mm-dd hh24:mi:ss') as next_fire_time,
u.fullname,
j.report_unit_uri,
j.label,
to_char(to_date('1970-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') + ((1.0 / (24 * 60 * 60 * 1000) * start_time) -(4/24)), 'yyyy-mm-dd hh24:mi:ss') as start_time,
trigger_state,
trigger_type
from JITenant t
join JIUSER u on t.id = u.TENANTID
join JIReportJob j on u.id = j.owner
join QRTZ_TRiGGERS qt on qt.job_name = concat('job_',j.id)
where trigger_state ='WAITING'

-----FIRED ---Times in EST ---Those are currently running reports in the system
select u.fullname,u.Username,
j.report_unit_uri,
j.label,
to_char(to_date('1970-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') + ((1.0 / (24 * 60 * 60 * 1000) * fired_time) -(4/24)), 'yyyy-mm-dd hh24:mi:ss') as fired_time
from JITenant t
join JIUSER u on t.id = u.TENANTID
join JIReportJob j on u.id = j.owner
join qrtz_fired_triggers qt on qt.job_name = concat('job_',j.id)
group by u.fullname,u.Username,j.report_unit_uri,
j.label,fired_time

 

As you can see I've had to convert the dates as in Jaspersoft database the dates are in Java time and start on 1 Jan 1970. With the conversion the times are in EST but you can also adjust this by modifying the 4/24 value.

Outcomes