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

[Dan Maxwell]

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

[Dan Maxwell]

I think I have figured this one out.

After looking over the JQL page of the XRay wiki (http://confluence.xpand-addons.com/display/XRAY/JQL+Queries), 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")

[Pablo Beltran]

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.

Then:

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.

[Pablo Beltran]

comment:

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.

[Mahesh S]

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