Select Test Case ID and Last Result Outcome on Database Azure DevOps by Iteration Path

Paulo Roberto Da Silva 1 Reputation point
2021-06-05T23:53:59.407+00:00

I need to get all test cases of an Azure DevOps iterationPath along with their results (passed, failed, etc).

I tried it in several ways, including doing inner join between 3 tables (from the azure API), but with no success as duplicate data comes.

Tables used: Testpoints, Workitems, Iterations

Query:

SELECT distinct w.WorkItemId, t.TestCaseId, t.LastResultOutcome, w.WorkItemType, i.IterationPath, i.IterationId, i.IterationName, i.IterationLevel1, i.IterationLevel2,i.IterationLevel3 as "Suite de Teste"
FROM vsts_iterations i
inner join vsts_workitems w On i.IterationSK = w.IterationSK
inner join vsts_testpoints t On w.ProjectSk = t.ProjectSk
where i.IterationSK like '%04b04041-e5ff-4ace-9ed5-1fdf55bf403d%'
and
w.WorkItemType = 'Test Case'
order by w.WorkItemId asc;

Expected outcome:

102628-workitem.png

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
41,941 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Anonymous
    2021-06-06T00:02:58.113+00:00

    Devops / TFS is not currently supported here on QnA. The product group for Azure DevOps / TFS actively monitors questions over at
    https://developercommunity.visualstudio.com/report?space=21&entry=problem
    https://developercommunity.visualstudio.com/report?space=22&entry=problem

    --please don't forget to upvote and Accept as answer if the reply is helpful--

    0 comments No comments

  2. de Carufel, Julie J [NC] 1 Reputation point
    2021-11-18T16:18:29.827+00:00

    We often have big releases that has many tests plans, we have querries that list all tests cases per release and the iteration path but, we would really like to have the Outcome in the column. We are unable to select the outcome, we can only do this info by test plan.... Is there a way we could add that choice in the query (Add the column outcome?). It would simplify the overall project status and respond to our client demands.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.