How to identify group usage in JIRA

Still need help?

The Atlassian Community is here for you.

Ask the community

This article requires fixes

This article has been Flagged for fixing. Use caution when using it and fix it if you have Publisher rights.

 

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

JIRA 6.0.x and before
SELECT
  cfv.stringvalue AS "Group(s)",
  cf.cfname AS "Custom Field",
  ji.pkey AS "Issue"
FROM
  customfieldvalue cfv
  LEFT JOIN customfield cf ON cf.id = cfv.customfield
  LEFT JOIN jiraissue ji ON cfv.issue = ji.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');
JIRA 6.1+
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

(info) 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;

Filter Subscriptions

SELECT
  fs.groupname AS "Group"
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'
  AND ba."KEY" IN ('helpdesk','administrators');

Application Access (JIRA 7+)

SELECT
	license_role_name AS "Application",
	group_id AS "Group"
FROM
	licenserolesgroup
WHERE
	group_id in ('helpdesk','administrators');

Saved Filters content

(info) 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
  *
FROM
  notification
WHERE
  notif_type LIKE '%Group%';

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');



Last modified on Jul 14, 2020

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.