How to list Projects and Field Configurations by Issue Types in Jira
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
As a Jira Admin, we may want to list the Projects and Field Configurations associated with a specific Issue Type.
This is useful, for example, to list all Field Configurations that need to be changed if we want to make a field mandatory for a specific Issue Type on every Project that uses it.
Environment
Jira 7 or Jira 8.
The queries presented on this KB are for the Postgres DB and you may need to adjust them to your specific DB.
Solution
Run the query below on the Jira DB, replacing "Issue Type exact name" by the respective Issue Type name:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select it.pname as issue_type_name, cat.cname as category, p.pname as project_name, fcs.configname as issue_type_scheme, fls.name as field_configuration_scheme, fl.name as specific_field_config, fld.name as default_field_config
from project p
join configurationcontext cc on p.id = cc.project
join fieldconfigschemeissuetype fci on fci.fieldconfigscheme = cc.fieldconfigscheme
join optionconfiguration OC on OC.fieldconfig = FCI.fieldconfiguration
join issuetype it on it.id = OC.optionid
join fieldconfigscheme fcs on fcs.id = cc.fieldconfigscheme
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 = it.id
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
left join nodeassociation projcat on projcat.sink_node_entity = 'ProjectCategory' and projcat.source_node_id = p.id
left join projectcategory cat on cat.id = projcat.sink_node_id
where IT.pname = 'Issue Type exact name'; -- <=== REPLACE HERE
This should provide an output similar to:
1
2
3
4
5
issue_type_name | category | project_name | issue_type_scheme | field_configuration_scheme | specific_field_config | default_field_config
-----------------+------------+--------------+----------------------------------+----------------------------+-----------------------+----------------------
Test | Category 1 | Scrum One | SCRUM: Scrum Issue Type Scheme S | Scrum | | Scrum
Test | | Kanban One | KANBAN: Kanban Issue Type Scheme | Kanban FCS | Kanban FC | Kanban FC
(2 rows)In which the Field Configurations we're interested in are the specific_field_config. If it's empty, we fall back to the default_field_config (for that Issue Type in that Project).
Was this helpful?