How to list all archived projects in SQL DB?

[Mindaugas Gelumbauskas]

We started archiving projects with this update

https://www.atlassian.com/blog/jira-software/project-archiving-jira-software-enterprise

but we cannot find how to get a list of these archived projects within the database. Is there any property keythat we can use?

[Andy Heinzer · Accepted solution]

Hi,

I understand you're using Jira Data Center and are looking to determine via SQL, which projects have been archived.  I was not sure of the answer here, so I'll walk you through the steps I took to learn more about this.   For other users that find this thread, Archiving projects is currently limited to Data Center platforms running Jira 7.10 or higher only.

First, I turned on the SQL logging in profiling and Logging.  Then I archived a project, and immediately disabled that logging (because it grows very fast).

In the logs (which you can find in the $JIRAHOME/log/atlassian-jira-sql.log file) I found no UPDATE or DELETE statements of any kind.  And I only found a single INSERT INTO command that was used when the project was archived:

2019-07-26 11:25:24,744 http-nio-8823-exec-8 admin2 685x726x1 1c20m6z /secure/project/ArchiveProject.jspa 1ms "INSERT INTO public.audit_log (ID, REMOTE_ADDRESS, CREATED, AUTHOR_KEY, SUMMARY, CATEGORY, OBJECT_TYPE, OBJECT_ID, OBJECT_NAME, OBJECT_PARENT_ID, OBJECT_PARENT_NAME, AUTHOR_TYPE, EVENT_SOURCE_NAME, DESCRIPTION, LONG_DESCRIPTION, SEARCH_FIELD) VALUES ('10802', '0:0:0:0:0:0:0:1', '2019-07-26 11:25:24.742', 'admin', 'Project archived', 'projects', 'PROJECT', '10004', 'BIZ', 'null', 'null', '1', '', 'null', '', 'admin2 admin 0:0:0:0:0:0:0:1 project archived projects biz')"

Which is just Jira adding an entry into the Audit Log that this project was archived by user admin2 at date/time, etc.   Aside from that single change to SQL, there does not appear to be any other flag or value that I could find on a database level that seems to identify which projects are currently archived.   I suppose you could try to query the Audit log via SQL to see this with something like:

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

Which should at least provide a list of all projects that have been archived AND restored along with date/times of those events.  I ordered this list by object_id in order to try to keep the archive/restore events together as they relate to a single project.

I hope this helps.

Andy

[CIbi_Cherian]

@Andy Heinzer, Is there an API yet to list this out including dates? Reading DB suggested to be a bad idea 

[Andy Heinzer]

@CIbi_Cherian You could use the REST API endpoint api/2/auditing in order to retrieve this same data.  You would probably need to filter the events to the category projects in order to limit the results you get back, however this could be something that endpoint can do.