Database query to check if a particular custom field has value and is used in any project/issues?

[Shriya Chhajed]

Hi, 

I want to cross check if the custom field is used somewhere or has values in it currently. I have checked with the JQL but not sure how to check if it is used in some script behind. Can you help me with two things: 

- How to be sure that the custom field is not used in any scripts or anywhere and thus safe to delete?  (Not attached in any screens though-its checked) 

- Query to check in database if the custom field is being used in any issues or any issues has value of that custom field? 

Help will be really appreciated!

Thanks and Regards

Shriya Chhajed

[Ignacio Pulgar · Accepted solution]

Hi Shriya,

Analyzing whether or not a specific custom field is actually being used in a Jira instance is a really interesting topic which could lead to a really extensive answer.

Deleting unnecessary custom fields is a maintenance task every Jira Administrator should eventually approach.

I'll add just some tips on this subject. Some general experience as a Jira Administrator is assumed. Should you need a deeper explanation on something, do not hesitate to ask.

The key tip here is: Instead of directly deleting a specific custom field, narrow the custom field context to an unused combination of project and issue type. In example, if the My Tasks (MT) project Issue Type Scheme uses just the Task issuetype, set the custom field context to that project but to a different issuetype, ie: Bug, so that no issues in the instance meet that context. The idea is to make the custom field unavailable as if it were deleted but without having deleted it yet, so that you can quickly rollback its context to its original state in case someone yells something had stopped working. This approach does not delete any custom field values from the database. Consider the field to be in quarantine. Once the field had remained unavailable for a long period of time with no collateral issues (maybe 3 to 6 months or so), then you may finally proceed with the complete deletion of that custom field with some warranties that the change is highly unlikely to cause any problems.

The previous tip is so powerful that it could even be used as a not too bad alternative to a deep analysis, if the environment is tolerant with short incidents happening until your Jira instance finally get rid of all its unused custom fields (a major win in the long run). If multiple custom fields are candidates for deletion, you may put them in quarantine at a rate of one custom field every two weeks or so, so that you immediately know which custom field's context should be restored in case any problem arose.

Please, note that this quarantine technique is recommended even if a deep analysis had been performed.

Should you want to analyze the custom field usage before taking any actions, please, continue reading.

Regarding querying the database directly, please, mind there are some custom field types that are not stored in the customfieldvalue table. Besides, only SELECT sentences are safe enough to be considered for our purpose. Cannot stress enough how important it is to avoid modifying the database directly through SQL, which is considered to be a really risky and bad practice.

Leonard's SQL query might help you too.

The first and most simple approach for analyzing custom field usage should be trying the JQL Nic suggested (the query syntax might vary for certain custom field types):

<"custom field name"> is not EMPTY

Should the previous query return any issues, we may analyze its distinct values. Saving the query as a filter for feeding a Pie Chart or Issue Statistics gadget based on that custom field may help, although not all custom field types are supported by said gadgets.

Sometimes, you might find that a custom field is apparently being used, because values are assigned to some issues, but it is always the very same value, set as the default value in the custom field configuration. That kind of "usage" couldn't probably be considered as an evidence of the field being actually used.

Regarding the potential usage of the custom field in scripts, assuming we were referring to ScriptRunner, you may find all code directly configured on your Jira instance through the Script Registry. There you could look for (Ctrl + F) the custom field name and custom field id.

Please, note that some scripts might be stored in a system file. If that were the case, the Script Registry would show the system file path, and you should also look for the custom field name and id in said files.

Storing scripts in a system file is not a very frequent practice, but it is indeed a good practice in some specific use cases, ie: the very same script is used multiple times in many different places, or access to the script should be further controlled because it contains sensitive data or just to prevent Jira admins from modifying it.

The deletion of a custom field which were being explicitly used in any filters would break them, together with any gadgets fed by them. The following query for PostgreSQL might help to determine if the custom field is present in any saved JQL filters:

SELECT * FROM searchrequest WHERE reqcontent ILIKE '%Custom Field Name%' OR reqcontent ILIKE '%cf[00000]%'

Where:

• ILIKE is PostgreSQL specific syntax, and it's for making case-insensitive the search for the custom field name. If ILIKE is not supported by your DBMS, try substituting it with LIKE.
• Custom Field Name should be substituted with the custom field you'd like to analyze.
• 00000 should be substituted with the custom field id.

The custom field could also be used in workflow definitions (ie: post-functions, conditions, validators...). To check whether a field is present in any workflow definitions, you may execute the following query, which returns all workflow names where the custom field is being used:

SELECT wf.workflowname
FROM jiraworkflows wf
WHERE wf.descriptor LIKE '%customfield_00000%'
OR wf.descriptor LIKE '%{00000}%'
OR wf.descriptor ILIKE '%cf[00000]%'

Where the previously commented considerations about ILIKE and 00000 apply.

The following query would show all custom fields used in any workflows, together with the workflow names in which they were used:

SELECT DISTINCT cf.id, cf.cfname, wf.workflowname
FROM customfield cf, jiraworkflows wf
WHERE wf.descriptor LIKE '%customfield_' || cf.id || '%'
OR wf.descriptor LIKE '%{' || cf.id || '}%'
OR wf.descriptor LIKE '%cf[' || cf.id || ']%'
ORDER BY 2 ASC

If there were any external sources calling the Jira REST API, ie: for integrations with third party tools, the presence of the custom field in some screens might be required for the underlying configuration to work. This is a long shot, and I think it is quite unlikely that an apparently unnecessary custom field were actually essential for an integration to work, but if you know your Jira instance is integrated with any other tools, further investigation should take place on that regard.

Field Configurations could also be analyzed, but I don't think the deletion of a custom field would result in any issues, ie: caused by that field being set as a required one in a Field Configuration.

There might be more ways a custom field could be considered to be as 'being used', although I think we have covered the main usages and potential impact of custom field deletions.

Regards

[Shriya Chhajed]

Hi @Ignacio Pulgar 

Thank you so much for such a good explanation and possibilities for finding the use of field in Jira :) 

Thanks and Regards

Shriya Chhajed

[Nic Brough -Adaptavist-]

Have you tried running the JQL:  

Team is not empty

?

[Shriya Chhajed]

Hi @Nic Brough -Adaptavist- 

Yes, i tried searching the values using the JQL. Even if, no values are visible in the issues, is it safe to delete the Custom Field? I mean how can we confirm it is not used somewhere inside the script or something, 

Regards

Shriya Chhajed

[Leonard Chew]

Hi there

Here's an Oracle Database Query to list the items where the Custom Field 'Team' is not empty.
Change 'Team' with your own custom field

select ji.id, 
       p.pkey||'-'||ji.issuenum issuekey, 
       ji.summary, 
       it.pname issuetype,
coalesce(to_char(cus_fld.textvalue),cus_fld.stringvalue,to_char(cus_fld.numbervalue),to_char(cus_fld.datevalue)) fld_value
from jiraissue ji
join project   p on (ji.project = p.id)
join issuetype it on (ji.issuetype = it.id)
left join    ( select cfv.id value_id, 
                      cfv.numbervalue, 
                      cfv.textvalue, 
                      cfv.stringvalue, 
                      cfv.datevalue, 
                      cfv.issue
               from customfieldvalue cfv 
               join customfield cf on (cfv.customfield = cf.id
                                       and cf.cfname = 'Team')
               ) cus_fld on (ji.id = cus_fld.issue)
where cus_fld.value_id is not null;

Regarding your question whether the field is referenced by a script, probably depends on your add-ons installed.

[Shriya Chhajed]

Hi   @Leonard Chew 

Thank you for the database query. Regarding the use of field in script, i will try following the suggestions from Ignacio :)

[Marc Isikoff]

So no native ability to see if a custom field value exists in a Jira project so we don't keep adding duplicate records?

This seems so elementary for a products like any service desk (hey don't let the same team put in a duplicate record for themselves - we only want one record per team).

I would suspect we will leave Atlassian for Broadcom if this elementary functionality cannot be implemented without some costly add-on product.