How to identify group usage in JIRA

Still need help?

The Atlassian Community is here for you.

Ask the community

 

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

Last modified on Jan 14, 2019

Was this helpful?

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