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; } } }

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

niranjan_jqtadt

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

Accepted answers

Syauqi

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!

All comments

Syauqi

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!

Edwin Kyalangalilwa

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_jqtadt

Thank you @Edwin Kyalangalilwa

niranjan_jqtadt

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

Syauqi

No worries neeranjan

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.

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

Omar Mohamed Fathi

thank you all it helped alot

mete.c

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

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)

William Wilson

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

Why do you have to be so condescending and difficult?

Welcome!

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

Quick Links

Find more posts tagged with

server

jira

jira-service-desk

jira-cloud

jira-server

jira-service-desk-server