Database SQL query to get field configuration and field configuration schemes on jira server

[ADEBAYO ISAAC TEMIDAYO]

I am trying to get field configuration and field configuration schemes so that I can do a clean up of redundant field configuration and field configuration schemes on the jira server. I couldn't get the endpoint for the resources and I guess database might be the last option.

[Nic Brough -Adaptavist- · Accepted solution]

SQL is the worst possible way to look at this.  For housekeeping, go to the list of field configuration schemes and delete all the unused ones, then go to the list of field configurations and do the same.

[ADEBAYO ISAAC TEMIDAYO]

Thanks Nic, I guess in the long run this would be my final solution if I don't see a better one because the main problem is that field configuration and field configuration schemes are so many, that's why i am looking for a scalable way to get the resources and hopefully perform a delete operation.

[WW]

I had this same question because apparently, you can delete any field configuration scheme with ScriptRunner even the ones you can't in the UI.

I think you'd use the FieldLayoutManager.getProjectsUsing(FieldLayoutScheme fieldLayoutScheme) method, and then get the ones without projects to determine which are unused. But I figured out the SQL. at least for the schemes.

Here's the SQL for all Field Configuration Schemes not in use:

SELECT fls.*
FROM fieldlayoutscheme fls
WHERE fls.id not in
    (SELECT na.sink_node_id 
     FROM nodeassociation na 
     WHERE na.sink_node_entity = 'FieldLayoutScheme') 

Field Configuration Scheme in the UI = FieldLayoutScheme behind the scenes.

[Nic Brough -Adaptavist-]

Under no circumstances should you delete anything from Jira using Scriptrunner if the UI won't let you do it.  Or even change anything.

You will break your system.

Scriptrunner is very powerful and gives you access to functions (in Server/DC) that let you break things in interesting ways.

I've spent a lot of the last 15 years cleaning up the mess made by inappropriate use of apps that are broken, or let you break things.

[WW]

I totally agree you should not delete things with ScriptRunner that the UI doesn't let you.

The problem is that the Java API allows you to delete the FieldLayoutSchemes (aka Field Configuration Schemes) even if they're in use. Who would want to do that? It breaks things.

Most of the other objects I've been writing cleanup scripts for will not allow you to delete them if they're in use.

The SQL statement I posted lists the FieldLayoutSchemes that are not in use as to avoid deleting schemes that are needed.

I did confirm as well that you can use the FieldLayoutManager.getProjectsUsing(FieldLayoutScheme fieldLayoutScheme) method to detect ones used or not used by projects for cleanup purposes.

[Nic Brough -Adaptavist-]

The Java API allows it because it is not the layer where protection against doing dumb things is.

Just because an API lets you do something, does not mean you ever should.

And I know how to script around the protection you've found in your delete-other-things code, it's not as hard as it looks, the API is just a bit more obscure!

But yes, you're absolutely right, all I have to add is that admins need to be careful when scripting!

[Pam Weber]

@WW , thank you for posting that SQL!  I was looking for a way to get counts of different artifact types so we can track a huge Jira cleanup effort.  The table for field config schemes was evading me.  Your SQL showed me that I needed to use the fieldlayoutscheme table.  Thanks!