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!
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
I can't help thinking it should be max(i.UPDATED) for the last time an issue was updated?
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
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!
Unfortunately there are no AUG chapters near you at the moment.Start an AUG