Analytics Schedule Column

[Community_User]

Originally posted on February 10 2022 at 20:38 UTC

I am making an analytic to gather the information of every analytic. I need to get a column for schedule but that is not in the analyticsconfiges table where all the data is. I found the schedule it in qrtz_triggers but I have no way of connecting that table to the analyticsconfiges table. Is there an easier way to get the schedule information for analytics?

[Community_User · Accepted solution]

Originally posted on February 11 2022 at 08:18 UTC

Hi Nick,

The details for all triggers are in qrtz_triggers whereas anything that might be on a schedule will be in qrtz_cron_triggers that will hold the cron value of the trigger.

quick query :

select qt.trigger_name,qt.job_name,qcr.CRON_EXPRESSION from qrtz_cron_triggers qcr inner join qrtz_triggers qt on qt.trigger_name=qcr.trigger_name where qt.job_group='analytics';

Thanks

Nikita 

[Community_User]

Originally posted on February 11 2022 at 16:14 UTC

Hi Nikita,

Thank you for that query that helps a lot. A follow up question I have however is how I could join this query you provided that gives the analytics schedule information with analyticsconfiges. I am using the following query and want the schedule information to map to the analytics pulled from this query. 

Query: 

select

    ac.analyticsname as 'Analytics Name',

    CASE

        WHEN ac.status = 1 THEN 'Active'

        ELSE 'In-Active'

    END AS 'Analytics Status',

    ac.category as 'Category',

    ac.subcategory as 'Sub Category',

    ac.description as 'Description',

    '' as 'Expected Usage',

    '' as 'Schedule',

    '' as 'Target Audience',

    ac.createdate as 'Date Created',

    ac.lastrun as 'Last Run',

    ac.analyticsqry as 'Analytics Query',

    ac.owner as 'Owner',

    ac.application as 'Application',

    ac.externalconnectionkey as 'External Connection',

    ac.updateuser as 'Update User',

    ac.updatedate as 'Update Date',

    ac.risk as 'Risk',

    ac.emailtemplate as 'Email Template',

    ac.allowed_action as 'Allowed Actions',

    ac.tags as 'Tags',

    ac.noofhistorytokeep as 'Number of History to Keep',

    ac.enablearchival as 'Enable Archival',

    ac.basecount as 'Base Count',

    '' as 'Type',

    '' as 'Notes'

from

    analyticsconfiges ac

Thank You,

Nick

[Community_User]

Originally posted on March 11 2022 at 04:31 UTC

Hi Nick,

Did the joining of the queries worked for you or you are still looking to it.

Thanks

Nikita

[Community_User]

Originally posted on March 11 2022 at 16:31 UTC

Hi Nikita,

I was not able to join the schedules of the analytics to the analyticsconfigues table. I want to be able to display the schedule information if the analytic has a schedule. 

Thank you,

Nick

[Community_User]

Originally posted on March 15 2022 at 06:31 UTC

Hi Nick,

We cannot schedule each analytics, it should be at category levelso you need to link the category you scheduled and list all analytics part of that category.

Thanks

Nikita