Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,359,916
Community Members
 
Community Events
168
Community Groups

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

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

2 answers

1 accepted

1 vote
Answer accepted
Syauqi Atlassian Team Sep 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!

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

Syauqi Atlassian Team Sep 18, 2018

No worries neeranjan 

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

3 votes
edwin Rising Star Sep 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

Does this feature exist on JIRA Cloud as well?

Like DamCoder likes this

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.

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

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?

This method is admin use only. 

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

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 Ignacio A likes this

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

Suggest an answer

Log in or Sign up to answer