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

2 votes
Accepted answer

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

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

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
Published Jan 08, 2019 in Jira

How to Jira for designers

I’m a designer on the Jira team. For a long time, I’ve fielded questions from other designers about how they should be using Jira Software with their design team. I’ve also heard feedback from other ...

1,099 views 4 9
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