Find archived projects in SQL DB?

[Liam Averion]

Hello,

I'm currently trying to find all inactive projects that have not updated/created an issue within a year. I've written an SQL for this already, but I'm having trouble filtering out the archived projects from this list.

I've found one approach from here:

https://community.atlassian.com/t5/Jira-questions/How-to-list-all-archived-projects-in-SQL-DB/qaq-p/113919

But, my team and I have SQL logger turned off, so this will not catch all the projects that we have archived already. I feel like there is a way that the projects are tagged as archived in the database because there's a list of it in the UI. I just can't seem to find which database this tag would reside in. 

Any help is appreciated.

Thank you! 

[DPKJ · Accepted solution]

@Liam Averionyou are on right track, but you don't need to turn SQL Logging on to execute query provided in answer you suggested, i.e.

select * from audit_log where category='projects' and summary in('Project archived') order by object_id, created desc

This will work even if you have SQL Logging turned off.

What SQL Logging does is log all query in log files that Jira execute on database.

[Liam Averion]

Oh wow, my mistake then. Thanks so much!

[Hack Vogel]

Hi @Liam Averion 

In our JIRA instance we archive projects by assigning a permission scheme in which only JIRA administrators have any access. 

The following SQL Server code will return Project, Last Issue Created and Last Issue Updated information for all projects archived in our JIRA instance. I hope this helps:

Use <<your JIRA database name>>
Go

SELECT prj.id, prj.pkey as ProjectKey
, psch.NAME as PermissionScheme
, (SELECT issnumcr.issuenum FROM [jiraissue] issnumcr where issnumcr.CREATED = (SELECT MAX(iss.CREATED) FROM [jiraissue] iss where iss.PROJECT = prj.ID)) as issnum_cr
, (SELECT MAX(iss.CREATED) as last_create_date FROM [jiraissue] iss where iss.PROJECT = prj.ID) as LastIssueCreated_date
, (SELECT issnumup.issuenum FROM [jiraissue] issnumup where issnumup.UPDATED = (SELECT MAX(iss.UPDATED) FROM [jiraissue] iss where iss.PROJECT = prj.ID)) as issnum_up
, (SELECT MAX(iss.UPDATED) as last_update_date FROM [jiraissue] iss where iss.PROJECT = prj.ID) as LastIssueUpdated_date
FROM [project] prj
join [nodeassociation] nas on nas.SOURCE_NODE_ENTITY = 'Project' AND nas.SINK_NODE_ENTITY = 'PermissionScheme' AND prj.ID = nas.SOURCE_NODE_ID
join [permissionscheme] psch on psch.id = nas.SINK_NODE_ID and psch.NAME = 'your archive permission scheme name'
order by prj.pname

[Michael Kuhl {Appfire}]

Hi @Liam Averion To find all Archived project you can go to the projects screen and filter on archived.

If by archived projects you mean projects that have no issues created or updated in the past year then use this JQL:

created <= -365d or updated <= -365d

and then export it to a pie chart (Export > Dashboard charts) using project as the statistic to see the set of projects that qualify.

[Liam Averion]

I already know of this list. I'm looking for a way to do this through SQL from the database not JQL in the web UI.