Spring Cleaning for Custom Fields

Well, I’m back…or at least upright. Thank you all for the well wishes I’ve received. As much as I hate not posting an actual article, I really did need the week off to recover. But now it’s time to get back to it. As I’ve stated, this week’s article was going to be a reader selected topic. It seems there was some demand for each, but the clear winner was…

I’ll be tackling the other articles in the coming weeks, but lets look at Getting those custom fields under control this week.

A bit of a confession here.

First, let me say that as Admins, I think each one of us has various strengths and weaknesses. Me, for example. My obvious strength is Systems. I can tune almost any system to run smoothly. I can learn new back-end technologies with almost no lead-time and still successfully deploy.

However, my weakness as a JIRA Admin is I’ve never been able to execute a successful Cleanup. I’ve known the problem, and I’ve worked to fix it. But I’d rather be a con-census builder than a dictator when it comes to JIRA, and people LOVE their custom fields.

To manage this, I’ve always put safeguards to keep things getting worse. I re-use fields rather than create new ones, and really analyze whether new fields are needed. But on systems I’ve taken over, it’s rare I get a group to give up a field that was already existing.

That being said, if you want to learn from a real master of this topic, I’d highly recommend Rachel Wright’s JIRA Strategy Admin Workbook.

However, I’m going to give you tools to analyze the problem, see what fields aren’t being used, and give you the data to say to people “This…this is a problem we can fix.” Then I’ll show you how to go about cleaning things up. So, lets learn together!

Why does this even matter?

So, you might be asking yourself “JIRA is all about the custom fields, why should I worry about how many there are?” That, honestly, is a fair question. But the reason you should care is performance. It’s just a fact: The more custom fields your system has to parse through, the slower things like creating an issue, searching, etc will be.

Take a look at this chart above. This is from some testing Atlassian did. If we look at JQL Searching alone, at 1400 custom fields, the performance is abysmal at almost 3 seconds. However, doubling the custom field count to 2800 triples that number to nearly 9 seconds.

And yes, this is an extreme case. But users will notice a delay of half a second. You will get comments saying JIRA is slow even at that number, trust me. And it doesn’t take too terribly many custom fields to get searching to be that slow. That is why your custom field count matters.

Analysis

It is said the first step to fixing a problem is admitting you have a problem. Check. The second step it would seem is to figure out how big the problem is. Unfortunately, for this part we are going to have to go directly to the database.

For all my databases, I like to keep an administrative read-only account that will allow me to go in and query from time to time. This helps with making sure things are running properly, or diagnosing problems every now and again. Just make sure it’s read only so you don’t mess with something that aught not to be messed with.

Anyways, Atlassian has some queries that will help you figure out what fields are being used in your system. You can find all of them available here:

• Managing custom fields in JIRA effectively

All the queries below I have confirmed are still working for MySQL 5.7 and JIRA 8.5.3.

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
and customfieldvalue.datevalue is null
and customfield.CUSTOMFIELDTYPEKEY not like '%com.atlassian.servicedesk%'
and customfield.CUSTOMFIELDTYPEKEY not like '%com.pyxis.greenhopper%'
and customfield.CUSTOMFIELDTYPEKEY not like '%com.atlassian.bonfire%'
and customfield.CUSTOMFIELDTYPEKEY not like '%com.atlassian.jpo%'
group by customfield.id, customfield.cfname, customfield.description;

So, the first logical question is to ask is what fields are just not being used – period. These can probably be thrown out easily enough. Just a word of caution with this query: It can return fields that are being used by Apps using their own datastore. I’ve modified it to at least throw out results from JIRA Service Desk, JIRA Software, Capture, and Portfolio So make sure you are vetting this list for such false postitives. But if it’s a field you or your team has created, and it’s on this list, it might be time to have a discussion on whether it needs to be around.

Custom fields that have not been updated after date (YYYY-MM-DD)

select field.id, field.cfname from customfield field where field.cfname not in (
select item.field from changeitem item
JOIN changegroup cgroup ON item.groupid=cgroup.id
where item.fieldtype='custom' and cgroup.created > 'YYYY-MM-DD'
) and customfieldtypekey not like '%com.pyxis.greenhopper%'
and customfieldtypekey not like '%com.atlassian.servicedesk%'
and customfieldtypekey not like '%com.atlassian.bonfire%'
and customfieldtypekey not like '%com.atlassian.jpo%'

So this one returns fields that haven’t been used after a date. You’ll have to modify the query by replaying “YYYY-MM-DD” with a date you are interested in, but this one is handy for an end of year review, as it can answer “What fields have we not used this year”. If your teams haven’t touched the field in that long, you have a strong case for it’s need to go away.

Custom fields with low usage

select count(*), customfield.id, customfield.cfname, customfield.description
from customfield left join customfieldvalue on customfield.id = customfieldvalue.customfield
where customfield.CUSTOMFIELDTYPEKEY not like '%com.atlassian.servicedesk%'
and customfield.CUSTOMFIELDTYPEKEY not like '%com.pyxis.greenhopper%'
and customfield.CUSTOMFIELDTYPEKEY not like '%com.atlassian.bonfire%'
and customfield.CUSTOMFIELDTYPEKEY not like '%com.atlassian.jpo%'
and customfieldvalue.ID is not NULL
group by customfield.id having count(*) < 5
order by count(*) desc

Unlike the first query, this one will return fields that do not meet a certain threshold of usage. As written, this threshold is 5 issues, but you can adjust it by changing the number in “count(*) < 5”. As a rule, I like to keep this to one-tenth of one percent of the total number of issues in your instance.. So if you have 400,000 issues in your instance, the threshold would be 400. But that is a personal preference, you should set this wherever it makes sense for you.

So, what now?

You know have your information, and you know what fields are not being used. You can just go and delete them, right?

Well…

Not really. You should do some more digging. Who asked for that field to be added? What was their business case? Was that field just recently added? For those with a small usage, are they only being used in a specific project? If so, what is the usage within that project?

Also, use Botron Power Admin to see where all the field is being used (or not used). This might tell you who you need to talk to as well.

This should help you narrow down your candidates further. Once you do that, go back to whoever requested those fields. Take the data from your queries, and let them know that the fields aren’t being used, and are on a list to be removed. You can also include documents on how custom fields impact performance for everyone. If you are lucky, they’ll agree with the data and you can remove it. If not, start negotiating. See why they think they need a field they aren’t using. See if there is a way you can give them their ask without the field.

Custom Field optimizer (Data Center)

For those of you on Data Center, JIRA has a built-in Custom field optimizer you can use. It will go through and find fields that are not used often, or otherwise “misconfigured” (As defined by Atlassian).

As you can see, it’s alerting on a field in my instance that has a global context. This was one I specifically created to test the optimizer, so it’s Ideally, you should limit your custom fields to only be available to the projects that need it. So you can use this tool to find those with global contexts and if necessary, fix them.

Merging Fields (App Required)

So, lets say you have a duplicate field. How do you copy the information from one to another so you can delete one without losing data?

Well, as you can guess, this isn’t something that is able to be done in vanilla JIRA – so we have to turn to the Atlassian Marketplace – and specifically Adaptavist’s Scriptrunner plugin.

Now my groovy – it needs some work. But Scriptrunner has a number of built in scripts, including one to “Copy custom field values”. This does what it says on the tin. It takes the value in a source field, and places it into a destination field, overwriting what was there. For most cases, this won’t be a problem for reasons I’ll talk about later, but do keep this in mind.

To use this, you will first need to save a filter that will limit what issues it will copy the fields on. I use something along the lines of:

"Custom Field A" is not EMPTY

This will return all issues that has the custom field set. You can add another clause to weed out those that have the destination field already set, and handle combining those manually. In most cases, each of the duplicate fields would have only been used on a project or two a piece, and have no overlap, so this shouldn’t be too much of a concern, but as always do your research first and make sure that is not the case in your situation.

Now, I shouldn’t have to say this, but always test any changes you want to make before doing it in production. That’s very true here. This built-in script isn’t perfect, and you may have to pick it apart and modify it a bit to get to your use case. The only way to know if this is required is to do your testing.

That being said, I asked my colleagues at Coyote Creek about merging fields, and Neil Taylor gave me this as a solutions he’s used before. From testing it, it’s the easiest to implement and execute that I’ve found, and should handle most of your use cases.

So time to get cleaning!

So you know how to find fields that are good candidates to removal, and have an action plan to migrate the data should they have anything worth keeping in them. What are you waiting for? Get out there, start conversations, and get yourself on a better path to better JIRA Performance.

Don’t forget to subscribe to receive new posts by email. It’s the easiest way to be notified as soon as the posts go live. You can sign up with the form at the bottom of the post. And don’t forget to check out our Atlassian Discord Chat!It’s always interesting to see who has popped up there and what’s being discussed. https://discord.gg/mXuRsVu

But until next time, this is Rodney, asking “Have you updated your JIRA issues today?”