Find where groups are used in Jira Data Center
Platform Notice: Data Center Only - This article only applies to Atlassian apps on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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.
*Except Fisheye and Crucible
Summary
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 it is used beyond what the current Group UI shows.
The UI does not display exhaustive usage information. An improvement request has been filed for this at JRASERVER-36740 - Extend Group administration page to show more use locations.
Solution
Query data from Jira Database with SQL
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');Custom Fields
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');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');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');Workflows
Option 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;Option 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');Permission Schemes
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');Comment visibility
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 idWas this helpful?