How to identify group usage in Jira
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Fisheye および Crucible は除く
This is an unsupported operation. The usage of groups may vary between different Jira versions.
目的
You want to delete or modify a group in Jira, but want to know where the group is used beyond what the current Group UI shows you.
原因
The UI does not display exhaustive usage information. An improvement request has been filed for this on JRASERVER-36740 - Getting issue details... STATUS
回避策
The short term workaround is to use SQL queries to get this information
All queries below were composed for PostgreSQL databases and may need slight syntax adjustments depending on the DBMS Jira's database is based on.
Replace ('helpdesk', 'administrators') with a comma-separated list of the groups you want to check for usages.
プロジェクト ロール
SELECT
pra.roletypeparameter AS "Group",
pr.name AS "Project Role",
p.pname AS "Project"
FROM
projectroleactor pra
LEFT JOIN projectrole pr ON pra.projectroleid = pr.id
LEFT JOIN project p ON pra.pid = p.id
WHERE
pra.roletype = 'atlassian-group-role-actor'
AND pra.roletypeparameter in ('helpdesk', 'administrators');グローバル権限
SELECT
gp.group_id AS "Group",
gp.permission AS "Permission"
FROM
globalpermissionentry gp
WHERE
gp.group_id in ('helpdesk', 'administrators');カスタム フィールド
SELECT
cfv.stringvalue AS "Group(s)",
cf.cfname AS "Custom Field",
CONCAT(p.pkey, '-', ji.issuenum) AS "Issue"
FROM
customfieldvalue cfv
LEFT JOIN customfield cf ON cf.id = cfv.customfield
LEFT JOIN jiraissue ji ON cfv.issue = ji.id
LEFT JOIN 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 ('helpdesk','administrators');Shared Dashboards
SELECT
shp.param1 AS "Group",
pp.pagename AS "Dashboard"
FROM
sharepermissions shp
LEFT JOIN portalpage pp ON shp.entityid = pp.id
WHERE
shp.entitytype = 'PortalPage'
AND shp.sharetype = 'group'
AND shp.param1 IN ('helpdesk', 'administrators');Shared Filters
SELECT
shp.param1 AS "Group",
sr.filtername AS "Filter"
FROM
sharepermissions shp
LEFT JOIN searchrequest sr ON shp.entityid = sr.id
WHERE
shp.entitytype = 'SearchRequest'
AND shp.sharetype = 'group'
AND shp.param1 IN ('helpdesk', 'administrators');ワークフロー
オプション 1
Since workflows are stored in XML on Jira's database, it's necessary to export the values from the "Descriptor" column and search for the group's name.
SELECT
jw.workflowname AS "Workflow",
jw.descriptor AS "Descriptor"
FROM
jiraworkflows jw;オプション 2
Alternatively, you can use the "like" operator on the descriptor to check if it contains the name of the groups you are looking for. Just a note that this query might not be fully accurate. For example, if the workflow descriptor could contain the word "administrators", without necessarily referring to the "administrator" group. Therefore, make sure to review the output of this query to make sure that it's valid:
SELECT
jw.workflowname AS "Workflow",
jw.descriptor AS "Descriptor"
FROM
jiraworkflows jw
WHERE
jw.descriptor like '%helpdesk%'
OR
jw.descriptor like '%administrators%';Filter Subscriptions
SELECT
fs.groupname AS "Group",
sr.filtername AS "Filter Name"
FROM
filtersubscription fs
LEFT JOIN searchrequest sr ON fs.filter_i_d = sr.id
WHERE
fs.groupname IN ('helpdesk','administrators');Board Administrators (Jira Agile)
SELECT
ba."KEY" AS "Group",
rv."NAME" AS "Board"
FROM
"AO_60DB71_BOARDADMINS" ba
LEFT JOIN "AO_60DB71_RAPIDVIEW" rv ON ba."RAPID_VIEW_ID" = rv."ID"
WHERE
ba."TYPE" = 'GROUP';Application Access (Jira 8.x)
SELECT
license_role_name AS "Application",
group_id AS "Group"
FROM
licenserolesgroup
WHERE
group_id in ('helpdesk','administrators');Saved Filters content
Groups can also be stated in a Filter's query, so for each group, search for it and adjust the lowercase function to your database accordingly.
SELECT
id AS "Filter ID",
filtername AS "Filter Name",
reqcontent AS "JQL"
FROM
searchrequest
WHERE
LOWER(reqcontent) like '%helpdesk%';Notification Schemes
select n.*
from notification n
where n.notif_type = 'Group_Dropdown'
and n.notif_parameter in ('helpdesk','administrators');権限スキーム
SELECT
SP.id,SP.perm_parameter AS GroupName
FROM
schemepermissions SP
INNER JOIN
permissionscheme PS ON SP.scheme = PS.id
WHERE
SP.perm_type = 'group'
AND SP.perm_parameter in ('groupname');
-- Scheme Permissions Granted to Group ---
SELECT
SP.perm_parameter AS GroupName, PS.name AS PermissionSchemeName, SP.permission_key AS Permission
FROM
schemepermissions SP
INNER JOIN
permissionscheme PS ON SP.scheme = PS.id
WHERE
SP.perm_type = 'group'
AND SP.perm_parameter in ('groupname');コメントの表示設定
select ja.id,ja.issueid,ja.actiontype,ja.actionlevel
from jiraaction ja
where ja.actionlevel in ('helpdesk', 'administrators');Issue security level
select sis.*
from schemeissuesecurities sis
where sis.sec_type = 'group'
and sis.sec_parameter in ('helpdesk', 'administrators');Work log visibility
select wl.id,wl.issueid,wl.grouplevel
from worklog wl
where wl.grouplevel in ('helpdesk', 'administrators');Automation rules (Automation For Jira)
select * from AO_589059_RULE_CONFIG where id in
(select rule_config_id from AO_589059_rule_cfg_component where value like '%<group name>%')
order by id