How to get a list of Projects for Specific Custom Field Configuration and Scheme
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
In Jira you may want to get a list of projects that are using a custom field configuration. In the UI, it may not be possible to get this list if there are too many projects associated with the same default field configuration.
The UI will only list the first 9 or 16 projects and then will display "and xxx hidden projects".
Environment
8.20.x JSW DC and later
Diagnosis
Different from what is seen in this KB: How to get a list of projects using default field configuration in Jira.
We want to get a list of projects using a Specific Custom field configuration
Solution
It is possible to retrieve this information via the Jira database using the following SQL query:
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT DISTINCT p.pname AS project_name,
fls.name AS field_configuration_scheme,
fl.name AS specific_field_config,
fld.name AS field_configuration
FROM project p
JOIN configurationcontext cc ON p.id = cc.project
JOIN nodeassociation na ON na.sink_node_entity = 'FieldLayoutScheme' AND na.source_node_id = p.id
JOIN fieldlayoutscheme fls ON fls.id = na.sink_node_id
LEFT JOIN fieldlayoutschemeentity flse ON flse.scheme = fls.id AND flse.issuetype IS NULL
LEFT JOIN fieldlayout fl ON fl.id = flse.fieldlayout
LEFT JOIN fieldlayoutschemeentity flsed ON flsed.scheme = fls.id AND flsed.issuetype IS NULL
LEFT JOIN fieldlayout fld ON fld.id = flsed.fieldlayout
WHERE fld.name = 'Field_Config_Name';⚠️ Replace the 'Field_Config_Name' with your Field Configuration name.
Was this helpful?