How to get unused custom fields?

Hi Everyone,


we took over a large JIRA instance with 700000+ issues and 600+ custom fields. Most of the custom fields are not restricted to specified projects but are set to global. However most of the fields are applied by only a few projects.

How can we easily figure out the usage of aech custom fields? Using navigator's '.is not EMPTY' vcommand is very cumbersome for each fields.


Thanks in advance,


4 answers

I ran this:

select distinct a.cfname, from customfield a
join customfieldvalue b on = b.customfield
order by cfname

Then I ran this:

select cfname, id from customfield
order by cfname

Once you have the two blocks of values, you paste them into the column A in Excel. Then in the column next to it (B), run this for each row:

=IF(COUNTIF(A:A,A1) > 1,"",A1)

Then you should be able to copy column B and paste values in some other column. Sort it to remove the blank cells, and now you have a list of fields to look at. It seems to grab some fields that shouldn't be deleted, like Epic Link, but it should get you most of the way there from what I can see.

I'm sure there's a better way of doing this, but it's something.

This builds on Jason's answer. You should verify each using JQL just in case:

select o1.*
from (
	select cfname, id from customfield) o1
left join ( 
	select distinct a.cfname, from customfield a
	join customfieldvalue b on = b.customfield) o2
on =
where is null

Try this query. It shows the number of entries in the custom field table as well as the date when the custom field was last used (from the change history table).

select c.cfname Field_Name, c.customfieldtypekey Field_Key, c.description Field_Description, TO_CHAR(l.last_used, 'MM/DD/YYYY') last_used, u.Records current_usage
from customfield c
LEFT JOIN (select h.field, cg.CREATED last_used
FROM changegroup cg, (select field, max(groupid) from changeitem group by field) h
where = max) l ON c.cfname = l.field
LEFT JOIN (select c.cfname, count( as Records
from customfield c
LEFT JOIN customfieldvalue v ON = v.customfield
group by c.cfname) u ON c.cfname = u.cfname
ORDER BY u.Records ASC

I am getting the below error when running this query.

Error Code: 1054. Unknown column 'max' in 'where clause'


What db engine are you using?

I am using:

MySQL Workbench Community (GPL) for Windows version 6.3.10 CE build 12092614 (64 bit)

I'm using Postgres, so you might need to tweak the max function to get it to work for you.

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,321 views 14 20
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot