Query to fetch the scheduled analytics and last run date

[Manu269]

Hello Team,

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.

[naveenss · Accepted 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%'

[Darshanjain · Accepted solution]

Hi @Manu269 

Use the below query to find

select trigger_name,from_unixtime(floor(prev_fire_time/1000)) as 'previous run' job_name,from_unixtime(floor(next_fire_time/1000)) as 'Next run' from qrtz_triggers where job_name like '%Analytics%'

Thanks

Darshan

[Manu269]

Hello All,

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.

Ex:

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

[naveenss · Accepted solution]

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. 

[Manu269]

Hi @naveenss / @Darshanjain 

Thanks for the input.

I was able to understand the explanation as well as the query.

Manish

[Manu269 · Accepted solution]

Hello All,

Following is the final query which i came up with and its working as expected.

select
qt.trigger_name AS 'Trigger Name',
qt.job_name AS 'Job Name',
qt.job_group AS 'Job Group',
from_unixtime(
floor(qt.next_fire_time / 1000)
) AS 'Next Run',
from_unixtime(
floor(qt.prev_fire_time / 1000)
) AS 'Previous Run' ,
DATE_FORMAT(
CONVERT_TZ(ac.LASTRUN, '+0:00', '+5:30'),
'%d-%b-%Y %H:%I:%S'
) 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'
from
qrtz_triggers qt,analyticsconfiges ac
where
job_name ='AnalyticsESJob'
AND job_group ='Analytics'
AND ac.ANALYTICSKEY = (REPLACE (substring_index(qt.trigger_name,'_',-1),'CRONE',''));