Clarity

Hello fellow community members!

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.

 Hope this helps -Nika

Thank you for sharing this tip with the community Nika!

Tech Tip - Jaspersoft Report Scheduler SQL queries