nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Accepted answers

Bhushan Nagaraj2

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

All comments

Bhushan Nagaraj2

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 Nagaraj2

Hi Rumceisz,

Please accept and vote up if you found the answer helpful

Cheers

Bhushan

Raymond Petters2

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

Jonathan Salisbury_2jwpf6

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

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 N_ B_

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

Corey_Tabb

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

jira-administration