Sort projects by last activity - how to?

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

2 answers

1 accepted

Accepted Answer
2 votes

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

Hi Bhushan,

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

Rumi

Hi Rumceisz,

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

Cheers

Bhushan

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

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Tuesday in Jira

Looking for anyone who made the switch to Data Center

The Jira Marketing team is putting together an ebook on migrating to Data Center. We're looking for pro tips on how you staffed your project team and organized your Proof of Concept. Share yo...

61 views 0 2
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you