How to identify group usage in Jira
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
This is an unsupported operation. The usage of groups may vary between different Jira versions.
Purpose
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.
Cause
The UI does not display exhaustive usage information. An improvement request has been filed for this on JRASERVER-36740 - Getting issue details... STATUS
Workaround
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.
Project Roles
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');Global Permissions
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');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');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 id