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

Direct database queries are not recommended in JIRA. Always back up your data before performing any modification to the database.

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;

Return All Project Issues

SELECT p.id AS project_id, p.pname AS project_name, p.lead AS project_lead,
ji.pkey AS issue_id, ji.reporter AS issue_reporter, pri.pname AS issue_priority, ist.pname AS issue_status, ji.summary AS issue_summary
FROM project p LEFT OUTER JOIN jiraissue ji ON ji.project = p.id
LEFT OUTER JOIN priority pri ON ji.priority = pri.id
LEFT OUTER JOIN issuestatus ist ON ji.issuestatus = ist.id
WHERE p.pname = '<project_name>'
ORDER BY ji.pkey;

Return Project Versions

SELECT p.id AS project_id, p.pname AS project_name, p.lead AS project_lead,
pv.vname AS version_name, pv.description AS version_desc, pv.sequence AS version_seq, pv.released AS version_released,
pv.archived AS version_archived, pv.url AS version_url, pv.releasedate AS version_release_date
FROM project p LEFT OUTER JOIN projectversion pv ON pv.project = p.id
WHERE p.pname = '<project_name>'
ORDER BY pv.sequence;

Return Project Components

SELECT p.id AS project_id, p.pname AS project_name, p.lead AS project_lead,
c.cname AS component_name, c.url As component_url, c.lead AS component_lead, c.assigneetype AS component_assignee_type
FROM project p LEFT OUTER JOIN component c ON c.project = p.id
WHERE p.pname = '<project_name>';

Return Project Roles

SELECT p.id AS project_id, p.pname AS project_name, p.lead AS project_lead, prc.roletypeparameter AS project_roles
FROM project p LEFT OUTER JOIN projectroleactor prc ON prc.pid = p.id
WHERE p.pname = '<project_name>';

Return Project Workflow

SELECT p.id AS project_id, p.pname AS project_name, p.lead AS project_lead, ws.name AS project_associated_workflow_scheme,
wse.workflow AS workflow_scheme_associated_workflow, jw.descriptor AS workflow_descriptor
FROM project p LEFT OUTER JOIN nodeassociation na ON na.source_node_id = p.id AND na.sink_node_entity = 'WorkflowScheme'
LEFT OUTER JOIN workflowscheme ws ON ws.id = na.sink_node_id
LEFT OUTER JOIN workflowschemeentity wse ON wse.scheme = ws.id
LEFT OUTER JOIN jiraworkflows jw ON jw.workflowname = wse.workflow
WHERE p.pname = '<project_name>';

Return project issue type screen scheme, screen scheme of the issue type screen scheme, screen name of the screen scheme, tab name, and tab fields

SELECT p.id AS project_id, p.pname AS project_name, p.lead AS project_lead,
itss.name AS project_issue_type_screen_scheme, fss.name AS screen_scheme_of_the_issue_type_screen_scheme,
fs.name AS screen_name_of_the_screen_scheme, fst.name AS screen_tab_name,
fsli.fieldidentifier AS tab_field, fsli.sequence AS tab_field_seq
FROM project p LEFT OUTER JOIN nodeassociation na ON na.source_node_id = p.id AND na.sink_node_entity = 'IssueTypeScreenScheme'
LEFT OUTER JOIN issuetypescreenscheme itss ON itss.id = na.sink_node_id
LEFT OUTER JOIN issuetypescreenschemeentity itsse ON itsse.scheme = itss.id
LEFT OUTER JOIN fieldscreenscheme fss ON itsse.fieldscreenscheme = fss.id
LEFT OUTER JOIN fieldscreenschemeitem fssi ON fss.id = fssi.fieldscreenscheme
LEFT OUTER JOIN fieldscreen fs ON fssi.fieldscreen = fs.id
LEFT OUTER JOIN fieldscreentab fst ON fs.id = fst.fieldscreen
LEFT OUTER JOIN fieldscreenlayoutitem fsli ON fst.id = fsli.fieldscreentab
WHERE p.pname = '<project_name>'
ORDER BY fsli.sequence;