nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Accepted answers

MatthewC

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

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

All comments

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

MatthewC

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

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

MatthewC

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');

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.

Jason Smith6

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');

Brad Apps

btw. fixed query from @Matthew Cobby :

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

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

Team FLYERALARM

<span class="sql1-reservedword">#projects without any updated issues within the last 6 month</span>

<span class="sql1-reservedword">select</span><span class="sql1-space">&nbsp;</span><span class="sql1-identifier">pname</span><span class="sql1-symbol">,</span><span class="sql1-space">&nbsp;</span><span class="sql1-identifier">lead</span><span class="sql1-symbol">,</span><span class="sql1-identifier">pkey</span><span class="sql1-space">&nbsp;</span><span class="sql1-reservedword">from</span><span class="sql1-space">&nbsp;</span><span class="sql1-tablename">project</span><span class="sql1-space">&nbsp;</span><span class="sql1-reservedword">where</span><span class="sql1-space">&nbsp;</span><span class="sql1-identifier">id</span><span class="sql1-space">&nbsp;</span><span class="sql1-reservedword">NOT</span><span class="sql1-space">&nbsp;</span><span class="sql1-function">in</span><span class="sql1-space">&nbsp;</span><span class="sql1-symbol">(</span><span class="sql1-reservedword">select</span><span class="sql1-space">&nbsp;</span><span class="sql1-reservedword">distinct</span><span class="sql1-symbol">(</span><span class="sql1-tablename">project</span><span class="sql1-symbol">) </span><span class="sql1-reservedword">from</span><span class="sql1-space">&nbsp;</span><span class="sql1-tablename">jiraissue</span><span class="sql1-space">&nbsp;</span><span class="sql1-reservedword">where</span><span class="sql1-space">&nbsp;</span><span class="sql1-identifier">updated</span><span class="sql1-space">&nbsp;</span><span class="sql1-symbol">></span><span class="sql1-space">&nbsp;</span><span class="sql1-function">date_sub</span><span class="sql1-symbol">(</span><span class="sql1-function">now</span><span class="sql1-symbol">(),</span><span class="sql1-function">interval</span><span class="sql1-space">&nbsp;</span><span class="sql1-number">6</span><span class="sql1-space">&nbsp;</span><span class="sql1-function">month</span><span class="sql1-symbol">)) </span>

Jose Zuniga_evuago

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_evuago

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

Katie Kennedy

Is there a way ot do this using postgres?

Thanks

Katie Kennedy

Is there a way to do this using postgres?

Jesse Wisener_h7f0wp

Great option, thanks.

Murat Sahan

Hi guys, I cant make this work, and especially not the select command. Can someone please show a working search just copied and pasted?

Thanks in advance

Great Sage

In the cloud version, you can view when the last modified issue in the 'view projects' screen.

View projects.PNG

Brandon Pierce_lp6iq9

Had originally posted I couldnt find the column but I was on the general Project table not the table in Settings.

For anyone who made the same mistake as me, go to the Gear/Settings Icon, select Projects and under Manage Projects you will see the table that includes the column in the screenshot above.

Thanks!

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

project

inactive