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

 

6 answers

This widget could not be displayed.

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.

This widget could not be displayed.

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
This widget could not be displayed.
This widget could not be displayed.

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

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.

This widget could not be displayed.

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

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?

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;
This widget could not be displayed.

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;

 

Suggest an answer

Log in or Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted Wednesday in New to Jira

Are you planning to trial, or are currently trialling Jira Software? - We want to talk to you!

Hello! I'm Rayen, a product manager at Atlassian. My team and I are working hard to improve the trial experience for Jira Software Cloud. We are interested in   talking to 20 people planning t...

274 views 5 0
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