Jira Automations - how to get - grom DB all automations related with archived projects?

[Zydek, Rafal]

Case: Jira Automations - how to get - grom DB all automations related with archived projects?

Background story: if you archiving project, automation related with it is still active. Even for single project scope automations.

Suggested workaround - solution: Find on database all automations (single and multi project scope) where archived project is in the scope. 
Next action will be fix or disable automation / or even remove them.

Question / blocker: Where to find automation scope in the database?

[Dick · Accepted solution]

Hi @Zydek, Rafal , Welcome to the Atlassian Community

I did a search and came to a page with a list of vendors and their respective database table names they use: Atlassian list-of-jira-server-ao-table-names-and-vendors-973498988.html 

The Automation tables start off with AO_589059_ 

Hope this will fix your blocker

Dick

[Dick]

Picking the Database of our own datacenter Jira clean, I noticed that the context (or at least the project id's that an automation is working for) is found in the table called: 

[dbo].[AO_589059_RULE_CONFIG]

This table even holds the State field with values enabled/disabled.

Onwards and upwards !

Dick

[Zydek, Rafal]

Thnx! Based on your reply I was able to prepare raw version:

select * from "AO_589059_RULE_CONFIG"

where "ID" in (select "RULE_CONFIG_ID" from "AO_589059_RULE_CFG_PROJ_ASSOC" rc where "RULE_CONFIG_ID" in (select "ID" from "AO_589059_RULE_CONFIG" where "STATE"='ENABLED') and rc."PROJECT_ID" in

(SELECT p.id::varchar FROM propertyentry pe JOIN project p on pe.entity_id=p.id WHERE property_key = 'jira.archiving.projects'))

[Dick]

That's awsome!  I'm glad you got this into a rough diamond.

Just curious: how did you find out that there were still automations running on archived projects in the first place? 

Thanks for accepting the answer. 

Dick

[Zydek, Rafal]

By accidence. During some owner change in automation, I got error on it, that I have no permission (even as Jira admin) to project. Later investigation leads to archived project.