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

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
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published yesterday in Jira Service Desk

Wy are we still using email for Service Desk workflows?

...attest to the experience of an urgent approval that gets lost in the boss’s inbox and requires that special “Please Approve” email or text message. In an age where we have distributed teams...

86 views 0 2
Read article

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