How can I list old projects in Jira?

[Leandro Nunes]

Is it possible to retrieve a list of projects without activity along the last e.g. 15 days?

[spuddy ಠ_ಠ · Accepted solution]

This is no direct way to do this in the JIRA UI, but you can get that information using a SQL query against the JIRA database.

select * from project where key not in (select distinct(pkey) from jiraissue where updated > "insert your date here")

[Aimee Swift]

If you are looking for a SQL that will pull through things that are X days/months old from the current date for archival candidates, this worked for me:

select project.pname, project.lead, project.pkey,MAX(UPDATED) as "Last Updated"
from jiraissue, project
where jiraissue.project = project.id
group by project.pname, project.lead, project.pkey
having max(jiraissue.updated) < Dateadd(Month, Datediff(Month, 0, DATEADD(m, -12, current_timestamp)), 0)
ORDER BY MAX(UPDATED) ASC

In this case, it shows projects that haven't been updated in 12 months or longer but you can just edit the time frame. Hope this helps :)

[Andrew Ardill]

To see a list of issues without activity in the last 15 you need to create a filter that specifies an 'Updated' date-range.

Select Issues->'Search for Issues' and expand the 'Dates and Times' section. Under the 'Updated' section write -15d in the 'From' box. Click the search button at the bottom, and you will be presented with a list of all issues that have not been updated in the last 15 days.

More time-range options can be found by clicking the date-range calendar icon next to the 'From' box.

[Andrei [errno]]

OP asked about JIRA projects though, not issues.

[Kishore Srinivasan]

select project.pname, project.lead
from jiraissue, project
where jiraissue.project = project.id
group by project.pname, project.lead
having max(jiraissue.updated) < 'respective date';