How to report on 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. While you can still view the issues that have been archived in the GUI by the direct link of the issue. It may be of use to quickly get a list of issues from the database directly.
Reporting on Archived Issues from the Database:
Whenever an issue is archived the following entries are made on the jiraissue table:
archived | Y
archivedby | JIRAUSER10000
archiveddate | 2020-08-13 16:13:41.153+00This shows us if the issue is archived by whom and when. The archivedby field uses the user_key from the app_user table, see our schema documentation for more info. This allows us to create some basic queries:
All queries are in Postgresql
All issues archived:
select jp.pkey, ji.issuenum
from project jp, jiraissue ji
where jp.id = ji.project
and ji.archived='Y'
order by jp.pkey, ji.issuenum;All issues archived in active projects only:
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:
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:
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: How to find any custom field's IDs.