JIra SQL on project configuration

[maarten breesnee]

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?

[Tom Lister · Accepted solution]

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]

actually that looks wrong. but out of time for today

:-(

[maarten breesnee]

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

[Tom Lister]

Hi 

did you run it ?

what else would you like to tweak?

[maarten breesnee]

it works thanks.....

[Tom Lister]

Cool

hit me up for a question answered

[Grzegorz Ziolek]

@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]

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

[Grzegorz Ziolek]

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'

[Grzegorz Ziolek]

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

[Tom Lister]

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

[maarten breesnee]

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. 

AAS	ano	software	NotificationScheme	Default Notification Scheme	null	null	null	null
AAS	ano	software	IssueTypeScreenScheme	null	null	null	AAS: Kanban Issue Type Screen Scheme	null
AAS	ano	software	WorkflowScheme	null	null	AAS: Software Simplified Workflow Scheme	null	null
AAS	ano	software	PermissionScheme	null	Default software scheme	null	null	null

[Jarrod]

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

[Mark Ahnell]

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

[Kristian Walker _Adaptavist_]

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

[maarten breesnee]

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.

[shetty]

Hi,

you can check Project name > project settings > summary.

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

[maarten breesnee]

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