How to know which data fields projects are using and how actively are they used?

[Tuomas Leppilampi]

Hi! So I got hundreds of JIRA projects split between two JIRA instances. I am planning to create reports that cover multiple projects, but it's a bit of a wild west out there - people have been creating their own fields into their projects and edited lists such as 'Bug Severity' which I need to be aligned across all projects for my reports to make sense.

Are there any tools that would help me export the data fields and their usage percentage from multiple projects in a JIRA instance?

Thanks for any help.

[francis]

Hi Tuomas,

We are facing the same type of issue at a customer site.

To resolve this problem we are using a couple of queries to know how the fields are being used.

You would need database access to run the queries or use a free add-on such as the Home directory and db browser for Jira

For instance

Get number of projects with number of issue (less than 100 issues)

select temp.i_count as issues_count, count(temp.pname) as projects_count 
from (    
      select count(ji.project) as i_count, p.pname 
      FROM "jiraissue" as ji    
        left join "project" as p on p.id = ji.project    
      group by p.pname) as temp
where temp.i_count <= 100
group by temp.i_count
order by issues_count

or 

Get an overview of the usage of custom fields per project

select pr.pkey, cf.cfname, count(cfv.id)
from customfield cf
inner join customfieldvalue cfv on cfv.CUSTOMFIELD = cf.ID
  inner join jiraissue ji on cfv.ISSUE = ji.ID
  inner join project pr on ji.PROJECT = pr.id
group by cf.cfname
order by pkey asc, count(cfv.id) ASC ;

If you would like - we can exchange interesting queries.

Francis

[Tuomas Leppilampi]

Thanks so much for your reply and help. We will continue testing in this area and will be sure to share when we find something interesting!
Again, kudos to you.