This page last changed on Feb 19, 2009 by amyers.
This is a page that lists example SQL queries that some JIRA users might find useful, mainly for reporting purposes.
Fixed by and Cascading Field Value
If you have a JIRA installation that uses a Cascading Select List custom field to track the version/build that the issue has been fixed in, and would like to find issues that were fixed by a particular user in a particular version/build, you can use the SQL query below. Please note that the SQL does not filter out reopened issues, but returns issues that were resolved at least once. Due to this, duplicates are also possible in the generated result set.
SELECT jiraissue.*
FROM jiraissue,
OS_HISTORYSTEP,
customfieldvalue,
customfieldoption
WHERE OS_HISTORYSTEP.ENTRY_ID = jiraissue.id
AND OS_HISTORYSTEP.ACTION_ID = <action_id>
AND OS_HISTORYSTEP.CALLER = <user_name>
AND customfieldvalue.issue = jiraissue.id
AND customfieldvalue.PARENTKEY = <parent_key>
AND customfieldvalue.stringvalue = customfieldoption.id
AND customfieldoption.customvalue like '<cf_value>';
Where
- <user_name> - the username of the desired user
- <action_id> - the id of your transition into the fixed state (may need multiple)
- <parent_key> - the id of the Level 1 option in customfieldoption - E.g. 10040
- <cf_value> - the Level 2 value of the cascading field. E.g 'realease%'
Find Fixed For versions for an issue
If you want to find out the Versions an Issue has been marked "Fix For" you can run the following query
SELECT projectversion.id, vname
FROM projectversion,
nodeassociation,
jiraissue
WHERE ASSOCIATION_TYPE = 'IssueFixVersion'
AND SINK_NODE_ID = projectversion.id
AND SOURCE_NODE_ID = jiraissue.id
AND pkey = '<issue_key>';
Where
- <issue_key> - the key of an issue. E.g. TEST-10
Find all issues changed by a user after a certain date
If you want to find out all the issues that a particular user has changed use the following query
SELECT DISTINCT(j.id) FROM jiraissue j, changegroup g
WHERE j.id = g.issueid
AND g.author = '<user name>'
AND g.created > '<date>';
Where
- <date> - the earliest desired date (The date should be in the format 'yyyy-mm-dd hh:mm:s'. E.g '2005-10-06 14:40:28')
- <username> - the name of the desired user
Find Statuses of all issues in a project on a given date
You can use this SQL to retreive the status of all issues on a give date in a give project: Note. This was tested under MySQL
SELECT JI.pkey, STEP.STEP_ID
FROM (SELECT STEP_ID, ENTRY_ID
FROM OS_CURRENTSTEP
WHERE OS_CURRENTSTEP.START_DATE < '<your date>'
UNION SELECT STEP_ID, ENTRY_ID
FROM OS_HISTORYSTEP
WHERE OS_HISTORYSTEP.START_DATE < '<your date>'
AND OS_HISTORYSTEP.FINISH_DATE > '<your date>' ) As STEP,
(SELECT changeitem.OLDVALUE AS VAL, changegroup.ISSUEID AS ISSID
FROM changegroup, changeitem
WHERE changeitem.FIELD = 'Workflow'
AND changeitem.GROUPID = changegroup.ID
UNION SELECT jiraissue.WORKFLOW_ID AS VAL, jiraissue.id as ISSID
FROM jiraissue) As VALID,
jiraissue as JI
WHERE STEP.ENTRY_ID = VALID.VAL
AND VALID.ISSID = JI.id
AND JI.project = <proj_id>;
Where
- <your date> is the date you want to check
- <proj_id> is the project you want to check
Find Status counts for a Project on a given date
Or you can find out the counts on specific date: Note. This was tested under MySQL
SELECT count(*), STEP.STEP_ID
FROM (SELECT STEP_ID, ENTRY_ID
FROM OS_CURRENTSTEP
WHERE OS_CURRENTSTEP.START_DATE < '<your date>'
UNION SELECT STEP_ID, ENTRY_ID
FROM OS_HISTORYSTEP
WHERE OS_HISTORYSTEP.START_DATE < '<your date>'
AND OS_HISTORYSTEP.FINISH_DATE > '<your date>' ) As STEP,
(SELECT changeitem.OLDVALUE AS VAL, changegroup.ISSUEID AS ISSID
FROM changegroup, changeitem
WHERE changeitem.FIELD = 'Workflow'
AND changeitem.GROUPID = changegroup.ID
UNION SELECT jiraissue.WORKFLOW_ID AS VAL, jiraissue.id as ISSID
FROM jiraissue) As VALID,
jiraissue as JI
WHERE STEP.ENTRY_ID = VALID.VAL
AND VALID.ISSID = JI.id
AND JI.project = <proj_id>
Group By STEP.STEP_ID;
Where
- <your date> is the date you want to check
- <proj_id> is the project you want to check
Find how Many Issue Moved into States for a given Period
Use this SQL to find out how many issues were Created, Resolved, ..., Closed during a given period. Note that if an issue moves through more than 1 transition, it will be counted more than once. Note. This was tested under MySQL
SELECT NEWSTRING AS Status, count(*) AS Number
FROM changeitem, changegroup, jiraissue
WHERE changeitem.field = 'Status'
AND changeitem.groupid = changegroup.id
AND changegroup.issueid = jiraissue.id
AND jiraissue.project = <project_id>
AND changegroup.CREATED >= '<date_from>'
AND changegroup.CREATED < '<date_to>'
Group By NEWSTRING
UNION
SELECT 'Created' As Status, count(*) AS Number
FROM jiraissue
WHERE jiraissue.CREATED >= '<date_from>'
AND jiraissue.CREATED < '<date_to>'
AND jiraissue.project = <project_id>;
Where
- <date_from> is the date you want to check from
- <date_to> is the date you want to check to
- <project_id> is the project you want to check
Get Components for an Issue
Get all the Components for an Issue
SELECT jiraissue.pkey, component.cname
FROM nodeassociation, component, jiraissue
WHERE component.ID = nodeassociation.SINK_NODE_ID
AND jiraissue.id = nodeassociation.SOURCE_NODE_ID
AND nodeassociation.ASSOCIATION_TYPE = 'IssueComponent'
AND pkey = '<issue_key>';
Find date that Closed issues were closed
Find out the date an issue was Closed for all currnetly closed issues.
SELECT pKey, OS_CURRENTSTEP.STATUS, OS_CURRENTSTEP.START_DATE
FROM jiraissue, OS_CURRENTSTEP
WHERE issuestatus = 6 AND OS_CURRENTSTEP.ENTRY_ID = jiraissue.WORKFLOW_ID;
Simple join - jiraissue and jiraaction
SELECT *
FROM jiraissue LEFT JOIN jiraaction ON jiraissue.id = jiraaction.issueid;
Simple join - jiraissue and changegroup
SELECT *
FROM jiraissue LEFT JOIN changegroup ON jiraissue.id = changegroup.issueid;
Simple join - Changegroup and changeitem
SELECT *
FROM changegroup LEFT JOIN changeitem ON changegroup.id = changeitem.groupid;
Simple join - jiraissue and os_currentstep
SELECT *
FROM jiraissue LEFT JOIN OS_CURRENTSTEP ON jiraissue.WORKFLOW_ID = OS_CURRENTSTEP.ENTRY_ID;
Simple join - jiraissue and os_historystep
SELECT *
FROM jiraissue LEFT JOIN OS_HISTORYSTEP ON jiraissue.WORKFLOW_ID = OS_HISTORYSTEP.ENTRY_ID;
|