How to find which projects are inactive in last 6 months? Like to know all possible ways to find.

niranjan September 17, 2018

I want to find out the projects which are inactive in my instance from last 6 months.

5 answers

1 accepted

2 votes
Answer accepted
Syauqi
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
September 17, 2018

Hi Neeranjan, 

There have been several similar post which you can refer to and here's one of them: Need a query to find out active and inactive Jira project over the last 6 months.

You can use the following queries:

1. SELECT count(id) FROM project WHERE ID NOT IN (SELECT DISTINCT(project) FROM jiraissue WHERE updated > DATE_SUB(NOW(), INTERVAL 6 MONTH) and created > DATE_SUB(NOW(), INTERVAL 6 MONTH));

2. SELECT count(id) FROM project WHERE ID NOT IN (SELECT DISTINCT(project) FROM jiraissue WHERE updated > DATE_SUB(NOW(), INTERVAL 6 MONTH) or created > DATE_SUB(NOW(), INTERVAL 6 MONTH));

Hope this helps!

niranjan September 18, 2018

I will try to with those queries. Thank you @Syauqi

Syauqi
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
September 18, 2018

No worries neeranjan 

Diego Cañete June 10, 2021

SELECT count(id) FROM project WHERE ID NOT IN (SELECT DISTINCT(project) FROM jiraissue WHERE updated > DATE_SUB(NOW(), INTERVAL 6 MONTH) or created > DATE_SUB(NOW(), INTERVAL 6 MONTH))))

Like Omar Mohamed Fathi likes this
4 votes
Edwin Kyalangalilwa
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 17, 2018

Hi @niranjan,

Upgrade to Jira v7.11.1 and above. They have added a feature that lets you see more project information in the admin section.

Jira Software 7.11.x release notes

projects_columns2.png

niranjan September 18, 2018
Philippe BONNARDEL
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
May 20, 2019

Does this feature exist on JIRA Cloud as well?

Like # people like this
Julian Governale
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 26, 2019

Not that i have been able to locate.  You would think they would make this available as we have limited querying options where as the server version can always query the DB.

Greg Williams November 6, 2020

On Jira Cloud you can see activity by doing the following:

  • Go to the Gear icon and choose "Projects"
  • Choose "Manage Projects" to the left
  • The column "Last issue update" is to the far right, sort descending 

Project dormancy.png

Like # people like this
Guillermo Ponce Puente
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
February 18, 2022

Thanks! @Greg Williams ! Is there any way  for including that same field in the project list obtained when "View All Projects" option is selected? Or this is just visible for admins?

Greg Williams May 5, 2022

This method is admin use only. 

I tried to see if there was a way to use JQL but that returns tickets, not projects. 

Diego Cañete July 11, 2022

try using avance filters:

from data base:
  1. This SQL query gives last updated issue in the Project with project key and project lead
2.  SELECT DISTINCT p.pkey,p.LEAD,MAX(i.UPDATED) as "Last Updated"3.  FROM jiraissue i4.  INNER JOIN project p5.  ON p.ID = i.PROJECT6.  GROUP BY p.pkey,p.LEADORDER BY MAX(i.UPDATED) ASC
Like # people like this
Ignacio A August 4, 2022

Thanks @Diego Cañete 

For everybody else: I couldn't make Diegos's query  work on my postgres version, so I did the following modifications and it works like a charm:

SELECT DISTINCT p.pkey,p.LEAD,MAX(i.UPDATED) as "Last Updated" FROM jiraissue i INNER JOIN project p ON p.ID = i.PROJECT GROUP BY p.pkey,p.LEAD ORDER BY MAX(i.UPDATED) ASC;

 

I hope this helps

Like Alberto Aragón likes this
Tushar Gohel April 26, 2023

Does Atlassian provide the same information via API as well? instead of going to the DB?

 

Thanks,

Tushar

Sandip Bhosale April 24, 2024

We're currently on Jira server Version 7.1.2, but unfortunately, there's no 'Manage Projects' option accessible for Jira Admin. Please advise if there's a solution available. Additionally, I need to check the latest issue update.project manage.JPG

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 24, 2024

Hi Sandip,

Server/DC does not really have a "mange projects" like Cloud does.  The screenshot you show is probably the closest you will get on older versions of Server/DC.

Your version is 4-8 years out of date, not supported in any way, and may have some security issues that you really need to fix.  

I strongly recommend that you upgrade it to Jira Data Center 9.12 (the latest long-term-support version) as soon as you can.  Not having "manage projects" is a trivial problem compared with the age of your system.

0 votes
William Wilson January 4, 2024

So, this is great... but... I have a situation where I am trying to develop a Confluence page for our project leads to be able to go to, see when projects were last active, and respond to some questions.

How do I get this data, into a confluence page? 

It's absolutely asinine that I can't get this information into Confluence. Just ANOTHER Atlassian FAIL.

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 4, 2024

Why do you think you need to dig up obsolete data?

(or "necropost" as well)

William Wilson April 30, 2024

Because.... the question remains. And I don't have an answer?

Why do you have to be so condescending and difficult?

0 votes
mete March 9, 2023

the query above could be incomplete in case you have existing project without issue:

please find the corriged one :

SELECT p.pkey, p.LEAD, MAX(i.UPDATED) as "Last Updated"
FROM jiraissue i
FULL JOIN project p ON p.ID = i.PROJECT
GROUP BY p.pkey , p.LEAD ORDER BY MAX(i.UPDATED) DESC

0 votes
Omar Mohamed Fathi
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
January 2, 2023

thank you all it helped alot

Suggest an answer

Log in or Sign up to answer