It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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 Leader Nov 06, 2018

try

 

SELECT P.pkey, p.lead, p.projecttype, nas.ASSOCIATION_TYPE,
nas.SINK_NODE_ENTITY,
NotificationScheme,
PermissionScheme,
WorkflowScheme,
IssueTypeScreenScheme,
FieldLayoutScheme

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 Leader 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 Leader Nov 08, 2018

Hi 

did you run it ?

what else would you like to tweak?

it works thanks.....

tom lister Community Leader Nov 08, 2018

Cool

hit me up for a question answered

Like maartenbreesnee likes this

@tom lister  Hi Tom, 

That's a great query, thanks :)

Would it be possible to additionally show IssueTypeScheme? Currently only IssueTypeScreenScheme is shown.

tom lister Community Leader Oct 16, 2019

Hi @Grzegorz Ziolek 

Good question. It isn't at all obvious where that data is stored. Not in the same place as the other scheme nodes as far as I can see.

I'll think about it for a while :-)

Tom

I think that this will give you all the names

select * from fieldconfigscheme where FIELDID = 'issuetype'

and it's connected with projects thru configurationcontext table where customfield = 'issuetype'

Hi @tom lister

One more thing, when running this query I get all projects except those using  Default schemes. 

Any idea how those can be included?

 

Regarding IssueTypeScheme, here is SQL that  gets that information

https://community.atlassian.com/t5/Jira-questions/Re-Re-Getting-the-issue-type-scheme-of-all-projects-in/qaq-p/1223395/comment-id/389985#M389985

Hi,

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 Leader Nov 06, 2018

with SQL you can try

SELECT P.pkey, p.lead, p.projecttype,
nas.SINK_NODE_ENTITY,
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?

pkeyleadprojecttypesink_node_entitynotificationschemepermissionschemeworkflowschemeissuetypescreenschemefieldlayoutscheme. 

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.

Regards,

Kristian

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
Posted in Jira

Watch Slack + Atlassian's Webinar: “Transform cross-functional collaboration with Slack + Atlassian

Hi Atlassian Community! We recently partnered together with Slack on a webinar, “ Transform cross-functional collaboration with Slack + Atlassian ”. Building fast, growing exponentially: all...

31 views 0 2
Join discussion

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you