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.

[Gregory S 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! @Gregory S 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?

[Gregory S 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

[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