How to manually delete an Automation Rule on the database in Jira
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
If unable to delete an Automation Rule by the Automation admin screen, we may delete it directly on the database.
This is a non-ideal approach and should only be considered if the Automation admin screen or rule deletion function is failing somehow.
Warning
This is only applicable to Automation for Jira and not to Jira Service Management rules, now known as Legacy Automation.
Solution
Warning
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
We may execute this query to list all Automation Rules and some relevant info to help us determine which we want to delete:
select rule."ID" as "Rule ID", rule."NAME" as "Rule name", rule."DESCRIPTION" as "Rule Description", rule."STATE" as "Rule status", rule."CREATED" as "Rule creation date", rule."UPDATED" as "Rule last updated date", state."CURRENT_CREATED" as "Rule last execution status date", state."CURRENT_CATEGORY" as "Rule last execution status", state."EXEC_COUNT" as "Rule execution count"from "AO_589059_RULE_CONFIG" ruleleft join "AO_589059_RULE_STATE_LATEST" state on state."RULE_ID" = rule."ID";
The output should be similar to:
Rule ID | Rule name | Rule Description | Rule status | Rule creation date | Rule last updated date | Rule last execution status date | Rule last execution status | Rule execution count---------+-------------+------------------+-------------+-------------------------+-------------------------+---------------------------------+----------------------------+----------------------3 | Second rule | | ENABLED | 2021-03-17 17:20:31.175 | 2021-03-17 19:22:23.7 | 2021-03-17 19:20:40.630796 | NO_ACTIONS_PERFORMED | 241 | First rule | | ENABLED | 2021-03-11 19:39:16.746 | 2021-03-17 19:22:35.144 | 2021-03-12 15:03:11.009316 | SUCCESS | 6(2 rows)
Good candidates for rules to be deleted are (in order):
Disabled rules
Rules with a low execution count
Rules with a "last execution status date" far in the past (ie. haven't been executed or edited recently)
Removing the rules
Once we select the rules to be deleted, we may execute the following statements in order:
delete "AO_589059_RULE_CFG_COMPONENT" where "RULE_CONFIG_ID" in (...);delete "AO_589059_RULE_CFG_PROJ_ASSOC" where "RULE_CONFIG_ID" in (...);delete "AO_589059_RULE_SCHEDULE" where "RULE_CONFIG_ID" in (...);delete "AO_589059_RULE_TAG" where "RULE_CONFIG_ID" in (...);delete "AO_589059_RULE_TO_LABEL" where "RULE_ID" in (...);delete "AO_589059_RULE_CONFIG" where "ID" in (...);
Replacing (...) by the selected "Rule ID" from the first query, separated by comma:. Eg.: (28, 456, 457, 401).
Historical data leftover
There are tables such as AO_589059_RULE_STATE_LATEST and the AO_589059_AUDIT_* set that contain historical data of the rules executions. We advise keeping those records intact so there's some sort of traceability that the deleted rules once existed.
In the reports they should look like this (second line is a deleted rule):
Was this helpful?