Jira Workflow Schemes Fields usage reports

[KP11]

Is there a way to get usage statistics for JIRA workflows,  Field schemes, Issue Types schemes, Screen schemes  and custom fields. We are planning to upgrade and do not wantto include schems & custom fields NOT USED. What kind of report is avilable or used for internal JIRA developers?   Any script we can run against Oracle database that can give us usage statistics? Example: How many times a custom field has been populated, workflows not used in any project? Schemes not used in any project?

[Jeremy Gaudet · Accepted solution]

For custom field usage in particular, Oracle may be easier, as you can run a single query to count how many values have been set for each custom field, or just pick out the fields that have never been used.  Using a Mysql example, it (unused custom fields) would be:

select count(cfv.id), cfname, cf.id from customfield cf left join customfieldvalue cfv on cf.id=cfv.customfield group by cf.id, cfname having count(cfv.id) = 0;

Don't pull cf.id from the query or else custom fields with duplicate names will be lumped together.  If you want full usage, use:

select count(cfv.id), cfname, cf.id from customfield cf left join customfieldvalue cfv on cf.id=cfv.customfield group by cf.id, cfname order by 1,2;

There may be single-use custom fields out there that really are adding no value as well, and could be removed.

For the rest, I agree with Nic, JIRA shows which schemes are not in use readily in the UI, cleaning those up is a straightforward exercise; figuring out the database table for each scheme, and the referencing column/table to do a similar query is likely more work than it is worth.

[MattS]

I wish there was an easier way to do all this

[Nic Brough -Adaptavist-]

You could dig through the database, but it's probably easier to just use the UI.

For example, for each custom field run a filter for <customfield is not empty> to see how much it's been used.  For workflows, look at the list of workflows and just delete all the ones in the "inactive" section.  For screens, start at the issue type screen scheme page and delete all the ones not mapped to a project, then go to screen schemes and delete all the ones not mapped into an issue type screen scheme, and then delete any screens not used by any workflows or screen schemes.