How to find unused Shared Teams on Advanced Roadmaps for Jira Data Center
Platform Notice: Data Center Only - This article only applies to Atlassian products 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
As an admin, you may want to cleanup Shared Teams (also known as Public teams) to reduce cluttering or in preparation for a migration.
This article aims to give you database queries to track how many issues and plans are associated to each Shared Team.
Environment
Any version of Jira Software Data Center or Server with Advanced Roadmaps (bundled or installed as an app).
Solution
For some context, Jira stored all Teams (including private teams) on AO_D9132D_PLANTEAM database table.
There's a column named Shareable which we can filter true only for the purpose of this solution.
Unlike other custom field which have their data stored on customfieldvalue table, Advanced Roadmaps and its exclusive fields are stored on entity_property where entity_id is the issue ID and the team value is on json_value column.
The last database table used is AO_D9132D_PLANTEAM which contains the association between plans and teams.
SQL queries below were tested in Postgres database. If you are using another database, update the syntax compatible to your database.
Shared Teams not associated to any issues
Remove the "having" condition if you'd like to get a count of issues associated to them.
1
2
3
4
5
SELECT t."ID",t."TITLE",count(ep.id) AS team_id
FROM "AO_82B313_TEAM" t
LEFT JOIN entity_property ep ON ep.property_key = 'jpo-issue-properties'
AND ep.json_value LIKE ('%"team_id":"' || t."ID" || '"%')
WHERE t."SHAREABLE" = true group by t."ID" having count(ep.id)=0;Shared Teams not associated to any plans
Remove the "having" condition if you'd like to get a count of plans associated to them.
1
2
3
4
SELECT t."ID",t."TITLE",count(pt."ID") AS team_id
FROM "AO_82B313_TEAM" t
LEFT JOIN "AO_D9132D_PLANTEAM" pt ON t."ID" = pt."TEAM_ID"
WHERE t."SHAREABLE" = true group by t."ID" having count(pt."ID")=0;Shared Teams not associated to any plans or issues
Here you can remove either/or the having conditions at the end if you'd like to filter by one condition only and get the counts for the other.
1
2
3
4
5
6
7
SELECT t."ID",t."TITLE",count(pt."ID") AS PlanAssociationCount,count(ep.id) AS IssueAssociationCount
FROM "AO_82B313_TEAM" t
LEFT JOIN entity_property ep ON ep.property_key = 'jpo-issue-properties'
AND ep.json_value LIKE ('%"team_id":"' || t."ID" || '"%')
LEFT JOIN "AO_D9132D_PLANTEAM" pt ON t."ID" = pt."TEAM_ID"
WHERE t."SHAREABLE" = true group by t."ID"
having count(ep.id)=0 and count(pt."ID")=0;Here's the documentation for reference around managing Teams in Advanced Roadmaps:
Was this helpful?