Find inactive projects in JIRA

[Sivaz]

Hi,

Is there a way to determine how many projects in my instance have not been used in the past 6 months? I.e. no issues created or modified not even comments added. I tried to think of using JQL but couldnt figure a way out.

Admin changes to projects can be ignored.

TIA

[Matthew Cobby · Accepted solution]

select * from project where key not in (select distinct(pkey) from jiraissue where updated > "insert your date here")

If you have confluence as well, you can run the SQL report in there using bob Swift's SQL plugin.

[Matthew Cobby]

and a URL reference https://confluence.atlassian.com/display/JIRACOM/Example+SQL+queries+for+JIRA#ExampleSQLqueriesforJIRA-ReturnOutdatedProjectsFromaSpecificDate

[Sivaz]

Thank you very much. The following is the query I have used.

select pname from project where ID not in (select distinct(project) from jiraissue where updated > '2012-07-01 00:00:00');

[Jason Smith]

Neither of those queries worked for me. The first one didn't because pkey is deprecated, and the second one uses the "wrong" date format. This is what worked for me: select pname from project where ID not in (select distinct(project) from jiraissue where updated > '01-JAN-12');

[David Wu]

assume today is 20 May 2016 following sql list all projects with issue created/updated in past month:

select * from project where id in (select distinct(project)
from jiraissue where updated > to_date('20/04/2016','dd/mm/yyyy'))

[Renjith Pillai]

You can do a reverse stuff using JQL, "updatedDate >= -180d ORDER BY resolved DESC", save it as filter and create a Issue Statistics gadget with Project as the key. It will list me all the projects which are active in last 6 months.

Everything remaining is unused.

[Verena Ruff]

#projects without any updated issues within the last 6 month

select pname, lead,pkey from project where id NOT in (select distinct(project) from jiraissue where updated > date_sub(now(),interval 6 month)) 

[Rahul Aich [Nagra]]

Jira does not support this feature at the moment.

This is a feature request submitted in Nov 2012. here's the link to it. Feel free to go in and vote for this feature request.

https://jira.atlassian.com/browse/JRA-30664?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel#issue-tabs

Rahul

[Brad Apps]

btw. fixed query from @Matthew Cobby :

select pkey from project where ID not in (select distinct(PROJECT) from jiraissue where  updated > date_sub(now(), interval 31 day));

showing all project keys for all project that didn't have updated JIRA since 31 days

[Jose Zuniga]

I did it with the last date updated per project with SubFactory

select * from (

select project, updated,

max(updated) over (partition by project) as max_update
from jiraissue
) temp
where updated = max_update

[Joshua McManus]

Can you provide some more information about this Jose? I don't know where to make the search and can't find information about SubFactory

[Jose Zuniga]

Hello Joshua, did you tried to run this query on your DB?

SubFactory is a term for a temporary table created (in this case temp) and selecting those values from the temporary table