How can I produce a view of custom field usage by project?

Question

Hi there,

I am currently working with a DC instance of JIRA with approximately 1600 custom fields. My initial goal is to reduce that to 800. For additional context, our environment is moving towards standardised, shared templates, so the remaining number of older, custom projects is actively diminishing.

I've already walked through 2 phases of reductions targeting low-hanging fruit:

* CF's with no ticket usage (excluding addon provided)
* CF's with no updates within the last two years (excluding addon provided)

This has yielded me a scope of approximately 450 fields; however, that's not even close to my desired outcome.

My next target is in-use custom fields used by low numbers of projects. We don't intend to support hyper-specific use cases for custom fields in our future.

I have no idea how to produce this view.

What I'm thinking would be great would be a sorted list looking like the following:

* Customfield A: 300 projects
* Customfield D:  150 projects
* Customfield C: 3 projects

Thanks, and looking forward to hearing back.

Florian Bonniec · Answer

You can find the db structure below

https://developer.atlassian.com/server/jira/platform/attachments/jira_8.20_database_schema.pdf

You have to look into customfieldvalue table, join with jiraissue table then project table.

Make sure to check STRINGVALUE, NUMBERVALUE, TEXTVALUE and DATEVALUE column in customfieldvalue because if there is any value for a CF it's not stored in the same column depending on the field type in JIRA.

Regards

Matthew Martin · Answer

Yeah. Maybe I'll just need to bite the bullet and commit to doing that.

Florian Bonniec · Answer

Hi @Matthew Martin

I would have used a database query to do that. Doing so you are sure to not miss anything due to permissions issue.

Regards