Rules from the last 24 hours with high execution count but no actions performed

This query results in rules with successful executions in the last 24 hours but no actions performed. These rules require optimization to reduce their context so that they aren't triggered by the current context events anymore.

Remember to adjust the interval to match your needs.

select 
        AI."OBJECT_ITEM_NAME" as Rule_Name
        ,sum(CASE WHEN AI."CATEGORY" = 'SUCCESS' THEN 1 ELSE 0 END) AS Successful_Executions
        ,sum(CASE WHEN AI."CATEGORY" = 'NO_ACTIONS_PERFORMED' THEN 1 ELSE 0 END) AS No_Actions_Performed
        from jiraschema."AO_589059_AUDIT_ITEM" AI
        join jiraschema."AO_589059_AUDIT_ITEM_ASC_ITEM" AIAI on AI."ID" = AIAI."AUDIT_ITEM_ID" 
        where 1=1 
        AND AI."CATEGORY" != 'CONFIG_CHANGE'
        AND AI."CREATED" >= DATEADD(day, -1, GETDATE())
        group by AI."OBJECT_ITEM_NAME"

Remember to adjust the interval to match your needs.

select 
        AI."OBJECT_ITEM_NAME" as Rule_Name
        ,sum(CASE WHEN AI."CATEGORY" = 'SUCCESS' THEN 1 ELSE 0 END) AS Successful_Executions
        ,sum(CASE WHEN AI."CATEGORY" = 'NO_ACTIONS_PERFORMED' THEN 1 ELSE 0 END) AS No_Actions_Performed
        from "AO_589059_AUDIT_ITEM" AI
        join "AO_589059_AUDIT_ITEM_ASC_ITEM" AIAI on AI."ID" = AIAI."AUDIT_ITEM_ID" 
        where 1=1 
        AND AI."CATEGORY" != 'CONFIG_CHANGE'
        AND AI."CREATED" >= NOW() - INTERVAL '30 day'
        group by AI."OBJECT_ITEM_NAME"

Rule actors with the highest execution count

This query provides the actors running the most rules. You can use it to identify users that might be creating unoptimized automation rules.

Remember to adjust the interval to match your needs.

select 
        cwu.user_name as Username
        ,count(AI."ID") as Num_Of_Executions 
        from jiraschema."AO_589059_AUDIT_ITEM" AI
        join jiraschema."AO_589059_AUDIT_ITEM_ASC_ITEM" AIAI on AI."ID" = AIAI."AUDIT_ITEM_ID" 
        join jiraschema.app_user au on AI."AUTHOR_KEY" = au.user_key
        join jiraschema.cwd_user cwu on au.lower_user_name = cwu.lower_user_name
        where 1=1 
        AND AI."CATEGORY" != 'CONFIG_CHANGE'
        AND AI."CREATED" >= DATEADD(day, -1, GETDATE())
        group by cwu.user_name

Remember to adjust the interval to match your needs.

select 
        cwu.user_name as Username
        ,count(AI."ID") as Num_Of_Executions 
        from "AO_589059_AUDIT_ITEM" AI
        join "AO_589059_AUDIT_ITEM_ASC_ITEM" AIAI on AI."ID" = AIAI."AUDIT_ITEM_ID" 
        join app_user au on AI."AUTHOR_KEY" = au.user_key
        join cwd_user cwu on au.lower_user_name = cwu.lower_user_name
        where 1=1 
        AND AI."CATEGORY" != 'CONFIG_CHANGE'
        AND AI."CREATED" >= NOW() - INTERVAL '30 day'
        group by cwu.user_name

Rules with the longest execution time

This query provides the last 300 rule executions that had the longest execution or duration time. You can use it to identify rules that could be taking a long time to execute and hurting the environment performance.

Remember to adjust the interval to match your needs.

select 
        top 300
        AI."OBJECT_ITEM_NAME" as Rule_Name
        ,AI."DURATION" 
        from jiraschema."AO_589059_AUDIT_ITEM" AI
        join jiraschema."AO_589059_AUDIT_ITEM_ASC_ITEM" AIAI on AI."ID" = AIAI."AUDIT_ITEM_ID" 
        where 1=1 
        AND AI."CATEGORY" != 'CONFIG_CHANGE'
        AND AI."CREATED" >= DATEADD(day, -1, GETDATE())
        order by AI."DURATION" desc

Remember to adjust the interval to match your needs.

select 
        AI."OBJECT_ITEM_NAME" as Rule_Name
        ,AI."DURATION" 
        from "AO_589059_AUDIT_ITEM" AI
        join "AO_589059_AUDIT_ITEM_ASC_ITEM" AIAI on AI."ID" = AIAI."AUDIT_ITEM_ID" 
        where 1=1 
        AND AI."CATEGORY" != 'CONFIG_CHANGE'
        AND AI."CREATED" >= NOW() - INTERVAL '30 day'
        order by AI."DURATION" desc
        limit 300

Events triggering the most rules

This query provides the events that are triggering the most rules. You can use it to identify triggers like a commented event that could be causing hundreds of rule executions and change them.

Remember to adjust the interval to match your needs.

select
        AI."EVENT_SOURCE",count(*) as Times_Triggered
        from jiraschema."AO_589059_AUDIT_ITEM" AI
        join jiraschema."AO_589059_AUDIT_ITEM_ASC_ITEM" AIAI on AI."ID" = AIAI."AUDIT_ITEM_ID" 
        where 1=1 
        AND AI."CATEGORY" != 'CONFIG_CHANGE'
        AND AI."CREATED" >= DATEADD(day, -1, GETDATE())
        group by Event_Source
        having count(*) >= 1

Remember to adjust the interval to match your needs.

select
        AI."EVENT_SOURCE",count(*) as Times_Triggered
        from "AO_589059_AUDIT_ITEM" AI
        join "AO_589059_AUDIT_ITEM_ASC_ITEM" AIAI on AI."ID" = AIAI."AUDIT_ITEM_ID" 
        where 1=1 
        AND AI."CATEGORY" != 'CONFIG_CHANGE'
        AND AI."CREATED" >= NOW() - INTERVAL '30 day'
        group by AI."EVENT_SOURCE"
        having count(*) >= 1
        order by times_triggered desc
  • No labels