How to check which custom fields have global contexts and default values and aren't associated with any screens

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.

    

Purpose

Custom fields, when not used wisely and not governed properly, can put a lot of strain on Jira's performance. 

The good news is that you can be proactive with custom fields. Checkout Managing custom fields in Jira effectively to know how. 

If you're thinking of optimizing custom fields, checkout Optimizing custom fields

Since the custom field optimizer doesn't check for the existence of the fields in screens or whether or not the fields have default values, the query below may come in handy. 

Solution

The query below was written by Atlassian Support on best-effort basis for PostgreSQL. It is not officially supported by Atlassian and requests to rewrite it for a specific DBMS may not be fulfilled. 

The query will return custom fields info and the number of tickets this custom field is set in. 

Once you get the result of the query, make sure you validate that the fields reported do indeed satisfy all those conditions and proceed with the cleanup actions (removing default values, creating project-based contexts, etc...)

SELECT cf.id, cf.cfname, COUNT(cfv.issue)
FROM customfield cf
    LEFT JOIN customfieldvalue cfv ON cf.id = cfv.customfield
WHERE cf.id IN
    (
    SELECT DISTINCT SUBSTRING(ct.customfield, 13)::INTEGER 
    FROM configurationcontext ct 
        INNER JOIN fieldconfigscheme fcs ON ct.customfield = fcs.fieldid 
        INNER JOIN genericconfiguration gc ON fcs.id::CHAR(60) = gc.datakey 
        -- Field has a default value
    WHERE gc.datatype = 'DefaultValue' 
        AND ct.customfield LIKE 'customfield_%' 
        -- Context is global
        AND ct.project IS NULL
        -- Field is not present in any screen
        AND ct.customfield NOT IN (SELECT fieldidentifier
                                   FROM fieldscreenlayoutitem)
    )
GROUP BY cf.id;

Last modified on Jul 26, 2021

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.