How to list and delete unused Plans in Jira Advanced Roadmaps
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
Summary
This article describes one possible way to list potentially unused Plans in Jira.
This is particularly useful if Admins are looking for cleanup opportunities or are facing JPOSERVER-8124, which prevents the Programs and Plans to be adequately listed with SQLServer if they amount to over 2,100 in number.
Environment
- Jira Software Data Center 8.X
- Jira Software Data Center 9.X
- Or Server with Advanced Roadmaps installed
Solution
When a user first opens a Plan, Jira records an entry in the AO_D9132D_PLAN_USER_PROPERTY table. It doesn't record when the access was made nor how many times, so it's only a measure of "How many different users have opened this Plan".
Listing potentially unused Plans
select
p."ID" as "Plan Id",
p."TITLE" as "Plan name",
pc.lower_user_name as "Plan creator",
count(ua.id) as "Unique active users access"
from "AO_D9132D_PLAN" p
left join app_user pc on pc.user_key = p."CREATOR_ID"
left join "AO_D9132D_PLAN_USER_PROPERTY" pup on pup."PLAN_ID" = p."ID" and pup."KEY" = 'viewSwitching-activeView'
left join app_user au on au.user_key = pup."USER_KEY"
left join cwd_user u on u.lower_user_name = au.lower_user_name and u.active = 1
left join cwd_directory d on d.id = u.directory_id and d.active = 1
left join cwd_user_attributes ua on ua.user_id = u.id and ua.attribute_name = 'login.lastLoginMillis' and cast(ua.attribute_value as bigint) >= 1672531200000
group by 1, 2, 3
order by 4 desc, 1 asc;You may need to change the query a bit to work for your specific database, like changing the bigint cast (line 10) or removing the double quotes on tables and column names.
This query joins users who are currently active and with a recent lastLogin (1672531200000 translates to Jan 1 2023 (epochconverter.com)). To disregard the lastLogin, change line 4 to count(u.id) instead.
We're interested in the Plans showing up at the end of the list:
Plan Id | Plan name | Plan creator | Unique active users access
---------+-----------------------------------+--------------+----------------------------
1390 | ***** Data Plans | ***** | 34
5922 | Unified Account Management ***** | ***** | 30
514 | ***** Roadmap | ***** | 25
600 | Portal ***** | ***** | 25
1786 | ***** Roadmap | ***** | 25
4459 | ***** Program Roadmap | ***** | 23
6052 | Cloud Migration Roadmap | ***** | 20
...
516 | ***** 4.0 Planning | ***** | 1
522 | ***** Test 1 | ***** | 1
525 | Test 1.1 | ***** | 1
530 | ***** - Roadmap | ***** | 1
536 | ***** plan | ***** | 1
543 | ***** Plan | ***** | 1
544 | ***** QA | ***** | 1
549 | *****-TP | ***** | 1
550 | ***** Test for ***** | ***** | 1
...
385 | *****_Sanbox_Plan | ***** | 0
388 | ***** project portfolio | ***** | 0
392 | *****-Plan-Test | ***** | 0
397 | ATL ***** | ***** | 0
400 | ***** Usage | ***** | 0
401 | ***** Data Producs | ***** | 0
403 | ***** Base | ***** | 0
404 | ***** v1 Plan | ***** | 0
411 | ***** Plan v1.4 | ***** | 0The Plans with very low numbers of "Unique active users access" are good candidates for exclusion as they're likely not being used.
The "Plan Creator" column may be useful to double check with the user if the Plans are good to be gone.
Deleting Plans
Deleting Plans can be done through the UI (user interface) by the Admin or by the respective Plan creators or through a REST API endpoint to make it simpler to bulk delete them:
DELETE https://JIRA-BASE-URL/rest/jpo/1.0/plans/PLAN-IDReplace the JIRA-BASE-URL and PLAN-ID respectively and run this through either the command line or some REST API client tool, like Postman:
For the command line, a Jira Admin can generate a PAT (Using Personal Access Tokens) and repeat the request for each PLAN_ID:
curl -H "Authorization: Bearer PAT-TOKEN-HERE" -X DELETE https://JIRA-BASE-URL/rest/jpo/1.0/plans/PLAN-IDAgain replacing JIRA-BASE-URL and PLAN-ID respectively.