How to get unused custom fields?

Rumceisz March 17, 2015

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

 

9 answers

1 accepted

1 vote
Answer accepted
Alexey Dorofeyev
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 16, 2017
2 votes
LarryBrock
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 21, 2020

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!

 

1 vote
Josh Simnitt March 28, 2019

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;
0 votes
Austin Smith March 18, 2019

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;

0 votes
Amit Girme June 7, 2018

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;

 
0 votes
Richard Lucas May 2, 2018

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
Sam Lin June 5, 2018

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 June 7, 2018

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;
0 votes
Kevin_Terminella January 30, 2018

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 February 12, 2018

I am getting the below error when running this query.

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

 

Kevin_Terminella February 12, 2018

What db engine are you using?

Samer Harb February 12, 2018

I am using:

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

Kevin_Terminella February 12, 2018

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

0 votes
Tom Jackson
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
December 23, 2016

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
0 votes
Jason Smith January 7, 2016

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.

Suggest an answer

Log in or Sign up to answer