nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Andrea Queralt

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.

Accepted answers

All comments

Nic Brough -Adaptavist-

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?

Andrea Queralt

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.

Stefan Arnold

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.

Nic Brough -Adaptavist-

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

Nic Brough -Adaptavist-

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

Stefan Arnold

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?

Nic Brough -Adaptavist-

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.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

sql

jira

jira-software

mysql