JIra SQL on project configuration

I want to have an overview of the jira project configuation.

So a list of all project names, leads and then which workflow scheme, issuetype scheme, screen scheme and permission scheme they are using.

can somebody please help me with that?


4 answers

1 accepted

0 votes
Answer accepted
Tom Lister Community Champion Nov 06, 2018



SELECT P.pkey, p.lead, p.projecttype, nas.ASSOCIATION_TYPE,

FROM project p,
(select source_node_id, max(ASSOCIATION_TYPE) as association_type, max(sink_node_entity) as sink_node_entity, max(nid), max(nname) as NotificationScheme ,
max(pid), max(pname) as PermissionScheme,
max(wid), max(wname) as WorkflowScheme,
max(iid), max(iname) as IssueTypeScreenScheme,
max(fid), max(fname) as FieldLayoutScheme from nodeassociation
-- left join cwd_user on lead = user_name
left outer join ( select max(id) as nid , name as nname from notificationscheme group by name) ns on sink_node_id = ns.nid and sink_node_entity = 'NotificationScheme'
left outer join ( select max(id) as pid, name as pname from permissionscheme group by name) ps on sink_node_id = ps.pid and sink_node_entity = 'PermissionScheme'
left outer join ( select max(id) as wid, name as wname from workflowscheme group by name) ws on sink_node_id = ws.wid and sink_node_entity = 'WorkflowScheme'
left outer join( select max(id) as iid, name as iname from IssueTypeScreenScheme group by name) itss on sink_node_id = itss.iid and sink_node_entity = 'IssueTypeScreenScheme'
left outer join ( select max(id) as fid, name as fname from FieldLayoutScheme group by name) fls on sink_node_id = fls.fid and sink_node_entity = 'FieldLayoutScheme'
where association_type = 'ProjectScheme'
group by source_node_id) nas
where p.id = nas.source_node_id

order by pkey


no more time to refine further :-)

Tom Lister Community Champion Nov 06, 2018

actually that looks wrong. but out of time for today


@Tom Lister do you have time cuz it looks that you are so close?

Tom Lister Community Champion Nov 08, 2018


did you run it ?

what else would you like to tweak?

it works thanks.....

Tom Lister Community Champion Nov 08, 2018


hit me up for a question answered

Like 1 person likes this


you can check Project name > project settings > summary.

there you can find the overview of your project with all the required details

This is a very time consuming thing.... if you have more then 500 projects....

0 votes
Tom Lister Community Champion Nov 06, 2018

with SQL you can try

SELECT P.pkey, p.lead, p.projecttype,
ns.name as NotificationScheme,
ps.name as PermissionScheme,
ws.name as WorkflowScheme,
itss.name as IssueTypeScreenScheme,
fls.name as FieldLayoutScheme

FROM project p, nodeassociation nas
-- left join cwd_user on lead = user_name
left join notificationscheme ns on sink_node_id = ns.id and sink_node_entity = 'NotificationScheme'
left join permissionscheme ps on sink_node_id = ps.id and sink_node_entity = 'PermissionScheme'
left join workflowscheme ws on sink_node_id = ws.id and sink_node_entity = 'WorkflowScheme'
left join IssueTypeScreenScheme itss on sink_node_id = itss.id and sink_node_entity = 'IssueTypeScreenScheme'
left join FieldLayoutScheme fls on sink_node_id = fls.id and sink_node_entity = 'FieldLayoutScheme'
where p.id = nas.source_node_id
and source_node_entity = 'Project'
order by pkey

This is a good start but....I get this as an outcome.... Isn't it possible to get one row per project?


AASanosoftwareNotificationSchemeDefault Notification Schemenullnullnullnull
AASanosoftwareIssueTypeScreenSchemenullnullnullAAS: Kanban Issue Type Screen Schemenull
AASanosoftwareWorkflowSchemenullnullAAS: Software Simplified Workflow Schemenullnull
AASanosoftwarePermissionSchemenullDefault software schemenullnullnull

Hi Maarten,

Thank you for your question.

I can confirm that the Project Configurator add on has a Used by report feature which will allow you to see where configuration objects are referenced inside of Jira and this may help you to satisfy your requirement.

You can see further details on how the Used by report works in the documentation located here

If this response has answered your question can you please mark it as accepted so that other users searching for a similar question can see that this is a correct answer.



Unfortunatly this is not exactly where I am looking for. We have a list of projects and we want to identify which configurations they are using (if they are using a template then they are complient to certain regulations) its a combination of workflow scheme (transitions will make things filled in and force that someone with a project role PO approve things) issuetype scheme (makes sure that the issuetypes are matching the workflow) and screen scheme (makes sure the information that is triggered via workflow is available) and the permission scheme that make that only a certain role can create issues as an approved change requestor) If I do it via the used by report functionalitiy its still a lot of work.

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Mar 14, 2019 in Jira

Updates to jira.atlassian.com give you visibility into what's coming in Jira Server and Data Center

Hello, Community! My name is Gosia and I'm a Product Manager on Jira Server and Data Center here at Atlassian. Since 2002 when we launched our public issue tracker, jira.atlass...

719 views 1 15
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you