How to Identify JQL Filters that Reference Name Values

[Benjamin Dains]

Scenario

We received a requirement to change the name of Bug to Defect in Jira, but I've identified that many of the JQL filters created in our Jira instance reference that issuetypes name, and not the id.

After a little testing, I figured out we can just use that issuetypes id and it works in place of the name. See example below.

// This will give you a list of bugs, but if the name is changed to defect for example, this JQL will break.

issuetype = "Bug"

// But if you change the JQL to this, it will be resilient to name changes.

issuetype = 10004

My Problem

It's great we have a solution to this and we can update to the issuetype id before we migrate the changes, but is there a way to find out which filters have components that reference the name property instead of the id property so I don't have to go through JQL filters one by one?

Bonus

Is there a way to not only find the list of filters using this convention, but also a way to update those filters programmatically using something like ScriptRunner or Jira Automation?

[Kai Sören Becker · Accepted solution]

Hi @Benjamin Dains 

easiest way would be to check the database. Thats how we usually handle these filters.
The corresponding table would be "searchrequest"

SELECT * FROM searchrequest WHERE reqcontent LIKE '%issuetype = Bug%' OR reqcontent LIKE '%type = Bug%';

[Benjamin Dains]

Thank you so much for that info! That will really help to save me some time. I appreciate you taking the time to comment and help out!

[Aron Gombas _Midori_]

Using @Kai Sören Becker 's suggestion to solve the "bonus" you defined, you could craft an SQL UPDATE statement which replaces "issuetype = Bug" with "issuestpye = 123" directly in the database using string replace, so that the other parts of the JQL remains untouched.

Notes:

1. I'd first create the SELECT statement to be sure that it only finds those that are needed to be updated.
2. If it works correctly, then
   1. Stop Confluence
   2. Run the UPDATE statement, one for each issue type name to issue type ID pair.
   3. After each, rerun the SELECT to verify that all occurences disappeared.
   4. Restart Confluence.

[Benjamin Dains]

Awesome info @Aron Gombas _Midori_ , thank you for that, I'll be looking into it more today. Much appreciated.