SQL to return for every project most recent updated date

[DI2E Licensing]

We are hosting > 100 projects in our JIRA instance. I'd like to know if all of these projects are active.

Current idea is to run a query that returns the most recent update date or create date for any issue, one for each project.  So it doesn't matter which issue within the project it is.  I just want to know when the last activity was.

Hope this makes sense. If you have a better way to gather this information would love to hear it.

If it makes a difference, we're running postgresql.

Thanks.

[Rodrigo Rosa · Accepted solution]

Hello team,

Please try the query from this previous answer:

https://answers.atlassian.com/questions/176459

I've adapted it to postgres:

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

[DI2E Licensing]

That is Brilliant!!! Thank you so much.

[DI2E Licensing]

I spoke too soon. :( the results indicated for project ABC that the last update was on 2012-10-16. But when I search on issues for that project, I see updates as recent as 8 days ago. I'll play around with it. I see where you are going here.

[DI2E Licensing]

Okay, changed MIN to MAX and it's a thing of beauty! SELECT DISTINCT i.PROJECT, MAX(i.UPDATED) as "Last Updated", p.pname FROM jiraissue i INNER JOIN project p ON p.ID = i.PROJECT GROUP BY i.PROJECT, p.pname ORDER BY MAX(i.UPDATED) ASC, i.PROJECT, p.pname;

[DI2E Licensing]

Question ... know what the query would be for confluence and stash??????

[hesham raafat]

Hi,

do you know how i can get it to list the project keys instead of the project names? or to list both the project keys and names?

[German Rodriguez Bolanos]

The Query is working nice! But has a little issue, for nulls values on the "Last issue update" are not listed.

[Cody Crandall]

DI2E SysAdmin was right max and min needs to be switched for this query to work correctly. Thanks all!