What is the right SQL query against the JIRA database to see the field edit history?

[Nathan Given]

Hi,

I'm trying to use SQL queries recreate the information that shows up in the "ALL" tab on the JIRA issue details view and I'm having a hard time figuring out the SQL for when a field changes.

Has anyone found this information in the JIRA database?  (6.3.8)

Thanks~!

[Volodymyr Krupach · Accepted solution]

Hi Nathan!

Here are samples with history tables: https://developer.atlassian.com/jiradev/jira-architecture/database-schema/database-change-history

Here you can find DB schema: https://developer.atlassian.com/jiradev/jira-architecture/database-schema Please use JIRA61_db_schema.pdf. It should match schema for JIRA 6.3.

https://developer.atlassian.com/jiradev/jira-architecture/database-schema

[Markus Pöhler]

Based on:

https://confluence.atlassian.com/jirakb/to-find-or-view-a-jira-issue-from-the-database-using-sql-744524576.html

/* Replace the Sample issuenum with your num! */

SELECT p.pname, p.pkey, i.issuenum, cg.ID, cg.issueid, au.lower_user_name, cg.AUTHOR, cg.CREATED, ci.FIELDTYPE, ci.FIELD, ci.OLDVALUE, ci.OLDSTRING, ci.NEWVALUE, ci.NEWSTRING
,DBMS_LOB.substr( ci.OLDSTRING, 3000) as cccf
,DBMS_LOB.substr( ci.NEWSTRING, 3000) as cccd
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 AND ci.FIELDTYPE='jira' AND ci.FIELD='status'
inner join app_user au on cg.author = au.user_key
WHERE cg.issueid=(select id from jiraissue where issuenum = 70892 and project in (select id from project)) 
order by cg.CREATED,1,3,4;

Please note this sample is filtering on ci.FIELD='STATUS' changes, you can just remove this or replace it with anything you like.