Clarity

Expand all | Collapse all

Tech Tip - Jaspersoft Report Scheduler SQL queries

  • 1.  Tech Tip - Jaspersoft Report Scheduler SQL queries

    Posted 05-17-2017 05:35 PM

    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



  • 2.  Re: Tech Tip - Jaspersoft Report Scheduler SQL queries

    Posted 05-17-2017 06:09 PM

    Thank you for sharing this tip with the community Nika!

    Tech Tip - Jaspersoft Report Scheduler SQL queries