How to determine the automation rule that updated an 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
This guide will provide SQL queries that will help identify automation rules:
that updated or created specific Jira issues
or that were triggered from specific Jira issues
Solution
The rule execution information is stored within the Database tables which names start with AO_589059_AUDIT_ITEM:
AO_589059_AUDIT_ITEM
AO_589059_AUDIT_ITEM_ASC_ITEM
AO_589059_AUDIT_ITEM_CGE_ITEM
AO_589059_AUDIT_ITEM_COMP_CGE
AO_589059_AUDIT_ITEM_PROJECT
These tables are where the success/failure messages come from within the automation rule Audit logs.
You can find below the SQL queries that you can run to either identify rules that were triggered by specific Jira issues or that created/updated specific issues.
A few important notes:
You will need to replace 'ABC-123', 'DEF-456' in the queries below with the list of issue keys you would like to query
These queries were written for Postgres, so it might need some adjustment for your database platform.
Depending on how old the ticket is and how many automation rules could have potentially executed on this ticket you may want to add a date constraint to this using the ai."CREATED" column
SQL query 1: Identifying automation rules that were triggered by specific Jira issue keys
1
2
3
4
5
6
7
8
9
10
11
SELECT
ai."CATEGORY" AS "Rule Result",
ai."CREATED" AS "Rule Execution Time",
ai."OBJECT_ITEM_NAME" AS "Rule Name",
ai."SUMMARY" AS "Rule Summary",
aiai."NAME" AS "Object Name",
aiai."TYPE_NAME" AS "Object Type"
FROM public."AO_589059_AUDIT_ITEM" ai
INNER JOIN public."AO_589059_AUDIT_ITEM_ASC_ITEM" aiai
ON ai."ID" = aiai."AUDIT_ITEM_ID"
WHERE aiai."NAME" in ('ABC-123', 'DEF-456');Example of output
1
2
3
4
5
|Rule Result|Rule Execution Time |Rule Name |Rule Summary |Object Name|Object Type|
|-----------|-----------------------|------------------|------------------|-----------|-----------|
|SUCCESS |2024-10-08 09:32:16.813|change description|change description|SCRUM-9 |ISSUE |
|SUCCESS |2024-10-08 09:32:19.670|Create issue |Create issue |SCRUM-9 |ISSUE |In this example, we can see that 2 rules were triggered once by the issue key SCRUM-9 on Oct 8th 2024 at 9:32AM.
SQL query 2: Identifying automation rules that created or updated specific Jira issue keys
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
ai."CATEGORY" AS "Rule Result",
ai."CREATED" AS "Rule Execution Time",
ai."OBJECT_ITEM_NAME" AS "Rule Name",
ai."SUMMARY" AS "Rule Summary",
aici."CHANGE_TO" AS "Object Name",
aici."FIELD_NAME" AS "Action Type"
FROM public."AO_589059_AUDIT_ITEM" ai
INNER JOIN public."AO_589059_AUDIT_ITEM_CGE_ITEM" aici
ON ai."ID" = aici."AUDIT_ITEM_ID"
WHERE aici."FIELD_NAME" in ('com.codebarrel.automation.rule.execution.edit.issue.success', 'com.codebarrel.automation.rule.execution.create.issue.success')
AND aici."CHANGE_TO" in ('ABC-123', 'DEF-456');Example of output 1:
1
2
3
|Rule Result|Rule Execution Time |Rule Name |Rule Summary |Object Name|Action Type |
|-----------|-----------------------|------------------|------------------|-----------|-----------------------------------------------------------|
|SUCCESS |2024-10-08 09:32:16.813|change description|change description|SCRUM-9 |com.codebarrel.automation.rule.execution.edit.issue.success|In this example, we can see that the rule called "change description" updated once the Jira issue with key SCRUM-9 on Oct 8th 2024 at 9:32AM.
The type of action that was made with that Jira issue is found in the "Action Type" column: in this example, the type of action is com.codebarrel.automation.rule.execution.edit.issue.success, which means that the rule edited this issue.
Example of output 2:
1
2
3
|Rule Result|Rule Execution Time |Rule Name |Rule Summary|Object Name|Action Type |
|-----------|-----------------------|------------|------------|-----------|-------------------------------------------------------------|
|SUCCESS |2024-10-08 09:32:19.670|Create issue|Create issue|SCRUM-24 |com.codebarrel.automation.rule.execution.create.issue.success|In this example, we can see that the rule called "Create issue" created the Jira issue with key SCRUM-24 on Oct 8th 2024 at 9:32AM.
The type of action that was made with that Jira issue is found in the "Action Type" column: in this example, the type of action is com.codebarrel.automation.rule.execution.create.issue.success, which means that the rule created this issue.
Was this helpful?