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.
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
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
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
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
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
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
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 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;
SELECT * FROM jiraissue LEFT JOIN jiraaction ON jiraissue.id = jiraaction.issueid;
SELECT * FROM jiraissue LEFT JOIN changegroup ON jiraissue.id = changegroup.issueid;
SELECT * FROM changegroup LEFT JOIN changeitem ON changegroup.id = changeitem.groupid;
SELECT * FROM jiraissue LEFT JOIN OS_CURRENTSTEP ON jiraissue.WORKFLOW_ID = OS_CURRENTSTEP.ENTRY_ID;
SELECT * FROM jiraissue LEFT JOIN OS_HISTORYSTEP ON jiraissue.WORKFLOW_ID = OS_HISTORYSTEP.ENTRY_ID;
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;
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;
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>';
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>';
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>';
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;