Sort projects by last activity - how to?

[Rumceisz]

Dear All,

we have a typical problem which involves all the admins of very big Jira instances.

We have more than 1600 projects in our instance and we use to find projects which are inactive for more than a year. The problem is that the users, project leads don't inform us when they finish working in a project or even don't start using a Jira project.

Is there a script or something which we could sort all the projects by the last update with? It would be very helpful for us!

Many thanks for your hints!

Best Regards,
Rumi

[Bhushan Nagaraj · Accepted solution]

Hi Rumceisz,

The SQL query below lists all the projects in order or the last issue update that occured.

Maybe this can be of some help?

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

Cheers

Bhushan

[Rumceisz]

Hi Bhushan,

thank you very much! IT's a big help for us!

Rumi

[Bhushan Nagaraj]

Hi Rumceisz,

Please accept and vote up if you found the answer helpful :)

Cheers

Bhushan

[Jonathan Salisbury]

I can't help thinking it should be max(i.UPDATED) for the last time an issue was updated?

MySQL: 

SELECT DISTINCT i.PROJECT, MAX(i.UPDATED) as 'Last Updated', p.pname
FROM jiraapp2.jiraissue i
INNER JOIN jiraapp2.project p
ON p.ID = i.PROJECT
GROUP BY i.PROJECT, p.pname
ORDER BY MAX(i.UPDATED) desc, i.PROJECT, p.pname

[Raymond Pettersen]

Hi.

We had some of the same challenges and developed a plugin you might want to take a closer look at:

https://marketplace.atlassian.com/plugins/com.tauron.project-activity

[Corey_Tabb]

thanks for wasting my time your link goes no where really appreciate you wasting my time thanks i look forward to more of my time being wasted by you 

[Sloan]

Hi Corey

As the post is from 2013 it can happen that Links get outdated. It seems like the plugin Raymond was referring to moved vendor or it's name. No need to panic. :)

Check if this might help you https://marketplace.atlassian.com/apps/1210777/latest-activity-report-for-jira?hosting=server&tab=overview

Cheers
Niklas