How to identify and list when a field value was changed in Jira
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
Jira doesn't have a built-in feature to display when a field value was changed on Issues. This information is only visible in the Issue's History tab through the User Interface (UI).
Solution
Marketplace apps
There are "reporting" apps in the Marketplace that allow such reporting features on field change dates and allow for dynamic selection of fields and date ranges.
Database queries
Please note the SQL queries provided here are as example and not optimized for Production.
The below query can be used to identify and list the dates when the "Some custom field" changed:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
select
concat(p.pkey, concat('-', i.issuenum)) as "Issue Key",
author.lower_user_name as "Author username",
cg.created as "Change date",
ci.field,
ci.oldvalue,
ci.oldstring,
ci.newvalue,
ci.newstring
from changeitem ci
join changegroup cg on cg.id = ci.groupid
join jiraissue i on i.id = cg.issueid
join project p on p.id = i.project
left join app_user a_author on a_author.user_key = cg.author
left join cwd_user author on author.lower_user_name = a_author.lower_user_name
where
ci.field = 'Some custom field'
and (ci.newvalue like 'Some value' or ci.newstring like 'Some value') -- Optional "new value" restrictions
and p.pkey = 'JIRA' -- Optional query restrictions
order by cg.created desc;Sample output:
1
2
3
4
Issue Key | Author username | Change date | field | oldvalue | oldstring | newvalue | newstring
-----------+-----------------+----------------------------+-------------------+----------+-----------+----------+------------
JIRA-10 | admin | 2022-10-10 20:53:33.048+00 | Some custom field | | | | Some value
(1 row)Please note the changeitem.field value contains the custom field's name at the time of the change. Custom fields that have had changes in their name will require that both the current and previous field's names in the "ci.field" line above.
Another example to identify and list when the assignee changed :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
select
concat(p.pkey, concat('-', i.issuenum)) as "Issue Key",
author.lower_user_name as "Author username",
cg.created as "Change date",
ci.field,
ci.oldvalue,
ci.oldstring,
ci.newvalue,
ci.newstring
from changeitem ci
join changegroup cg on cg.id = ci.groupid
join jiraissue i on i.id = cg.issueid
join project p on p.id = i.project
left join app_user a_assignee on a_assignee.user_key like ci.newvalue and ci.field = 'assignee'
left join cwd_user assignee on assignee.lower_user_name = a_assignee.lower_user_name
left join app_user a_author on a_author.user_key = cg.author
left join cwd_user author on author.lower_user_name = a_author.lower_user_name
where
ci.field = 'assignee'
and assignee.lower_user_name = 'admin' -- Optional restriction to list only changes to a specific Username
and p.pkey = 'JIRA' -- Optional query restrictions
order by cg.created desc;Sample output:
1
2
3
4
Issue Key | Author username | Change date | field | oldvalue | oldstring | newvalue | newstring
-----------+-----------------+----------------------------+----------+----------+-----------+----------+-----------
JIRA-10 | admin | 2022-10-10 21:16:55.049+00 | assignee | | | admin | admin
(1 row)Was this helpful?