How to find unused projects with no recent updates

Still need help?

The Atlassian Community is here for you.

Ask the community


Platform notice: Server and Data Center only. This article only applies to Atlassian products on the server and data center platforms.

Please note the following information is provided as-is. Atlassian Support cannot provide further assistance with the SQL queries described below.


Summary

Using the example query on this page, we can find projects that have not been updated for a defined period of time. This can help you better identify projects to archive or clean up your Jira instance.

Environment

Jira Software

Jira Service Desk

Jira Core

Solution

The query below was tested with PostgreSQL, and may need to be adjusted for other database types. 

PostgreSQL - Projects without recent updates
select
ss.*
,case when ss."Last Update" < CURRENT_DATE - INTERVAL '12 months'
then 'NO'
else 'YES'
end as "Updated in the last 12 months?"
from (
select distinct
p.pkey as "Project Key"
,case when p.pkey = p.originalkey
then NULL
else p.originalkey
end as "Original Key"
,case when p.id = pe.entity_id
then 'YES'
else 'NO'
end as "Project Archived?"
,cu.lower_user_name as "Project Lead"
,cu.lower_email_address as "Lead Email"
,case when cu.active = 1
then 'YES'
else 'NO'
end as "Lead Active?"
,max(ji.updated) as "Last Update"
,count(distinct ji.id) as "Issue Count"
from jiraissue ji
join project p
on p.id = ji.project
join app_user au
on p.lead = au.user_key
join cwd_user cu
on au.lower_user_name = cu.lower_user_name
left join propertyentry pe
on p.id = pe.entity_id
and pe.property_key = 'jira.archiving.projects'
group by 1,2,3,4,5,6
)ss;

Customizing the time frame

< CURRENT_DATE - INTERVAL '12 months' then 'NO' else 'YES' end as "Updated in the last 12 months?"

You can adjust '12 months' in this portion of the query to suit your needs. For example, a 3 month time frame is used in the example below:

< CURRENT_DATE - INTERVAL '3 months' then 'NO' else 'YES' end as "Updated in the last 3 months?"

You may also filter for projects older than a set number of days, just add the following prior to the GROUP BY statement, update the <numberOfDays> with the desired value (e. g.  to list projects older than two months, just replace <numberOfDays> with 60):

and p.id in (SELECT ae.SECONDARY_RESOURCE_ID FROM AO_C77861_AUDIT_ENTITY ae WHERE [ACTION] = 'Project created'AND DATEDIFF(DAY, DATEADD(ms, ENTITY_TIMESTAMP / 86400000, (ENTITY_TIMESTAMP / 86400000) + 25567), GETDATE()) >= <numberOfDays>)

For Oracle you may use this - the interval 12 months was replaced with 365 (days).

Oracle - Projects without recent updates
select
ss.*
,case when ss."Last Update" < CURRENT_DATE - 365
then 'NO'
else 'YES'
end as "Updated in the last 12 months?"
from (
select distinct
p.pkey as "Project Key"
,case when p.pkey = p.originalkey
then NULL
else p.originalkey
end as "Original Key"
,case when p.id = pe.entity_id
then 'YES'
else 'NO'
end as "Project Archived?"
,cu.lower_user_name as "Project Lead"
,cu.lower_email_address as "Lead Email"
,case when cu.active = 1
then 'YES'
else 'NO'
end as "Lead Active?"
,max(ji.updated) as "Last Update"
,count(distinct ji.id) as "Issue Count"
from jiraissue ji
join project p
on p.id = ji.project
join app_user au
on p.lead = au.user_key
join cwd_user cu
on au.lower_user_name = cu.lower_user_name
left join propertyentry pe
on p.id = pe.entity_id
and pe.property_key = 'jira.archiving.projects'
GROUP  BY p.pkey,
CASE WHEN p.pkey = p.originalkey THEN NULL ELSE p.originalkey END,
CASE WHEN p.id = pe.entity_id THEN 'YES' ELSE 'NO' END,
cu.lower_user_name,
cu.lower_email_address,
CASE WHEN cu.active = 1 THEN 'YES' ELSE 'NO' END
)ss;

The query above will help to answers the following questions:

  • What is the Project Key?
    • If the Project Key was changed, what was the original Project Key?
    • Is the project archived?
  • Who is the Project Lead?
    • What is the Project Lead's email address?
    • Is the Project Lead Active?
  • When was the last update to an issue in this project?
  • How many issues are in this project?
  • Was the project updated within the last 12 months?

Example result:

Project KeyOriginal KeyProject Archived?Project LeadLead EmailLead Active?Last UpdateIssue CountUpdated in the last 12 months?
ABCARCNOjsmithjsmith@example.comYES2020-12-14 17:57:45.89200057YES


Additional information

Before venturing into manual queries, please, review the ⚙️ → Projects menu, available since Jira 7.11:

We’ve added two columns to the Projects page to help you determine the importance of your projects, so you can decide if they’re worth keeping. Issues and Last issue update will show you the number of issues in a project, and the date of the last update on an issue—just to give you an idea of what’s going on with the projects in your Jira.

This may help you address the problem via the UI without the need for direct queries.


Last modified on Jun 1, 2022

Was this helpful?

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