Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

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

 

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.
Feb 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.
Apr 21, 2020 • edited

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!

 

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;

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;

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 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;

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.

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.
Dec 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

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