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

Question

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 · Answer

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")

Contact Kintosoft · Answer

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.

Contact Kintosoft · Answer

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: * 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. * Join to the BOARDSPRINTISSUES table to get the issues of the sprints above (1) and filter by the TYPEID= to get the Tests in the sprints. * Join the ISSUELINKS table to get the Test Executions linked to the Tests (2). You have to filter by TYPE = '' as well as the DIRECTION ='' 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. * 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(") 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.