Example SQL queries for JIRA

jira user's full name and email:

select ps_name.propertyvalue        fullname
, ps_email.propertyvalue        email
from propertystring ps_name
, propertystring ps_email
, propertyentry pe_name
, propertyentry pe_email
, userbase ub
where ub.id = pe_name.entity_id
and ub.id = pe_email.entity_id
and pe_name.id = ps_name.id
and pe_email.id = ps_email.id
and pe_name.entity_name = 'OSUser'
and pe_name.property_key = 'fullName'
and pe_email.entity_name = 'OSUser'
and pe_email.property_key = 'email'

This page would also be a good place to describe how to enable logging of all sql queries against a Jira database. That way, I can just perform some action from the browser and deduce what happened underneath it all. For confluence, the steps appear to be:

1. 1. log hibernate prepared statements/SQL queries (equivalent to setting 'hibernate.show_sql' to 'true')
      #log4j.logger.net.sf.hibernate.SQL=DEBUG, confluencelog
      #log4j.additivity.net.sf.hibernate.SQL=false

1. 1. log hibernate prepared statement parameter values
      #log4j.logger.net.sf.hibernate.type=DEBUG, confluencelog
      #log4j.additivity.net.sf.hibernate.type=false
      

For mysql, you can always add log=/var/lib/mysql/foo.log to the mysqld section in /etc/my.cnf, but it would still be more useful to coordinate this output with other log messages from Jira.

A user has contributed the following regarding CSV imports and the Fix For Version field:

Greetings - I had an issue in which I had imported a bunch of issues via CSV, but their "fix version" was not set. The following are my notes during the fix process, which describe the process by which issues, imported into Jira with no "fix version" set, can be set to a reasonable value that works for your system. I am posting this here in hopes that it might help someone, because it was one step I had to take to get all these wonderful charting reports working for me. There are probably many better ways to accomplish what I did below. All errors are mine, and all comments and suggestions are of course welcome.

The reader should also note:

• As a service company that bills customers monthly, my company eSolia's "versions" for support issues are not software versions but rather billing periods, such as "January 2007" or "December 2006".
• I assumed that the issues' "updated" date will suffice as a seed value to set the fix version, knowing that there may still be a few problems with this if the issues have somehow been updated after their import.
• Three db tables are relevant: jiraissue, projectversion, nodeassociation. Both the "affected version" and "fix version" are in nodeassociation.
• The commands below assume postgresql is the db.
• The 'fixver' field in the jiraissue table seems to not be used.
• Any time data is manipulated via SQL for a system like Jira which caches its data, the best practice is to stop the application server (tomcat), do the changes via sql, then restart tomcat and reindex manually from within Jira.

First, all issues with no fix version must be found. This sql queries against jira issue, checking for id's that do not show up in the nodeassociation table with the "IssueFixVersion" flag.

select id,pkey,project,created,updated,to_char(updated,'FMMonth YYYY') as updatedversion,summary from jiraissue where id not in (select SOURCE_NODE_ID from nodeassociation where ASSOCIATION_TYPE='IssueFixVersion') AND issuestatus=6;

Notes:

• The where clause uses a subquery, and negates it with "not in". nodeassociation.source_node_id is where the issue's id is stored. nodeassociation.association_type is the flag, which will be 'IssueFixVersion' if the id has a fix version.
• to_char is a function that converts timestamps to strings. Here, we extract the month fully-spelled out in proper case, i.e. January, not Jan. The trick to getting a reasonable version name is the to_char function, applied to the updated field, and using the "FM" fillmode modifier on the full month name "Month" to be finally FMMonth, which strips spaces (without it, the month has an extra space which makes the version string unequal to what is in the database already).
• The phrase "as updatedversion" is used to give the to_char column a name.
• Closed issues have a status equal to 6.

Take a closer look at the to_char functionality. Select from jiraissue, and limit the id's to a reasonable range for readability:

select id,created,updated,to_char(updated,'FMMonth YYYY') as updatedversion,fixfor from jiraissue where id > 11420;

This SQL returns this:

id   |          created           |          updated           | updatedversion | fixfor 
-------+----------------------------+----------------------------+----------------+--------
 11421 | 2006-06-04 11:00:00-04     | 2006-06-06 11:00:00-04     | June 2006      |       
 11422 | 2006-06-05 11:00:00-04     | 2006-06-06 11:00:00-04     | June 2006      |       
 11423 | 2006-06-11 11:00:00-04     | 2006-06-12 11:00:00-04     | June 2006      |       
 11424 | 2006-06-11 11:00:00-04     | 2006-06-12 11:00:00-04     | June 2006      |       
 11425 | 2006-06-15 11:00:00-04     | 2006-06-29 11:00:00-04     | June 2006      |       
 11426 | 2006-06-29 11:00:00-04     | 2006-06-29 11:00:00-04     | June 2006      |       
 11427 | 2006-06-29 11:00:00-04     | 2006-07-02 11:00:00-04     | July 2006      |       
 11428 | 2006-06-30 11:00:00-04     | 2006-07-03 11:00:00-04     | July 2006      |       
 11441 | 2006-12-30 08:51:25.16-05  | 2006-12-30 08:51:25.16-05  | December 2006  |       
 11442 | 2006-12-31 02:13:25.109-05 | 2006-12-31 07:12:07.217-05 | December 2006  |       
 11450 | 2006-12-31 08:18:50.365-05 | 2006-12-31 08:19:41.671-05 | December 2006  |       
 11451 | 2006-12-31 17:49:53.107-05 | 2006-12-31 17:49:53.107-05 | December 2006  |       
 11429 | 2006-09-28 11:00:00-04     | 2007-01-01 18:53:58.889-05 | January 2007   |       
 11430 | 2006-10-09 11:00:00-04     | 2007-01-01 19:20:58.82-05  | January 2007   |

The calculated values "June 2006", "December 2006" will serve as the version, later when we write to the nodeassociation table.

Now, make it easy for ourselves by creating a view:

create view closedissueswithnofixver as 
select id,pkey,project,created,updated,to_char(updated,'FMMonth YYYY') as updatedversion,summary from jiraissue where id not in (select SOURCE_NODE_ID from nodeassociation where ASSOCIATION_TYPE='IssueFixVersion') AND issuestatus=6;

You can query against the view as though it were a table:

select * from closedissueswithnofixver;

Looking ahead, next, we need to somehow link this version text to the vname in the projectversions table, and grab the projectversion table id (assuming all the versions have been already entered), and use this as the "fix version". Then, we can write the issue id, and fix version id into the nodeassociation table in this sort of format:

12345, Issue, 54321, Version, IssueFixVersion

... assuming 12345 is the issue id and 54321 is the version id.

I created a view of a query, to prep the data to go into the nodeassociation table. However, there are a number of tricks to note. First, take a look at the SQL:

create or replace view prepfornodeassocinsert as 
select closedissueswithnofixver.id as source_node_id, text ('Issue') as source_node_entity,projectversion.id as sink_node_id,text ('Version') as sink_node_entity,text ('IssueFixVersion') as association_type from closedissueswithnofixver left join projectversion on closedissueswithnofixver.project=projectversion.project AND closedissueswithnofixver.updatedversion=projectversion.vname;

Note:

• "create or replace view" means to either create the view anew, or replace what is there. However, a replacement occurs only when the columns are exactly the same.
• Here we are creating a view based on the other closedissueswithnofixver view created previously.
• We need to specify text so that the data is ultimately created with the right data type. This is the "text ('Issue')" part.
• Use the "as" clauses to specify the right column names.
• Use a left join to join the view to the projectversion table, and link the project id's and the project text names.
• To get the text "Issue", "Version" and "IssueFixVersion" in the table, the data has to have dummy text in some columns. This can be faked in the query by using "('TextToInsert')".

This SQL returns:

source_node_id | source_node_entity | sink_node_id  |  sink_node_entity  |  association_type
-----------------+--------------------+---------------+--------------------+-------------------
  12345          |  Issue             |  23456        |   Version          |  IssueFixVersion
  22345          |  Issue             |  34567        |   Version          |  IssueFixVersion
  32345          |  Issue             |  56789        |   Version          |  IssueFixVersion
...

Finally, to do the insert into nodeassociation, Jira (tomcat) should be shut down first, and then the prepfornodeassocinsert view can be used as the datasource for the insert.

You can confirm the fields in nodeassociation by running a "\d nodeassociation" in the psql interactive query program on the command line.

jira37=# \d nodeassociation
             Table "public.nodeassociation"
       Column       |         Type          | Modifiers 
--------------------+-----------------------+-----------
 source_node_id     | numeric(18,0)         | not null
 source_node_entity | character varying(60) | not null
 sink_node_id       | numeric(18,0)         | not null
 sink_node_entity   | character varying(60) | not null
 association_type   | character varying(60) | not null
 sequence           | numeric(9,0)          | 
Indexes:
    "pk_nodeassociation" PRIMARY KEY, btree (source_node_id, source_node_entity, sink_node_id, sink_node_entity, association_type)
    "node_sink" btree (sink_node_id, sink_node_entity)
    "node_source" btree (source_node_id, source_node_entity)

Then, because there is no constraint on the sequence field, a sql insert query can be run just on the fields in the prepfornodeassocinsert view, specifying which fields to add to.

insert into nodeassociation  (source_node_id, source_node_entity, sink_node_id, sink_node_entity, association_type ) select * from prepfornodeassocinsert;

Finally, take these steps to get started again:

#start tomcat
#reindex manually

The fix versions for all your issues should now be set up, allowing you to run various reports.

Regards,
Rick

I recently had to purge a few projects of private issues & comments, and came up with the following.

Find issues that are private to "company" (forget which table this lives in):

SELECT *
FROM jiraissue
WHERE security > 0
 AND pkey LIKE 'KEYSTEM%';

Find issues within a few projects which contain private comments:

SELECT distinct jiraissue.pkey
FROM jiraaction, jiraissue
WHERE actiontype = 'comment'
 and actionlevel = 'company'
 and jiraaction.issueid = jiraissue.ID
 and jiraissue.pkey like 'KEYSTEM%'
 order by pkey

Project Category to Project mapping:

select tpr.pkey as 'Project Key', tpr.pname as 'Project name', tpc.cname as 'Project Category', isnull (tpc.description,'None') as 'Project Category Description'
from nodeassociation tna, project tpr, projectcategory tpc
where tna.sink_node_entity='ProjectCategory' and tna.source_node_id=tpr.id
and tna.sink_node_id=tpc.id order by tpr.pkey

Source:

http://forums.atlassian.com/thread.jspa?messageID=257239191&#257239191

How do I get the text representation of the priority of an issue?

I'm using Enterprise Jira and MySQL.

Hello,

How to get list of all Issue Type for project ?

I'm using  version: 3.8.1 | edition: Enterprise. Thank you!

Members of a group:

SELECT USER_NAME FROM membershipbase WHERE GROUP_NAME = <group_name>

What's the most efficient way of pulling a count of bugs moved into a resolved state within a certain time frame?

JIRA Enterprise / PostgreSQL

Thanks.

Creates a customized view of a project that can be used when the jiraissues macro does not provide the right columns. You will need your project id to replace 12345 below. If you need to run multiple different queries, it helps to create a view in your database first to make the page sql easier. Here is an example that shows priority, votes, plan, summary, and description:

create view project12345 as 
select  
    '[' || pkey || '|' || pkey || '@jira]' as "Issue", 
    priority.pname as "Priority",
    votes as "Votes", 
    (select vname from projectversion  
        where id in (select sink_node_id from nodeassociation  
                         where association_type='IssueFixVersion' 
                           and source_node_id = jiraissue.id 
                     ) 
    ) as "Plan", 
    '[~' || reporter || ']' as "Reporter", 
    summary as "Summary", 
    '{panel:borderStyle=none}' || jiraissue.description || '{panel}' as "Description"  
    
    from jiraissue, priority 
    where project=12345 and jiraissue.priority = priority.id 
    order by priority.sequence;

{sql:datasource=jiraDS|output=wiki|sortIcon=true}
select * from project12345
{sql}

This works for PostgreSQL. The concat function || may need to be changed for other databases.

Hi,

How is it possible to track workflow changes (open to assign for example)?

 Thanks

Textual representation of an issue´s issue type:

select jiraissue.pkey, issuetype.pname from jiraissue, issuetype where jiraissue.issuetype=issuetype.id
   and project=10001

There's an error in the second paragraph "Find Fixed For versions for an issue". Here is the corrected version:

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>';

There's a couple of queries here: http://blogs.onresolve.com/?p=50

Average Issue Age by Priority over time, and Issue Count by Priority over time...

jamie

Enterprise Jira 3.12.2, MySQL 4.1

Each project's notification scheme: 

Each project's issue type screen scheme: 

Each project's workflow scheme, or NULL if default: 

Each project's issue type scheme, or NULL if default: