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,
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 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
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
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!
Unfortunately there are no AUG chapters near you at the moment.Start an AUG
We're bringing product updates and pro tips on teamwork to ten cities around the world.Save your spot