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~!
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
Based on:
/* 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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.