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

[niranjan]

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

[Syauqi · Accepted solution]

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]

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

[Syauqi]

No worries neeranjan 

[Diego Cañete]

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

[edwin]

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

[niranjan]

Thank you @edwin

[Philippe BONNARDEL]

Does this feature exist on JIRA Cloud as well?

[Julian Governale]

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]

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 

[Guillermo Ponce Puente]

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]

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]

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

[Ignacio A]

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

[Tushar Gohel]

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

Thanks,

Tushar

[Sandip Bhosale]

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.

[Nic Brough -Adaptavist-]

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.

[William Wilson]

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-]

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

(or "necropost" as well)

[mete]

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

[Omar Mohamed Fathi]

thank you all it helped alot