nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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?

Accepted answers

Tom Lister_fo1j4e

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 :-)

All comments

shetty

Hi,

you can check Project name > project settings > summary.

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

Tom Lister_fo1j4e

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

maarten breesnee

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

Tom Lister_fo1j4e

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_fo1j4e

actually that looks wrong. but out of time for today

:-(

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.

maarten breesnee

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

Tom Lister_fo1j4e

did you run it ?

what else would you like to tweak?

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

Jarrod_b97s1x

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira