We are unable to find any tables which will help us to find all the scheduled analytics in our instance. Also we are also looking to fetch last execution and next schedule.
Please help in sharing the sample query.
Solved! Go to Solution.
Hi @Manu269 ,
Please try the below query and let me know if this helps
select trigger_name,job_name,job_group,from_unixtime(floor(next_fire_time/1000)) as 'Next Run',from_unixtime(floor(prev_fire_time/1000)) as 'Last Run' from qrtz_triggers where job_name like '%Analytics%'
This is not giving the expected result.
The ask here is :
When we navigate to Analytics Configuration Page, we see there are huge list of analytics.
We need a way to identify which are the scheduled analytics?
Also what is the next schedule.
As per below screenshot we can see that the job is scheduled and its suppose to run at Jun 15, 2023 12:45:00.
Hence, I need to know what is the last time job ran and when is the next schedule also to fetch what are other jobs
Hi @Manu269 ,
All the analytics scheduled from this analytics configuration page will be under the job_name "AnalyticsESJob" in the qrtz_triggers table. The trigger name will have the naming convention of "Analytics_<analyticsconfigKey>CRONE". Try the above query from the analytics configurations. Please let me know if this helps or you're still facing any issues.
Following is the final query which i came up with and its working as expected.
qt.trigger_name AS 'Trigger Name',
qt.job_name AS 'Job Name',
qt.job_group AS 'Job Group',
floor(qt.next_fire_time / 1000)
) AS 'Next Run',
floor(qt.prev_fire_time / 1000)
) AS 'Previous Run' ,
CONVERT_TZ(ac.LASTRUN, '+0:00', '+5:30'),
) AS 'LASTRUN',
REPLACE (substring_index(qt.trigger_name,'_',-1),'CRONE','') AS 'Analytics Key',
ac.ANALYTICSNAME AS 'Analytics Name',
ac.DESCRIPTION AS 'Analytics Description',
ac.DISPLAYNAME AS 'Analytics Display Name',
ac.CATEGORY AS 'Analytics Category',
ac.CREATEDATE AS 'Analytics Create Date'
qrtz_triggers qt,analyticsconfiges ac
AND job_group ='Analytics'
AND ac.ANALYTICSKEY = (REPLACE (substring_index(qt.trigger_name,'_',-1),'CRONE',''));