Retrieve Project Schemes and Categories for all Jira projects via the database

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.

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

The Jira User interface does not provide an easy method of retrieving project schemes and settings. Currently, the View All Projects Page shows only their associated categories and lead information. We can query a list of this data, including the issue type scheme and permission scheme directly from the database.

Environment

8.x

Solution

The following query can be used to gather the details such as schemes and categories associated with a project.

Postgres
with cte as (select p.id as pid, p.pkey as prjkey,sink_node_id as AssocID, sink_node_entity as AssocScheme from project p join nodeassociation na on p.id = na.source_node_id and source_node_entity='Project') 
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,cname from project prj join cte on cte.pid=prj.id join ProjectCategory PC on cte.AssocID=PC.ID and cte.AssocScheme='ProjectCategory'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,NS.name from project prj join cte on cte.pid=prj.id join NotificationScheme NS on cte.AssocID=NS.ID and cte.AssocScheme='NotificationScheme'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,ISS.name from project prj join cte on cte.pid=prj.id join IssueSecurityScheme ISS on cte.AssocID=ISS.ID and cte.AssocScheme='IssueSecurityScheme'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,ITSS.name from project prj join cte on cte.pid=prj.id join IssueTypeScreenScheme ITSS on cte.AssocID=ITSS.ID and cte.AssocScheme='IssueTypeScreenScheme'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,WFS.name from project prj join cte on cte.pid=prj.id join WorkflowScheme WFS on cte.AssocID=WFS.ID and cte.AssocScheme='WorkflowScheme'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,PRS.name from project prj join cte on cte.pid=prj.id join PermissionScheme PRS on cte.AssocID=PRS.ID and cte.AssocScheme='PermissionScheme'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,FLS.name from project prj join cte on cte.pid=prj.id join FieldLayoutScheme FLS on cte.AssocID=FLS.ID and cte.AssocScheme='FieldLayoutScheme'
union
select prj.pkey as prjkey,prj.lead as Prjlead,INITCAP(fcs.fieldid)||'Scheme' as Category, fcs.configname from project prj join configurationcontext cct on cct.project=prj.id and customfield='priority' join fieldconfigscheme fcs on fcs.id=cct.fieldconfigscheme and fieldid='priority'
union
select prj.pkey as prjkey,prj.lead as Prjlead,INITCAP(fcs.fieldid)||'Scheme' as Category, fcs.configname from project prj join configurationcontext cct on cct.project=prj.id and customfield='issuetype' join fieldconfigscheme fcs on fcs.id=cct.fieldconfigscheme and fieldid='issuetype'
order by 1,3

Sample Output as below. You can remove the union and select some of the categories you do not want in the above query.

prjkey |    prjlead    |       category        |       cname                                  
---------+---------------+-----------------------+-------------------------------------------------------------------------
 SCRUM  | JIRAUSER10000 | FieldLayoutScheme     | Field Config Scheme for SCRUM
 SCRUM  | JIRAUSER10000 | IssueSecurityScheme   | Tester
 SCRUM  | JIRAUSER10000 | IssueTypeScreenScheme | SCRUM: Scrum Issue Type Screen Scheme
 SCRUM  | JIRAUSER10000 | IssuetypeScheme       | SCRUM: Scrum Issue Type Scheme
 SCRUM  | JIRAUSER10000 | NotificationScheme    | Default Notification Scheme
 SCRUM  | JIRAUSER10000 | PermissionScheme      | Default software scheme
 SCRUM  | JIRAUSER10000 | PriorityScheme        | Test_Priority
 SCRUM  | JIRAUSER10000 | ProjectCategory       | Linking_CatOne
 SCRUM  | JIRAUSER10000 | WorkflowScheme        | SCRUM: Software Simplified Workflow Scheme

For a more organized view of the data, you can make use of the crosstab in PostgreSQL and similar pivot functions in other databases. For Postgres, you need to enable the tablefunc extension by running the following in psql prompt

CREATE EXTENSION tablefunc;

Then proceed to run the following query to utilize the crosstab to transpose the columns to get an organized view of all the associated schemes and categories of a project

Postgres_Pivot
SELECT *  FROM crosstab($$with cte as (select p.id as pid, p.pkey as prjkey,sink_node_id as AssocID, sink_node_entity as AssocScheme from project p join nodeassociation na on p.id = na.source_node_id and source_node_entity='Project') 
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,cname from project prj join cte on cte.pid=prj.id join ProjectCategory PC on cte.AssocID=PC.ID and cte.AssocScheme='ProjectCategory'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,NS.name from project prj join cte on cte.pid=prj.id join NotificationScheme NS on cte.AssocID=NS.ID and cte.AssocScheme='NotificationScheme'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,ISS.name from project prj join cte on cte.pid=prj.id join IssueSecurityScheme ISS on cte.AssocID=ISS.ID and cte.AssocScheme='IssueSecurityScheme'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,ITSS.name from project prj join cte on cte.pid=prj.id join IssueTypeScreenScheme ITSS on cte.AssocID=ITSS.ID and cte.AssocScheme='IssueTypeScreenScheme'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,WFS.name from project prj join cte on cte.pid=prj.id join WorkflowScheme WFS on cte.AssocID=WFS.ID and cte.AssocScheme='WorkflowScheme'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,PRS.name from project prj join cte on cte.pid=prj.id join PermissionScheme PRS on cte.AssocID=PRS.ID and cte.AssocScheme='PermissionScheme'
union
select prjkey,prj.lead as Prjlead,cte.AssocScheme as Category,FLS.name from project prj join cte on cte.pid=prj.id join FieldLayoutScheme FLS on cte.AssocID=FLS.ID and cte.AssocScheme='FieldLayoutScheme'
union
select prj.pkey as prjkey,prj.lead as Prjlead,INITCAP(fcs.fieldid)||'Scheme' as Category, fcs.configname from project prj join configurationcontext cct on cct.project=prj.id and customfield='priority' join fieldconfigscheme fcs on fcs.id=cct.fieldconfigscheme and fieldid='priority'
union
select prj.pkey as prjkey,prj.lead as Prjlead,INITCAP(fcs.fieldid)||'Scheme' as Category, fcs.configname from project prj join configurationcontext cct on cct.project=prj.id and customfield='issuetype' join fieldconfigscheme fcs on fcs.id=cct.fieldconfigscheme and fieldid='issuetype'
order by 1,3$$,
$$
        SELECT distinct sink_node_entity from nodeassociation where source_node_entity='Project'
        union select 'PriorityScheme' 
        union select 'IssuetypeScheme' order by 1
$$) 
AS final_result(ProjectKey TEXT, ProjectLead TEXT, FieldLayoutScheme TEXT, IssueSecurityScheme TEXT,IssueTypeScreenScheme TEXT,IssueTypeScheme TEXT,NotificationScheme TEXT,PermissionScheme TEXT,PriorityScheme TEXT,ProjectCategory TEXT,WorkflowScheme TEXT);

Here is the sample output

  projectname   |  projectlead  |                       fieldlayoutscheme                       | issuesecurityscheme |              issuetypescreenscheme               |              issuetypescheme              |     notificationscheme      |                   permissionscheme                   |              priorityscheme              | projectcategory |                             workflowscheme                              
-----------------+---------------+---------------------------------------------------------------+---------------------+--------------------------------------------------+-------------------------------------------+-----------------------------+------------------------------------------------------+------------------------------------------+-----------------+-------------------------------------------------------------------------
 ADJAS           | JIRAUSER10000 |                                                               |                     | ADJAS: Scrum Issue Type Screen Scheme            |                                           | Default Notification Scheme | Default software scheme                              |                                          |                 | jdg-schema-for-project-adjamdjamensis
 ADRYSA          | JIRAUSER10000 |                                                               |                     | ADRYSA: Scrum Issue Type Screen Scheme           |                                           | Default Notification Scheme | Default software scheme                              |                                          | Linking_CatTwo  | jdg-schema-for-project-adryasa
 CCERE           | JIRAUSER10000 |                                                               |                     | CCERE: Scrum Issue Type Screen Scheme            |                                           | Default Notification Scheme | Default software scheme                              |                                          |                 | jdg-schema-for-project-ccatherinae
 CFORSA          | JIRAUSER10000 |                                                               |                     | CFORSA: Scrum Issue Type Screen Scheme           |                                           | Default Notification Scheme | Default software scheme                              |                                          |                 | jdg-schema-for-project-cfemoralisa
 CTLBIA          | JIRAUSER10000 |                                                               |                     | CTLBIA: Scrum Issue Type Screen Scheme           |                                           | Default Notification Scheme | Default software scheme                              |                                          |                 | jdg-schema-for-project-ctullbergia
 GBEI            | JIRAUSER10000 |                                                               |                     | GBEI: Scrum Issue Type Screen Scheme             |                                           | Default Notification Scheme | Default software scheme                              |                                          |                 | jdg-schema-for-project-gilbertii
 ITSM            | JIRAUSER10000 | Jira Service Desk Field Configuration Scheme for Project ITSM |                     | ITSM: Jira Service Desk Issue Type Screen Scheme | ITSM: Jira Service Desk Issue Type Scheme | Default Notification Scheme | Jira Service Desk Permission Scheme for Project ITSM | ITSM - Jira Service Desk Priority Scheme |                 | Jira Service Desk IT Support Workflow Scheme generated for Project ITSM
 SCRUM           | JIRAUSER10000 | Field Config Scheme for SCRUM                                 | Tester              | SCRUM: Scrum Issue Type Screen Scheme            | SCRUM: Scrum Issue Type Scheme            | Default Notification Scheme | Default software scheme                              | Test_Priority                            | Linking_CatOne  | SCRUM: Software Simplified Workflow Scheme

Last modified on Feb 10, 2023

Was this helpful?

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