Group Manager for Jira - Systems Workarounds

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.

Community Group:- JRASERVER-36740

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;

Workflows - 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

Related content:-

Search