Return the status of a field on a ticket that is linked to a ticket in a sprint

Let's see if I can explain this clearly...

I'll start with the backstory, I am using the 3rd-party plugin XRay for our test management in JIRA. XRay has 4 issue types: Test, Test Sets, Preconditions, and Test Executions. We are using Agile boards to plan work using sprints, and the only issue type we have going into these sprints is Test Execution; the other 3 are reference types and are not task-oriented and therefore left out of the sprints. Tests are then linked to Test Executions using the custom linking within the XRay plugin so that an execution status can be assigned to it.

So having said all of that, here's what I need returned via a JQL filter. I need a way to lookup the field TestRunStatus on Test issue types for those that were altered by Test Executions within the current sprint.

Thank you for your help

3 answers

Basically, you need to display the TestRunStatus of corresponding Test issue in the Test Executions itself. Is my understanding correct? If not, can you please explain with an example? I can understand the setup but little bit confused on the requirement

It looks like pretty easy to do with SQL for JIRA Driver:

Please open the SQL console:

JIRA > Issues menu > Search for issues (opens Issue Navigator) > Export menu > Convert to SQL (opens the SQL console)

it allows you to perform SQL queries and explore results as well as the database model (tables, columns, indexes) in a visual way.


  1. Use the BOARDSPRINTS table to get the sprints of a given boardid (You might want to use the BOARDS table to get the board id) and filter by the column STATUS = 'active' for current sprints only.
  2. Join to the BOARDSPRINTISSUES table to get the issues of the sprints above (1) and filter by the TYPEID=<Test issue type id> to get the Tests in the sprints.
  3. Join the ISSUELINKS table to get the Test Executions linked to the Tests (2). You have to filter by TYPE = '<XRAY custom link type id>' as well as the DIRECTION ='<inward|outward>' as issues are always linked in a bidirectional way on JIRA ("block" and "is blocked by", "duplicates" and "is duplicated by",..) and avoid duplicated results.
  4. Join the ISSUECHANGES table to get the history of changes of the Tests issues (3) and filter by FIELD = 'TestRunStatus' and change's CREATED date> the sprint's START date (1) to get the Tests with TestRunStatus changed after the current sprint started

Finally, use the SQL for JIRA built-in sql JQL function to convert back the SQL for JIRA query above into a new JQL:

issue in sql("<your JQL for JIRA query">)

and save it as filter. Then you might want to use it as Agile quick filter to filter your backlog or whatever any other use case.


hope this helps.



if you want to filter for the issue TYPEID (2) then a join is missed: ISSUES table between BOARDSPRINTISSUES and ISSUELINKS.

However, if the XRay's custom link type is used only to link Tests and TestExecutions then you could ignore the join above because filtering by link type would also filter by issue type. Otherwise you would need the extra join.

I think I have figured this one out.

After looking over the JQL page of the XRay wiki (, I found the 'testExecutionTests' command which returns tests linked to a specific Test Execution ticket or a filter. I already have a filter that brings back executions that were assigned to the current sprint, so I used that as the filter and I think it will work:

project = PROJECT AND issuetype = Test AND issue in testExecutionTests("Test Executions In Current Sprint Filter")

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Oct 09, 2018 in Jira Core

How to manage many similar workflows?

I have multiple projects that use variations of the same base workflow. The variations depend on the requirements of the project or issue type. The variations mostly come in the form of new statuses ...

222 views 6 0
Join discussion

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