JIRA

Just a placeholder for useful Jira scripts / queries…

 

 

Find project roles granted to one or more groups…

SELECT
pra.roletypeparameter AS “Group”,
pr.name AS “Project Role”,
p.pname AS “Project”
FROM
jiraschema.projectroleactor pra
LEFT JOIN jiraschema.projectrole pr ON pra.projectroleid = pr.id
LEFT JOIN jiraschema.project p ON pra.pid = p.id
WHERE
pra.roletype = ‘atlassian-group-role-actor’
AND pra.roletypeparameter in (‘Group name 1′,’Group name 2’);

Find global permissions granted to one or more groups…

SELECT
gp.group_id AS “Group”,
gp.permission AS “Permission”
FROM
jiraschema.globalpermissionentry gp
WHERE
gp.group_id in (‘Group name 1′,’Group name 2’);

 

Find custom fields where groups are specified…

SELECT
cfv.stringvalue AS “Group(s)”,
cf.cfname AS “Custom Field”,
CONCAT(p.pkey, ‘-‘, ji.issuenum) AS “Issue”
FROM
jiraschema.customfieldvalue cfv
LEFT JOIN jiraschema.customfield cf ON cf.id = cfv.customfield
LEFT JOIN jiraschema.jiraissue ji ON cfv.issue = ji.id
LEFT JOIN jiraschema.project p ON ji.project = p.id
WHERE
cf.customfieldtypekey IN (
‘com.atlassian.jira.plugin.system.customfieldtypes:grouppicker’,
‘com.atlassian.jira.plugin.system.customfieldtypes:multigrouppicker’
)
AND cfv.stringvalue in (‘Group name 1′,’Group name 2’);

 

…and more examples here:

https://confluence.atlassian.com/jirakb/how-to-identify-group-usage-in-jira-441221524.html

Advertisements