Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
0 / 0 points
Next:
badges earned

Your Points Tracker
Challenges
Leaderboard
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Recognition
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Kudos
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

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?

 

5 answers

1 accepted

1 vote
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 maarten breesnee 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

2 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

This is Toms original query, but with a few fixes:

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) sink_node_entity, max(nid) nid, max(nname) as NotificationScheme ,
max(pid) pid, max(pname) as PermissionScheme,
max(wid) wid, max(wname) as WorkflowScheme,
max(iid) IID, max(iname) as IssueTypeScreenScheme,
max(fid) 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) AS nas
where p.id = nas.source_node_id

order by pkey

Like tom lister likes this

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....

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.

Thanks @tom lister!  I tweaked it to produce the scheme result in a single column:

SELECT
p.pname, p.pkey,
nas.SINK_NODE_ENTITY as 'SchemeType',
CAST(
CASE
WHEN sink_node_entity = 'PermissionScheme' THEN ps.name
WHEN sink_node_entity = 'NotificationScheme' THEN ns.name
WHEN sink_node_entity = 'WorkflowScheme' THEN ws.name
WHEN sink_node_entity = 'IssueTypeScreenScheme' THEN itss.name
WHEN sink_node_entity = 'FieldLayoutScheme' THEN fls.name
END AS varchar) as 'SchemeName'
FROM
project p, nodeassociation nas
left join notificationscheme ns on nas.SINK_NODE_ID = ns.id and nas.SINK_NODE_ENTITY = 'NotificationScheme'
left join permissionscheme ps on nas.SINK_NODE_ID = ps.id and nas.SINK_NODE_ENTITY = 'PermissionScheme'
left join workflowscheme ws on nas.SINK_NODE_ID = ws.id and nas.SINK_NODE_ENTITY = 'WorkflowScheme'
left join IssueTypeScreenScheme itss on nas.SINK_NODE_ID = itss.id and nas.SINK_NODE_ENTITY = 'IssueTypeScreenScheme'
left join FieldLayoutScheme fls on nas.SINK_NODE_ID = fls.id and nas.SINK_NODE_ENTITY = 'FieldLayoutScheme'
WHERE
p.id = nas.SOURCE_NODE_ID and nas.SOURCE_NODE_ENTITY = 'Project'
ORDER BY
p.pkey, nas.SINK_NODE_ENTITY

Suggest an answer

Log in or Sign up to answer
TAGS

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