Jump to content
JasperReports Library 7.0 is now available ×
  • Sample SQL Query to List All Report Jobs in JasperReports Server Scheduler


    Tom C
    • Edited on:
    • Features: Scheduler Version: v9.0.0 Product: JasperReports® Server

    Below is a sample SQL query users can use to get all the report jobs listed by its associated report name, job id, next fire time, and job owner information from the JRS scheduler. The query is sorted by next fire time to help determine how the jobs are clustered for capacity planning.

    select TIMEZONE('EDT',TO_TIMESTAMP(q.next_fire_time/1000)) as next_job_run
    , TIMEZONE('EDT',TO_TIMESTAMP(q.prev_fire_time/1000)) as prev_job_run
    , TIMEZONE('EDT',TO_TIMESTAMP(q.start_time/1000)) as first_job_run
    , j.report_unit_uri as job_report_name
    , j.id as job_id
    , u.username as job_owner
    , t.tenantname as job_owner_org
    from qrtz_triggers q
    inner join jireportjob j on 'job_'||j.id = q.job_name
    left outer join jiuser u on j.owner = u.id
    left outer join jitenant t on t.id = u.tenantid
    order by 1

     

    Sample query result:

    Screenshot(2150)x.thumb.png.235dec577a3e164101299af7cbfc0b60.png

    Note

    The query syntax is for PostgreSQL database. If users' JRS repository is using other DBs, users need to find equivalent DB functions for job name concatenation to couple the Quartz and JRS table integration, and to convert internally stored Epoch time in Quartz to a true timestamp in their respective database.

    With this query, users can create and deploy a report to the server and share the job information with non admin users who are not the report job owners.

    =================================================

    20240426-TTC-2256177


    User Feedback

    Recommended Comments

    There are no comments to display.



    Create an account or sign in to comment

    You need to be a member in order to leave a comment

    Create an account

    Sign up for a new account in our community. It's easy!

    Register a new account

    Sign in

    Already have an account? Sign in here.

    Sign In Now

×
×
  • Create New...