How to find the project that is not used for last 2 years.

[Jongho Jung]

I want to clean up the projects in Jira so I need a project list those are not used for last 2~3 years.

Is there any way including DB query to find unused project?

Regards, JJ

[Jongho Jung]

I found the answer from the JIRA Knowledge Base.

https://confluence.atlassian.com/jirakb/how-to-find-unused-projects-with-no-recent-updates-1063161883.html

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.

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

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;