Documentation for JIRA 4.0. Documentation for other versions of JIRA is available too.

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;



Log a request with our support team.



Raise an issue for our developers.



See answers from the community.



Blog and update.

Except where otherwise noted, content in this space is licensed under a Creative Commons Attribution 2.5 Australia License.