How to run an SQL query to get data from JIRA Project ?

Hi !

We need to do a query using a Join between two diferents Issuetypes linked then I should use the SQL query on JIRA.

I have generated the database scheme using the JIRA Diagram Scheme Generator plugin. But, I don't undestand which step should I execute to be able to run an SQL query and where can I run it ?

Please, I'll apreciate your help.

Thanks in advance.

2 answers

Comments Closed
0 vote

Generally, running SQL against a JIRA database is the worst way to do any form of reporting.  It is not designed to be reported on.

Of course, the application has no way to get to the database either, as you shouldn't be doing it.

And, never, never, never, write to a JIRA database.

To run your SQL, you will need a database tool to connect to the database.

But I'd strongly recommend that you not bother, as it's very likely that you won't know enough about the database structure to actually get what you want from it.

Could you explain what question you're trying to answer?  Not "I think I want to use SQL", but what are you trying to find out about your JIRA system?

Hi Nic,
Thanks for your answer.
I need to do an SQL query to get all the Stories with the Sprint greather than the parent Epics and the Task with their Sprint greather than the parent Stories in a JIRA project.
I found some choices but , based on Atlassian answers, it is not possible , and they have recommended me to use the Database Scheme.
Please, could you guide me to do it ?
Thanks in advance.

I'm sorry, I can't work out what that means.

The main problem I have is understanding what "sprint greather than" mean?

For example you say: Stories with the Sprint greather than the parent Epics 

Does that mean

  • Stories whose estimates add up to a number larger than the estimate on their parent Epic
  • Stories whose planned sprint is planned to be done after the sprint the parent Epic is in
  • Something else

There's further problems with all of the interpretations I can make, but I know I do not understand the question

Iam using a groovy script in a transition postfunction to ask the database for the person that did a specific transition in the issue workflow. For that the might exist an api function too but writing the query and executing it was for me the fastest way.

import com.atlassian.jira.ComponentManager
import com.atlassian.jira.component.ComponentAccessor
import groovy.sql.Sql
import java.sql.Connection
import org.ofbiz.core.entity.ConnectionFactory
import org.ofbiz.core.entity.DelegatorInterface
import com.atlassian.jira.issue.Issue
import com.atlassian.jira.issue.MutableIssue;
import com.atlassian.jira.issue.ModifiedValue
import com.atlassian.jira.issue.util.DefaultIssueChangeHolder
import com.atlassian.jira.user.util.UserManager
import com.atlassian.jira.util.ImportUtils
//import com.atlassian.crowd.embedded.api.User



//Issue issue = issue
//def id = issue.getId()
ComponentManager componentManager = ComponentManager.getInstance()
def delegator = (DelegatorInterface) componentManager.getComponentInstanceOfType(DelegatorInterface.class)
String helperName = delegator.getGroupHelperName("default");


def sqlStmt = """
                SELECT a.author as 'doer' FROM changegroup as a JOIN changeitem as b ON b.groupid = a.id
                WHERE b.field = 'status' AND a.issueid = ${issue.id} AND b.oldstring = 'In Progress' AND b.newstring = 'Review'
                ORDER BY a.created DESC LIMIT 1
"""

Connection conn = ConnectionFactory.getConnection(helperName)
Sql sql = new Sql(conn)
try {
        StringBuffer sb = new StringBuffer()
        sql.eachRow(sqlStmt)
    {
        sb << it.doer
    }
    def userManager = (UserManager) ComponentAccessor.getUserManager()
    def user = userManager.getUserByName(sb.toString())
issue.setAssignee(user)


} finally {
sql.close()
}

To execute groovy scripts in transition postfunction you need the ScriptRunner plugin.
Hope you will get an idea how to do what you want to do.

That method may not give you the right answer if a change was recent.

mmh, so there is a delay between transitioning the issue and writing the change to database?

Isnt a problem for us cause there are a lot of review and stage testing status that take several days before that script is executed.

Do you have experience when things like changes, new issues etc are writing to database?

You're probably alright then as you're giving it plenty of time.

People reporting via SQL and getting the wrong answer was the first thing that made me start looking into JIRA database usage, and very quickly lead me to "the database is always the worst option".  There are times when it's the only option, but it's always the worst.

How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published May 21, 2018 in Jira Software

How large do you think Jira Software can grow?

Hi Atlassian Community! My name is Shana, and I’m on the Jira Software team. One of the many reasons this Community exists is to connect you to others on similar product journeys or with comparabl...

1,189 views 10 18
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you