Retrieve list of Automation for Jira rules(A4J) executed against individual issue
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Sometimes, there is a need to access the list of Automation for Jira rules(A4J) that have been executed for a specific issue. Retrieving this information directly from the user interface is not possible, instead, it is necessary to obtain the details from the backend. This article provides guidance on how to retrieve the list of those rules that have been executed for a particular issue.
Solution
Note
This was written and tested using a PostgreSQL DB, so you may need to tweak it depending on the database you are using.
The below query can be used to retrieve the details from the database.
1
2
3
4
5
6
7
select rule."ID" as "Rule Id", rule."NAME" as "Rule name", rule."DESCRIPTION" as "Rule description",
i."END_TIME" as "Execution end", ai."NAME" as "Issue Key"
from "AO_589059_AUDIT_ITEM" i
inner join "AO_589059_RULE_CONFIG" rule on i."OBJECT_ITEM_ID" = rule."ID"
inner join "AO_589059_AUDIT_ITEM_ASC_ITEM" ai on ai."AUDIT_ITEM_ID" = i."ID"
where ai."TYPE_NAME" = 'ISSUE' and ai."NAME" = '<Ticket-ID>'
order by i."END_TIME" DESC;Kindly replace the <Ticket-ID> with Jira issue ID.
Sample result
Note
Database queries other than the ones mentioned earlier can also assist in troubleshooting Automation for Jira.
Rule actors with the highest execution count
1
2
3
4
5
6
7
8
9
10
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
1
2
3
4
5
6
7
8
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
1
2
3
4
5
6
7
8
9
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 descWas this helpful?