One critical caveat before running any queries: some “empty” fields aren’t actually empty.

Jira Service Management, Jira Software (the agile features), Capture, and Portfolio for Jira store their custom field data in separate datastores — not in the standard customfieldvalue table. If you query that table, these fields will appear unused even when they’re actively in use.

Every query below excludes these field types:

AND cf.CUSTOMFIELDTYPEKEY NOT LIKE '%com.atlassian.servicedesk%'
AND cf.CUSTOMFIELDTYPEKEY NOT LIKE '%com.pyxis.greenhopper%'
AND cf.CUSTOMFIELDTYPEKEY NOT LIKE '%com.atlassian.bonfire%'
AND cf.CUSTOMFIELDTYPEKEY NOT LIKE '%com.atlassian.jpo%'

These fields exist in your system but have never contained data. Zero values. Ever.

SELECT cf.id, cf.cfname, cf.description, cf.CUSTOMFIELDTYPEKEY
FROM customfield cf
LEFT JOIN customfieldvalue cfv ON cf.id = cfv.customfield
WHERE cfv.id IS NULL
AND cf.CUSTOMFIELDTYPEKEY NOT LIKE '%com.atlassian.servicedesk%'
AND cf.CUSTOMFIELDTYPEKEY NOT LIKE '%com.pyxis.greenhopper%'
AND cf.CUSTOMFIELDTYPEKEY NOT LIKE '%com.atlassian.bonfire%'
AND cf.CUSTOMFIELDTYPEKEY NOT LIKE '%com.atlassian.jpo%'
ORDER BY cf.cfname;

What you’ll find:

• Fields created “just in case” that were never used
• Fields from plugins that were uninstalled
• Duplicate fields created by mistake and then abandoned
• Test fields someone forgot to delete

These fields have data, but barely. Maybe 5 issues out of 100,000.

SELECT COUNT(*) as usage_count, cf.id, cf.cfname, cf.CUSTOMFIELDTYPEKEY
FROM customfield cf
LEFT JOIN customfieldvalue cfv ON cf.id = cfv.customfield
WHERE cf.CUSTOMFIELDTYPEKEY NOT LIKE '%com.atlassian.servicedesk%'
AND cf.CUSTOMFIELDTYPEKEY NOT LIKE '%com.pyxis.greenhopper%'
AND cf.CUSTOMFIELDTYPEKEY NOT LIKE '%com.atlassian.bonfire%'
AND cf.CUSTOMFIELDTYPEKEY NOT LIKE '%com.atlassian.jpo%'
AND cfv.id IS NOT NULL
GROUP BY cf.id, cf.cfname, cf.CUSTOMFIELDTYPEKEY
HAVING COUNT(*) < 50
ORDER BY usage_count ASC;

Adjusting the threshold:

The < 50 threshold is a starting point. Scale it to your instance:

The 0.1% rule comes from The Jira Guy's recommendation: if fewer than one-tenth of one percent of issues use a field, it's worth questioning whether that field should exist globally.

What you'll find:

• Project-specific fields that got global context by mistake
• Fields from completed/archived projects
• Experimental fields that didn't get adoption
• Fields replaced by newer alternatives but never cleaned up

The classic "which Priority field is the real one?" problem.

SELECT cfname, COUNT(*) as duplicate_count
FROM customfield
GROUP BY cfname
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;

What you'll find:

• Fields created by different admins who didn't check existing fields
• Test/production duplicates that both ended up in production
• Fields with slightly different configurations (different options, different contexts) but same name

Next step: For each duplicate, run this to see which projects actually use each variant:

SELECT DISTINCT p.pkey as project_key, cf.id, cf.cfname
FROM customfield cf
JOIN customfieldvalue cfv ON cf.id = cfv.customfield
JOIN jiraissue ji ON cfv.issue = ji.id
JOIN project p ON ji.project = p.id
WHERE cf.cfname = 'Your Duplicate Field Name'
ORDER BY p.pkey, cf.id;

These fields exist, have historical data, but haven't been touched in a long time.

SELECT cf.id, cf.cfname
FROM customfield cf
WHERE cf.cfname NOT IN (
    SELECT item.field 
    FROM changeitem item
    JOIN changegroup cg ON item.groupid = cg.id
    WHERE item.fieldtype = 'custom' 
    AND cg.created > '2024-01-01'
)
AND cf.CUSTOMFIELDTYPEKEY NOT LIKE '%com.pyxis.greenhopper%'
AND cf.CUSTOMFIELDTYPEKEY NOT LIKE '%com.atlassian.servicedesk%'
AND cf.CUSTOMFIELDTYPEKEY NOT LIKE '%com.atlassian.bonfire%'
AND cf.CUSTOMFIELDTYPEKEY NOT LIKE '%com.atlassian.jpo%'
ORDER BY cf.cfname;

Adjust the date ('2024-01-01') to your preferred lookback period:

What you'll find:

• Fields from deprecated processes
• Fields tied to completed long-term projects
• Fields that teams stopped using but never formally retired
• Historical tracking fields no longer relevant

Not all plugin fields are bad, but it's useful to know which plugins have left their mark:

SELECT cf.cfname, cf.CUSTOMFIELDTYPEKEY
FROM customfield cf
WHERE cf.CUSTOMFIELDTYPEKEY NOT LIKE '%com.atlassian%'
AND cf.CUSTOMFIELDTYPEKEY NOT LIKE '%com.pyxis%'
ORDER BY cf.CUSTOMFIELDTYPEKEY, cf.cfname;

What you'll find:

• Fields from currently installed plugins (expected)
• Fields from uninstalled plugins (cleanup candidates)
• Fields from trial plugins you tested years ago

Cross-reference with your installed plugins list. Any field type from a plugin that's no longer installed is safe to investigate for deletion.

After running all four queries, you should have:

Create a spreadsheet with columns:

• Field ID
• Field Name
• Category (Unused/Low/Duplicate/Abandoned)
• Plugin source (if applicable)
• Decision (Delete/Keep/Investigate/Merge)
• Notes

This becomes your cleanup roadmap.