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

Skip to end of metadata
Go to start of metadata
Icon

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.

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

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

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

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

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

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

Find date that Closed issues were closed

Find out the date an issue was Closed for all currnetly closed issues.

Simple join - jiraissue and jiraaction

Simple join - jiraissue and changegroup

Simple join - Changegroup and changeitem

Simple join - jiraissue and os_currentstep

Simple join - jiraissue and os_historystep

Return All Project Issues

Return Project Versions

Return Project Components

Return Project Roles

Return Project Workflow

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