How to extract all jobs with description in job control panel

[Saviyntreh]

How to extract all jobs with description in job control panel

[rushikeshvartak]

SELECT trigger_name                                 AS 'Job Trigger Name',
       job_group,
       trigger_state,
       From_unixtime(t.next_fire_time / 1000)       AS 'Next Trigger Date',
       Week(From_unixtime(t.next_fire_time / 1000)) AS 'Next Trigger Week',
       Count(*)                                     AS 'Trigger Count'
FROM   qrtz_triggers t
WHERE  t.next_fire_time != 4070945700000
       AND From_unixtime(t.next_fire_time / 1000) > Sysdate()
GROUP  BY trigger_name,
          job_group,
          trigger_state,
          'NextTrigger Date',
          'Next Trigger Week'; 

[Saviyntreh]

Getting the error while running the query. Saviynt version is 24.4

How to extract total created jobs with Job name, job description, schedule trimmings etc.

[rushikeshvartak]

• Run query from analytics
• SELECT TRIGGER_NAME AS 'Job Trigger Name', JOB_GROUP, TRIGGER_STATE, FROM_UNIXTIME(t.NEXT_FIRE_TIME / 1000) AS 'Next Trigger Date', WEEK(FROM_UNIXTIME(t.NEXT_FIRE_TIME / 1000)) AS 'Next Trigger Week', COUNT(*) AS 'Trigger Count' FROM qrtz_triggers t WHERE t.NEXT_FIRE_TIME != 4070945700000 AND FROM_UNIXTIME(t.NEXT_FIRE_TIME / 1000) > SYSDATE() GROUP BY TRIGGER_NAME ,JOB_GROUP,TRIGGER_STATE, 'NextTrigger Date' , 'Next Trigger Week';
• 

[Saviyntreh]

i am able to run the query from Analytics.

1. getting only job with waiting Trigger_State, Please include all the jobs from Job Control Panel

2. is the  qrtz_triggers table available in Data analyzer 

2. is 

[rushikeshvartak · Accepted solution]

• SELECT TRIGGER_NAME AS 'Job Trigger Name', JOB_GROUP, TRIGGER_STATE, FROM_UNIXTIME(t.NEXT_FIRE_TIME / 1000) AS 'Next Trigger Date', WEEK(FROM_UNIXTIME(t.NEXT_FIRE_TIME / 1000)) AS 'Next Trigger Week', COUNT(*) AS 'Trigger Count' FROM qrtz_triggers t GROUP BY TRIGGER_NAME ,JOB_GROUP,TRIGGER_STATE, 'NextTrigger Date' , 'Next Trigger Week';

• 2. is the  qrtz_triggers table available in Data analyzer  _ NO

[rushikeshvartak · Accepted solution]

@Saviyntreh ✅Please click the 'Accept As Solution' button on the reply (or replies) that best answers your original question and hit 'Kudos' button 👍.

[Amit_Malik · Accepted solution]

Hi @Saviyntreh ,

use this 

SELECT qt.trigger_name, qt.trigger_state, qt.DESCRIPTION, From_unixtime(qt.next_fire_time / 1000) as next_fire_time
FROM qrtz_triggers qt
WHERE
group by qt.trigger_name

qrtz_triggers table is not exposed in data analyzer but you can fins details here - https://docs.saviyntcloud.com/bundle/EIC-Database-Schema-Reference/page/Content/Database-Schema-Refe...

[dgandhi]

you can check ecmimportjob table