How to find out which Plans are still in use/required in a Jira instance
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
A plan is a roadmap of all the work that you manage in Jira. Work can be anything you're involved in — it can be the issues your teams are working on, the releases your teams have committed to delivering, and more. Currently, there are no Audit logs captured for who created the plan, or when a plan was last viewed. This article provides a workaround, that can at least provide some idea of when a Plan was created or a View was edited.
Solution
Workaround
The content on this page is provided "as-is" since Reporting falls out of Atlassian Support Offerings. Nonetheless, the query below was tested in PostgreSQL. If you are using a different database, you may need to work with a DBA to adapt it to your database environment.
Also note that, if the plan was created with Portfolio 2.5 (now known as Advanced Roadmaps) or older versions, the following query might not produce correct results, as in those older versions, there was no CREATED_TIMESTAMP in the AO_D9132D_PLAN table and the AO_D9132D_SAVED_VIEW2 table did not even exist.
You can run the following query to verify who created the plan :
1
2
3
4
5
SELECT au.lower_user_name, cu.display_name, ap."ID" as "Plan ID", ap."TITLE", TO_TIMESTAMP(ap."CREATED_TIMESTAMP") as "Created", asv."ID" as "View ID", asv."NAME" as "View Name", TO_TIMESTAMP(asv."LAST_MODIFIED_TIMESTAMP") as "View Modified"
FROM "app_user" as au RIGHT JOIN "AO_D9132D_PLAN" as ap ON au."user_key"=ap."CREATOR_ID"
RIGHT JOIN cwd_user as cu on au.lower_user_name=cu.lower_user_name
RIGHT JOIN "AO_D9132D_SAVED_VIEW2" as asv ON ap."ID"=asv."SUBJECT_ID"
order by "Plan ID", "Created";On running the query you will get results as given below. Here you can find the respective plan name and the user who has created the plan :
Was this helpful?