How to find unused projects with no recent updates
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
Please note the following information is provided as-is. Atlassian Support cannot provide further assistance with the SQL queries described below.
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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
1
< 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:
1
< 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):
1
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
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 Key | Original Key | Project Archived? | Project Lead | Lead Email | Lead Active? | Last Update | Issue Count | Updated in the last 12 months? |
|---|---|---|---|---|---|---|---|---|
ABC | ARC | NO | jsmith | YES | 2020-12-14 17:57:45.892000 | 57 | YES |
Additional information
Before venturing into manual queries, please, review the ⚙️ → Projects menu, available since Jira 7.11:
Quoting from Jira Software 7.11 Release Notes:
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.
Was this helpful?