How to fetch the list of archived issues in Jira Data Center
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
As of Jira 8.1, we now support Issue Archival. When issues are archived they are no longer available to be searched via JQL. Although you can view archived issues in the GUI using their direct links, it might be helpful to quickly retrieve a list of issues directly from the database.
Solution
Reporting on Archived Issues from the Database:
Whenever an issue is archived the following entries are made on the jiraissue table:
1
2
3
archied | Y
archivedby | JIRAUSER10000
archiveddate | 2020-08-13 16:13:41.153+00This indicates who archived the issue and when it was archived. The 'archivedby' field utilizes the 'user_key' from the 'app_user' table. For more details, please refer to our schema documentation. This information enables us to formulate some basic queries:
All queries are in Postgresql
All issues archived:
1
2
3
4
5
6
select jp.pkey, ji.issuenum
from project jp, jiraissue ji
where jp.id = ji.project
and (ji.archived='Y'
or jp.id IN (select entity_id from propertyentry where entity_name = 'Project' AND property_key = 'jira.archiving.projects') ) -- in newer versions, such as 9.12, this OR clause is not needed
order by jp.pkey, ji.issuenum;All issues archived in active projects only:
1
2
3
4
5
6
7
select jp.pname, jp.pkey, ji.issuenum
from project jp, jiraissue ji
where jp.id = ji.project and ji.archived='Y' and ji.project IN
(select entity_id from propertyentry where entity_name = 'Project'
AND entity_id NOT IN (select entity_id from propertyentry
where entity_name = 'Project' AND property_key LIKE 'jira.archiving%'))
order by jp.pkey, ji.issuenum; All issues archived by a particular user:
1
2
3
4
5
6
select jp.pkey, ji.issuenum
from project jp, jiraissue ji
where jp.id = ji.project
and ji.archivedby=
(select user_key from app_user where lower_user_name='username')
order by jp.pkey, ji.issuenum;All archived issues that use a particular custom field with non-null data:
1
2
3
4
5
select p.pkey ,CONCAT(p.pkey,'-',ji.issuenum) as issue_key, cf.cfname, cf.id as custom_field_id, cf.description, cfv.stringvalue, cfv.numbervalue, cfv.textvalue, cfv.datevalue, cfv.valuetype, ji.archived from customfield as cf
join customfieldvalue as cfv on cf.id = cfv.customfield
join jiraissue as ji on cfv.issue = ji.id
join project p on p.id = ji.project
where ji.archived = 'Y'and cf.id = <custom_field_id> and (cfv.stringvalue != '' or cfv.numbervalue != null or cfv.textvalue != '') Note: Please make sure to replace the <custom_field_id> with the id of the custom field in the above SQL. For figuring out the custom_field_id, please refer: Find my custom field ID number in Jira.
All issues Restored from archive:
1
2
3
4
5
6
7
8
SELECT DISTINCT p.pname, p.pkey, (p.pkey || '-' || i.issuenum) AS issuekey, cg.issueid, au.lower_user_name, cg.AUTHOR, ci.FIELDTYPE, ci.FIELD, ci.OLDVALUE, ci.OLDSTRING, ci.NEWVALUE, ci.NEWSTRING
FROM changegroup cg
INNER JOIN jiraissue i ON cg.issueid = i.id
INNER JOIN project p ON i.project = p.id
INNER JOIN changeitem ci ON ci.groupid = cg.id
INNER JOIN app_user au ON cg.author = au.user_key
WHERE ci.FIELD = 'Restored'
GROUP BY p.pname, p.pkey, issuekey, cg.issueid, au.lower_user_name, cg.AUTHOR, ci.FIELDTYPE, ci.FIELD, ci.OLDVALUE, ci.OLDSTRING, ci.NEWVALUE, ci.NEWSTRING;Count of issues from all archived projects
1
2
3
SELECT p.pname AS "Archived Project name", count(*) "Issue Count" FROM project p, jiraissue i where i.project = p.id AND p.id IN (SELECT p.id FROM propertyentry pe
JOIN project p on pe.entity_id=p.id
WHERE pe.property_key = 'jira.archiving.projects')group by p.pname, i.project;Was this helpful?