Use case: Custom field usage across projects

Custom fields pile up. A field added for one project ends up in every project’s context, another is on three screens that nobody fills in, and after a few years nobody knows which ones still matter. Jira’s custom fields page shows each field on its own; it does not compare projects or tell you whether anyone puts values in.

These SQL reports answer the questions behind a custom field cleanup:

• Which custom fields do we have, how many projects and screens is each on, and when was it last used?
• Which projects have which fields, as a project-to-project matrix?
• Which fields does each project actually fill in, and which does it have but never use?
• Is usage going up or down, month by month?

Set up the export

1. Create an export whose work items cover the projects you want to compare, with JQL such as:

   project in (APP, OPS, MKT) AND created >= -365d ORDER BY key

   A year of work items is usually enough to see what is used. A run reads up to 20,000 work items; for larger projects, narrow the period.

2. Add the custom fields you want to audit. Each one becomes a column of work_items, which is where the usage queries count values. To audit every custom field, select them all in the field picker. The inventory and the matrix (the first three queries) do not need them: they read fields and project_fields, which cover every custom field on the site.

3. Under Output, choose SQL query.

4. Open Settings and turn on Include project fields, under Used by the SQL step. It fills the project_fields table: which custom fields each project has. Without it the table is empty and greyed out in the table list. It costs one Jira request per project, so the data takes a little longer to build.

5. Paste one of the queries below, choose Run on sample (choose Refresh first if the sample was built before you turned on project fields), and Save.

You do not need change history for these reports.

What “has a field” means

A project has a custom field when one of the field’s contexts covers the project, including a global context, or when a team-managed project created it. That is what Jira lets you set on the project’s work items. It is not the same as being on the project’s view screen: a field can apply to a project and sit on none of its screens. To see a project’s screens, open its issue type screen scheme in Jira’s project settings.

The tables

fields has one row per field on the site. Besides id, name and custom, these columns describe how widely a field is set up, as Jira’s custom fields page shows it:

Column	Type	Meaning
screens_count	INTEGER	Screens the field is on, across the site; 0 for a field on no screen
contexts_count	INTEGER	Field contexts: which projects and work types it applies to
projects_count	INTEGER	Projects the field applies to, across the site
last_used	TIMESTAMP WITH TIME ZONE	When a value of the field last changed, anywhere on the site. Empty (NULL) when Jira does not track it for the field, as for most system and app fields
description	VARCHAR	The field’s description
is_locked	BOOLEAN	true for a field managed by Jira or an app

project_fields has one row for each project in the export and each custom field it has: project_id (joins projects.id and work_items.project) and field_id (joins fields.id).

Both come from Jira’s field search, which may need the Administer Jira permission. If Jira does not return them for the export’s owner, the columns and the table are empty, and the table’s description in the editor says why.

1. Custom field inventory

Every custom field, with how widely it is set up and when it was last used. The fields at the top are the cleanup candidates.

SELECT
    f.name AS field,
    f.id AS field_id,
    f.projects_count,
    f.screens_count,
    f.contexts_count,
    f.last_used::DATE AS last_used,
    CASE
        WHEN f.screens_count = 0 THEN 'on no screen'
        WHEN f.last_used < current_date - INTERVAL 1 YEAR THEN 'not used for a year'
    END AS cleanup_hint
FROM fields AS f
WHERE f.custom
ORDER BY f.last_used NULLS FIRST, f.name

The names and numbers in the example results on this page are made up, to show the shape of each result.

field	field_id	projects_count	screens_count	contexts_count	last_used	cleanup_hint
Legacy code	customfield_10105	9	0	1	2023-02-01	on no screen
Go-live date	customfield_10104	1	1	1	2026-08-20
Platforms	customfield_10103	1	1	1	2026-09-02
Severity	customfield_10102	2	2	2	2026-09-10
Team	customfield_10100	2	4	1	2026-10-30
Story points	customfield_10101	3	6	1	2026-11-01

2. Project-to-project matrix

One row per custom field, one column per project: 1 where the project has the field, 0 where it does not. List your project keys in IN (...): a query must be a single statement, and DuckDB needs the list to build the columns in one.

PIVOT (
    SELECT f.name AS field, p.key AS project
    FROM project_fields AS pf
    JOIN fields AS f ON f.id = pf.field_id
    JOIN projects AS p ON p.id = pf.project_id
)
ON project IN ('APP', 'OPS', 'MKT')
USING count(*)
GROUP BY field
ORDER BY field

field	APP	OPS	MKT
Go-live date	0	0	1
Legacy code	1	1	1
Platforms	1	0	0
Severity	1	1	0
Story points	1	1	1
Team	1	1	0

3. Fields shared across projects

The same comparison as a list, with no project keys to type: how many of the projects each field is in, and which ones. Fields in one project only are candidates for a project-specific context; fields in every project are your common set.

WITH compared AS (SELECT count(DISTINCT project_id) AS n FROM project_fields)
SELECT
    f.name AS field,
    count(*) AS projects_with_field,
    (SELECT n FROM compared) AS projects_compared,
    string_agg(p.key, ', ' ORDER BY p.key) AS projects
FROM project_fields AS pf
JOIN fields AS f ON f.id = pf.field_id
JOIN projects AS p ON p.id = pf.project_id
GROUP BY f.name
ORDER BY projects_with_field DESC, field

field	projects_with_field	projects_compared	projects
Legacy code	3	3	APP, MKT, OPS
Story points	3	3	APP, MKT, OPS
Severity	2	3	APP, OPS
Team	2	3	APP, OPS
Go-live date	1	3	MKT
Platforms	1	3	APP

4. How often each project fills each field

Having a field is not using it. This query counts, for each project and each field it has, how many of the project’s work items have a value.

WITH filled AS (
    UNPIVOT (SELECT id, project, COLUMNS('^customfield_\d+$')::VARCHAR FROM work_items)
    ON COLUMNS('^customfield_') INTO NAME field_id VALUE value
),
per_project AS (
    SELECT project, count(*) AS work_items FROM work_items GROUP BY project
)
SELECT
    p.key AS project,
    f.name AS field,
    pp.work_items,
    count(fl.id) AS with_value,
    round(100.0 * count(fl.id) / pp.work_items, 1) AS pct_filled
FROM project_fields AS pf
JOIN projects AS p ON p.id = pf.project_id
JOIN fields AS f ON f.id = pf.field_id
JOIN per_project AS pp ON pp.project = pf.project_id
LEFT JOIN filled AS fl ON fl.project = pf.project_id AND fl.field_id = pf.field_id AND fl.value NOT IN ('', '[]')
GROUP BY p.key, f.name, pp.work_items
ORDER BY project, pct_filled DESC, field

project	field	work_items	with_value	pct_filled
APP	Story points	412	412	100
APP	Platforms	412	275	66.7
APP	Team	412	275	66.7
APP	Severity	412	137	33.3
APP	Legacy code	412	0	0
OPS	Severity	180	180	100
OPS	Team	180	90	50
OPS	Legacy code	180	0	0
OPS	Story points	180	0	0

How it works:

• UNPIVOT turns each work item’s custom field columns into rows, one per field with a value. Each column is cast to text first (::VARCHAR), so text, number, date and select fields can be counted the same way. Empty fields are left out; an emptied multi-select field reads [], so the query leaves that out too.
• project_fields decides which fields are listed for each project, so a field the project has but never fills shows up with 0, rather than not at all.
• Only fields in the export are counted. A custom field that is not among the export’s fields has no column, so it shows 0 here. Add it to the export to count it.

5. Fields a project has but never uses

The fields from the previous query with no value on any of the project’s work items. They are the first to remove from that project’s screens or context.

WITH filled AS (
    UNPIVOT (SELECT id, project, COLUMNS('^customfield_\d+$')::VARCHAR FROM work_items)
    ON COLUMNS('^customfield_') INTO NAME field_id VALUE value
)
SELECT p.key AS project, f.name AS field, f.last_used::DATE AS last_used_anywhere
FROM project_fields AS pf
JOIN projects AS p ON p.id = pf.project_id
JOIN fields AS f ON f.id = pf.field_id
WHERE NOT EXISTS (
    SELECT 1 FROM filled AS fl
    WHERE fl.project = pf.project_id AND fl.field_id = pf.field_id AND fl.value NOT IN ('', '[]')
)
ORDER BY project, field

project	field	last_used_anywhere
APP	Legacy code	2023-02-01
MKT	Legacy code	2023-02-01
MKT	Story points	2026-11-01
OPS	Legacy code	2023-02-01
OPS	Story points	2026-11-01

last_used_anywhere is site-wide: Story points is still used, just not in MKT or OPS. As in the previous query, a field that is not among the export’s fields is listed here too, since there is nothing to count it in: add every field you want to judge to the export.

6. Usage trend, month by month

The share of work items created each month that have each field filled. A falling share means a field is being abandoned; a rising one, that it is catching on.

WITH filled AS (
    UNPIVOT (SELECT id, created, COLUMNS('^customfield_\d+$')::VARCHAR FROM work_items)
    ON COLUMNS('^customfield_') INTO NAME field_id VALUE value
),
months AS (
    SELECT date_trunc('month', created)::DATE AS month, count(*) AS created_items FROM work_items GROUP BY 1
)
SELECT
    m.month,
    f.name AS field,
    count(fl.id) AS with_value,
    m.created_items,
    round(100.0 * count(fl.id) / m.created_items, 1) AS pct_filled
FROM filled AS fl
JOIN fields AS f ON f.id = fl.field_id
JOIN months AS m ON m.month = date_trunc('month', fl.created)::DATE
WHERE fl.value NOT IN ('', '[]')
GROUP BY m.month, f.name, m.created_items
ORDER BY f.name, m.month

month	field	with_value	created_items	pct_filled
2026-07-01	Severity	96	96	100
2026-09-01	Severity	52	104	50
2026-07-01	Team	96	96	100
2026-08-01	Team	44	88	50

A month in which no work item filled a field has no row, rather than a 0. The value is “filled now”: a field cleared after the work item was created does not count for that month.

Adjusting the queries

• One project’s trend. In query 6, add WHERE project = '10001' (the project’s id, from projects) to the SELECT inside UNPIVOT and to the months step.
• A threshold. To list fields filled on fewer than 10% of a project’s work items, wrap query 4 and keep WHERE pct_filled < 10.
• System fields too. Query 1 lists them if you drop WHERE f.custom; most have no last_used, because Jira does not track it for them.
• One sheet per question. Each query is one export. To deliver them together, save each as its own export and schedule them at the same time.

Schedule it

A monthly run of queries 4 and 6 keeps a record of field usage over time, without anyone rebuilding the spreadsheet by hand.

Or ask an AI agent

The Rovo agent, or an AI assistant such as Claude connected through MCP, can build these for you. Copy this prompt, and change the project keys to yours:

Create a report called "Custom field usage" for projects APP, OPS and MKT, reading
work items created in the last 365 days with all custom fields, and with project
fields included. For each project and each custom field the project has, show the
number of work items, how many have a value in the field, and the percentage,
ordered by project and then percentage, highest first.

Show me the result on my data first.

Once I confirm, save it as a SQL export and schedule it to run on the 1st of every
month at 8:00, emailing it to me as an Excel (XLSX) file.

The agent asks you to confirm each step that saves something: the export, the query, and the schedule.