How to get unused custom fields?

[Rumceisz]

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,
Rumi

[Alexey Dorofeyev · Accepted solution]

This can be done using Custom Fields Usage plugin

https://marketplace.atlassian.com/plugins/systems.npe.jira.cfu/server/overview

[LarryBrock]

Hi @Rumceisz - great question!

The free Power Admin add-on from Botron Software is a great way to handle all this from within the Jira UI. It will also help you clean up much more than just custom fields.

I've been square in your shoes before and appreciate your situation.  I arrived to find seven DC instances, each with a 65k user license and each with at minimum 2,800 custom fields (max was 4,800+).  There was no governance process to be found so the first step I'd recommend is to put a governance process in place.  Otherwise you're wiping up the spilled milk but the jug is still turned over on the counter dumping faster than you can clean. :-)

Cheers!

[Josh Simnitt]

Lots of answers already, but Atlassian provided something close to an official answer and other queries here:

Managing Custom Fields in JIRA effectively

**'This SQL query can also return custom fields implemented by plugins; as such, those custom fields may be using their own datastore. Have these custom fields reviewed by Business Owners before taking any action.'**

Unused Custom Fields 

select count(*), customfield.id, customfield.cfname, customfield.description
from customfield left join customfieldvalue on customfield.id = customfieldvalue.customfield
where customfieldvalue.stringvalue is null 
  and customfieldvalue.numbervalue is null 
  and customfieldvalue.textvalue is null
group by customfield.id;

[Austin Smith]

Modified for MSSQL with schema named "jiraschema"

select c.id Field_ID, c.cfname Field_Name,  last_used, u.Records current_usage
from jiraschema.customfield c
LEFT JOIN (select h.field, cg.CREATED last_used
FROM jiraschema.changegroup cg, (select field, max(groupid) max from jiraschema.changeitem group by field) h
where cg.id = max) l ON c.cfname = l.field
LEFT JOIN (select c.cfname, count(v.id) as Records
from jiraschema.customfield c
LEFT JOIN jiraschema.customfieldvalue v ON c.id = v.customfield group by c.cfname) u
ON c.cfname = u.cfname
ORDER BY u.Records ASC;

[Amit Girme]

For MySQL  TO_CHAR function is not available so below query shall work:

select c.id Field_ID, c.cfname Field_Name, date_format(l.last_used, '%m/%d/%y') 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) max from changeitem group by field) h
where cg.id = max) l ON c.cfname = l.field
LEFT JOIN (select c.cfname, count(v.id) as Records
from customfield c
LEFT JOIN customfieldvalue v ON c.id = v.customfield group by c.cfname) u
ON c.cfname = u.cfname
ORDER BY u.Records ASC;

 

[Richard Lucas]

This version of Kevin's SQL will work on Oracle, but there are no guarantees.

select c.id Field_ID, 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) max from changeitem group by field) h
where cg.id = max) l ON c.cfname = l.field
LEFT JOIN (select c.cfname, count(v.id) as Records
from customfield c
LEFT JOIN customfieldvalue v ON c.id = v.customfield
group by c.cfname) u ON c.cfname = u.cfname
ORDER BY u.Records ASC

[Harish Tuccapuram]

Thanks @Richard Lucas it did worked for me, i was just wondering why its showing as null on Last_Used column when executed this query?

[Amit Girme]

For MySQL  TO_CHAR function is not available so below query shall work:

select c.id Field_ID, c.cfname Field_Name, date_format(l.last_used, '%m/%d/%y') 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) max from changeitem group by field) h
where cg.id = max) l ON c.cfname = l.field
LEFT JOIN (select c.cfname, count(v.id) as Records
from customfield c
LEFT JOIN customfieldvalue v ON c.id = v.customfield group by c.cfname) u
ON c.cfname = u.cfname
ORDER BY u.Records ASC;

[Kevin_Terminella]

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 cg.id = max) l ON c.cfname = l.field
LEFT JOIN (select c.cfname, count(v.id) as Records
from customfield c
LEFT JOIN customfieldvalue v ON c.id = v.customfield
group by c.cfname) u ON c.cfname = u.cfname
ORDER BY u.Records ASC

[Samer Harb]

I am getting the below error when running this query.

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

[Kevin_Terminella]

What db engine are you using?

[Samer Harb]

I am using:

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

[Kevin_Terminella]

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

[Tom Jackson]

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, a.id from customfield a
	join customfieldvalue b on a.id = b.customfield) o2
on o1.id = o2.id
where o2.id is null

[Jason Smith]

I ran this:

select distinct a.cfname, a.id from customfield a
join customfieldvalue b on a.id = 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.