Extracting Jira Reports for Specific Values Added to an Issue Field from the Database
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
This article provides a comprehensive overview of how to retrieve information regarding the frequency of specific values added to a field within an issue. This information can be easily obtained from the issue history table in a database. It includes the necessary queries to effectively access and utilize this data for various internal auditing purpose.
Solution
This was written and tested using a PostgreSQL DB, so you may need to tweak it depending on the database you are using.
This query aims to gather detailed information about specific field values that have been modified or updated within an issue. Alongside these field values, it will also display the associated project name, Issue Key, the users who have added the values, and other relevant details.
1
2
3
4
5
6
7
8
9
SELECT p.pname, (p.pkey||'-'|| i.issuenum) AS issuekey , cg.ID, cg.issueid, au.lower_user_name, cg.AUTHOR, cg.CREATED, ci.FIELDTYPE, ci.FIELD, ci.OLDVALUE, ci.OLDSTRING, ci.NEWVALUE, ci.NEWSTRING , it.pname
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
inner join issuetype it on it.id=i.issuetype
WHERE ci.FIELD like '%<field Name>%' and ci.oldstring like '%<field value>%' or ci.newstring like '%<field value>%' ;
The query result outlines the specific values added to the Version field.
Was this helpful?