Test case steps and test action results

Question

Where is the connection between test case steps and test action results in the tfs_defaultcollection db?

I can use this query to get the test steps:

declare @fldIdSteps int = (Select top 1 fldid from Fields where ReferenceName = 'Microsoft.VSTS.TCM.Steps')
select *
from WorkItemLongTexts
where FldID = @fldIdSteps

And use this query to get Action results:

select *
from tbl_TestActionResult

Now, I know how to find the connection between a testrunid and a test case id, but I have not been able to find a way to connect the step ids (embedded in xml in WorkItemLongTexts) to the actionpath in tbl_TestActionResult.

MTM2010 can show this info (view test result for test case, check under Test Step Details) so there must be a way to connect them. I need to know how so I can finish a test report (ssrs) for my client. 

---

Jesper Fernström
QWise Software engineering – refactored!

Answer 1

Best guess so far was to simply rely on that the order of the test step elements would match up with the order of the results in tbl_TestActionResults. Surprisingly it seems to work most of the time, but (not so surprisingly) it does not work ALL of the time. And I need a solution that works ALL the time.

To get an idea what I mean, open up MTM2010, run a test case and save the result. Write down testcaseid and testrunid (found under Test->Analyze test runs) and then modify and run this in Management Studio:

declare @testcaseid int = 1262 -- replace with testcaseid from your own tfs
declare @tesrunid int = 1261 -- replace with testrunid from your own tfs

declare @fldIdSteps int = (Select top 1 fldid from Fields where ReferenceName = 'Microsoft.VSTS.TCM.Steps')
-- drop temp tables if they exist
IF OBJECT_ID('tempdb..#tmpstepstableXML','u') IS NOT NULL
BEGIN
drop table #tmpstepstableXML
END
IF OBJECT_ID('tempdb..#tmpstepstable','u') IS NOT NULL
BEGIN
drop table #tmpstepstable
END
IF OBJECT_ID('tempdb..#tmpstepstable2','u') IS NOT NULL
BEGIN
drop table #tmpstepstable2
END
IF OBJECT_ID('tempdb..#tmpstepstestResult','u') IS NOT NULL
BEGIN
drop table #tmpstepstestResult
END

-- Create temp table containg test case id and steps in XML format. Important that words column is converted to xml type or xml operations in next statement wont work.
select wilt.ID as id, wilt.Rev, CAST(wilt.Words as xml) as stepsXML
into #tmpstepstableXML
from WorkItemLongTexts wilt
where wilt.FldID = @fldIdSteps
and wilt.id = @testcaseid
and rev = (select MAX(rev) from WorkItemLongTexts where fldid = wilt.fldid and id = wilt.id)

-- Shred the xml and create a table. nr column is vital for the creation of the next temp table.
create table #tmpstepstable (nr int identity, testCaseId int, rev int, stepId int, stepType varchar(15), step varchar(300), result varchar(300)) 
insert into #tmpstepstable (testCaseId, rev, stepid, stepType, step, result)
select id, rev, p.value('@id', 'int') as stepid, p.value('@type', 'VARCHAR(15)') as stepType, p.value('./parameterizedString[1]', 'VARCHAR(300)') as step, p.value('./parameterizedString[2]', 'VARCHAR(300)') as result
from #tmpstepstableXML cross apply stepsXML.nodes('/steps/step') t(p)

-- This table is a work around for the problem of xpath function .position() does not work with SQL server. We need the sequence number of each step in order to match the
-- result of each step to the actual step.
select nr + 1 - (select MIN(nr) from #tmpstepstable tst where tst.testCaseId = #tmpstepstable.testCaseId and tst.rev = #tmpstepstable.rev) as stepSequence, testCaseId, rev, stepId, stepType, step, result
into #tmpstepstable2
from #tmpstepstable

create table #tmpstepstestResult (nr int identity, testRunId int, testResultId int, iterationId int, actionPath varchar(512), outcome int, errorMessage nvarchar(512))
insert into #tmpstepstestResult (testRunId, testResultId, iterationId, actionPath, outcome, errorMessage)
select testRunId, testResultId, iterationId, actionPath, outcome, errorMessage
from tbl_TestActionResult
where ActionPath <> ''
and testrunid = @tesrunid
order by TestRunId

select * 
from #tmpstepstable2 steps
left join #tmpstepstestResult results on steps.stepsequence = results.nr

 

---

Jesper Fernström
QWise Software engineering – refactored!

Answer 2

I've also looked into the MTM API and i think the ITestActionResult Interface and TestActionResultCollection Class could be used, but so far I've been unable to find anything on how to get an instance of either for a specific testcase/testrun combo...

---

Jesper Fernström
QWise Software engineering – refactored!

Answer 3

After some trial and error I've found that the code below can give me the action results if I have access to a test run id and a test result id (which I have from a SQL query). However, in my SSRS report I would then have to make quite a lot of connections to tfs/tms (one call per test case or even one call per action result!) It would be much smoother if I could just get the info myself straight from SQL.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.TeamFoundation.Client;
using Microsoft.TeamFoundation.TestManagement.Client;
using Microsoft.TeamFoundation.WorkItemTracking.Client;

namespace MTM2010ApiExploration
{
 class Program
 {
  static void Main(string[] args)
  {
   string serverurl = "http://tfs:8080/tfs/defaultcollection";
   string project = "Template Test";
   int testrunid = 102; //Replace this with a valid id from your own tfs 
   int testresultid = 100000; //Replace this with a valid id from your own tfs

   TfsTeamProjectCollection tfs = new TfsTeamProjectCollection(new Uri(serverurl));
   ITestManagementService tms = tfs.GetService<ITestManagementService>();
   ITestManagementTeamProject proj = tms.GetTeamProject(project);

   ITestCaseResult tcr = proj.TestResults.Find(testrunid, testresultid);
   TestActionResultCollection tarc = tcr.Iterations[1].Actions;
   Console.WriteLine("hello");
  }
 }
}

---

Jesper Fernström
QWise Software engineering – refactored!

Answer 4

Microsoft helped me out via email :) I'll share with you all what I've learned.

ActionPath is a hierarchical hexadecimal representations of test step ids.

The column ActionPath will typically contain an empty string, 8 chars string or 16 chars string. For a specific test result in a test run there will be one line with the empty string (this line is the over all test result) and then one line for each step, containing 8 or 16 chars. The first 8 chars is the step id and the next 8 chars (if they exist) is a shared step id.

So, still have to shred the XML for test steps and rely on the order of <step> elements for the step sequence, but apart from that it is easy enough to do the connection between specific steps and the step results (as long as you know how to split strings and convert from hex to decimal in SQL).

 

---

Jesper Fernström
QWise Software engineering – refactored!

Answer 5

I am trying to do just what you needed to do.  Care to post your finished SQL?

I tried to post a comment to your post about this on your blog, but IE can't load it and Firefox says it uses bad compression.

Answer 6

I'm having problems with my blog and no time to fix it :( Anyway, I'll post the SQL here. Keep in mind that this query is designed to run against the DefaultCollection db, not the warehouse. As such it can impact TFS performance...

Using a test suite id as input this query will give you the testcases contained in the suite and subsuites. For each testcase you get the steps and shared steps with the latest test results (if any).

declare @ParamTestSuite int = 11 -- SELECT * FROM tbl_Suite -- to find an id to use as input parameter...

declare @fldIdSteps int = (Select top 1 fldid from Fields where ReferenceName = 'Microsoft.VSTS.TCM.Steps')
declare @sharedStepLinkType int = (Select top 1 ReverseID from LinkTypes where ReferenceName = 'Microsoft.VSTS.TestCase.SharedStepReferencedBy')

-- drop temp tables if they exist
IF OBJECT_ID('tempdb..#tempTestStepsXML','u') IS NOT NULL
BEGIN
drop table #tempTestStepsXML
END
IF OBJECT_ID('tempdb..#tempStepsTable','u') IS NOT NULL
BEGIN
drop table #tempStepsTable
END
IF OBJECT_ID('tempdb..#tempStepsTable2','u') IS NOT NULL
BEGIN
drop table #tempStepsTable2
END
IF OBJECT_ID('tempdb..#tempStepsTestResult','u') IS NOT NULL
BEGIN
drop table #tempStepsTestResult
END

-- Create temp table containg test case id and steps in XML format. Important that words column is converted to xml type or xml operations in next statement wont work.
create table #tempTestStepsXML (TestCaseId int not null, Rev int not null, StepsXML xml, Primary key (TestCaseId, Rev))
;WITH SuiteCTE2
 AS
 (
 SELECT SuiteId, ParentSuiteId
  FROM tbl_Suite
  WHERE SuiteId = @ParamTestSuite
  UNION ALL
  SELECT S.SuiteId, S.ParentSuiteId
  FROM tbl_Suite AS S
  JOIN SuiteCTE2 AS C
   ON S.ParentSuiteId = C.SuiteId
  )
  insert into #tempTestStepsXML (TestCaseId, Rev, StepsXML)
  select distinct wilt.ID as id, wilt.Rev, CAST(wilt.Words as NVARCHAR(MAX)) as stepsXML
	from tbl_Point tp
		join WorkItemLongTexts wilt on tp.TestCaseId = wilt.ID and wilt.FldID = @fldIdSteps
		left join tbl_TestResult ttr on ttr.TestPointId = tp.PointId and ttr.TestRunId = tp.LastTestRunId
	where tp.SuiteId in (select SuiteId from SuiteCTE2)
	and (TestCaseRevision = wilt.Rev
		OR (TestCaseRevision is null and wilt.Rev = (select MAX(Rev) from WorkItemLongTexts w where w.ID = wilt.ID and w.FldID = wilt.FldID))
		OR (TestCaseRevision <> wilt.Rev and wilt.Rev = (select MAX(Rev) from WorkItemLongTexts w where w.ID = wilt.ID and w.FldID = wilt.FldID and w.Rev <= TestCaseRevision)))
	union
	select distinct wilt.ID as id, wilt.Rev, CAST(wilt.Words as NVARCHAR(MAX)) as stepsXML
	from WorkItemLongTexts wilt
	where wilt.FldID = @fldIdSteps
		and wilt.ID in (select TargetID from LinksLatest where LinkType = @sharedStepLinkType and SourceID in (select testcaseid from tbl_Point tp where tp.SuiteId in (select SuiteId from SuiteCTE2)))

-- Shred the xml and create a table. nr column is vital for the creation of the next temp table.
create table #tempStepsTable (nr int identity, testId int, rev int, stepId int, stepType varchar(15), step varchar(300), result varchar(300), sharedStepId int) 
insert into #tempStepsTable (testId, rev, stepid, stepType, step, result, sharedStepId)
select TestCaseId, Rev, p.value('@id', 'int') as stepid, ISNULL(p.value('@type', 'VARCHAR(15)'), 'SharedStep') as stepType, p.value('./parameterizedString[1]', 'VARCHAR(300)') as step, p.value('./parameterizedString[2]', 'VARCHAR(300)') as result, p.value('@ref', 'int') as sharedStepId
from #tempTestStepsXML cross apply StepsXML.nodes('//*[@id > 0]') t(p)

-- This table is a work around for the problem of xpath function .position() does not work with SQL server. We need the sequence number of each step in order to match the
-- result of each step to the actual step.
create table #tempStepsTable2 (stepSequence int, testId int, rev int, stepId int, stepType varchar(15), step varchar(300), result varchar(300), sharedStepId int, primary key (testId, rev, stepId))
insert into #tempStepsTable2 (stepSequence, testId, rev, stepId, stepType, step, result , sharedStepId)
select nr + 1 - (select MIN(nr) from #tempStepsTable tst where tst.testId = #tempStepsTable.testId and tst.rev = #tempStepsTable.rev) as stepSequence, testId, rev, stepId, stepType, step, result, sharedStepId
from #tempStepsTable

create table #tempStepsTestResult (testRunId int, testResultId int, iterationId int, actionPath int, sharedStepsActionPath int, actualAP varchar(512), outcome int, errorMessage nvarchar(512), primary key (testRunId, testResultId, iterationId, actionPath, sharedStepsActionPath))
insert into #tempStepsTestResult (testRunId, testResultId, iterationId, actionPath, sharedStepsActionPath, actualAP, outcome, errorMessage)
select testRunId, testResultId, iterationId, CONVERT(int, CONVERT(varbinary(4), SUBSTRING(ActionPath, 1, 8), 2)) as actionPath, CONVERT(int, CONVERT(varbinary(4), SUBSTRING(ActionPath, 9, 8), 2)) as sharedStepsActionPath, ActionPath, outcome, errorMessage
from tbl_TestActionResult
where ActionPath <> ''

;WITH SuiteCTE
 AS
 (
 SELECT SuiteId, ParentSuiteId
  FROM tbl_Suite
  WHERE SuiteId = @ParamTestSuite
  UNION ALL
  SELECT S.SuiteId, S.ParentSuiteId
  FROM tbl_Suite AS S
  JOIN SuiteCTE AS C
   ON S.ParentSuiteId = C.SuiteId
  )
select 
	TS.SuiteId as TSId,
	TS.Title as TSTitle,
	WILAW.ID as TestCaseId,
	WILAW.Rev as TestCaseRev,
	ttr.TestCaseRevision,
	WILAW.Title as TestCaseTitle,
	cast(tac2.Content as xml) as SystemInformation,
	steps.stepSequence, 
	steps.stepid as stepID,
	steps.stepType as stepType,
	steps.step as stepAction,
	steps.result as stepResult,
	steps.rev as steprev,
	steps.sharedStepId as sharedStepId,
	sharedSteps.stepSequence as sharedStepsStepSequence,
	sharedSteps.stepid as sharedStepsStepID,
	sharedSteps.step as sharedStepsStepAction,
	sharedSteps.result as sharedStepsStepResult,
	sharedSteps.rev as sharedStepsSteprev,
	testPoint.PointId,
	testPoint.State,
	ISNULL(ttr.Outcome, 1) as TestOutcome,
	ttr.TestRunId,
	ttr.TestResultId,
	ttr.DateCompleted as TestDate,
	tar2.Comment as Comment,
	tar.ActionPath, 
	tar.Outcome as StepOutcome, 
	tar.ErrorMessage, 
	Constants.DisplayPart, 
	ta.FileName, 
	tac.Content
from tbl_Suite TS
	join tbl_SuiteEntry tse on TS.SuiteId = tse.SuiteId
	join WorkItemsLatestAndWere WILAW on WILAW.ID = tse.TestCaseId
	
	join #tempStepsTable2 steps on steps.testId = WILAW.ID 
	left join #tempStepsTable2 sharedSteps on steps.sharedStepId = sharedSteps.testId
		and sharedSteps.rev = (select max(rev) from #tempStepsTable2 tst2 where tst2.testId = sharedSteps.testId)

	left join tbl_Point testPoint on testPoint.SuiteId = TS.SuiteId and testPoint.TestCaseId = WILAW.ID

	left join tbl_TestResult ttr on ttr.TestPointId = testPoint.PointId and ttr.TestRunId = testPoint.LastTestRunId
		
	left join #tempStepsTestResult tar on ttr.TestRunId = tar.testRunId
		and ((steps.stepid = tar.actionPath and steps.stepType not like 'SharedStep') or (steps.stepid = tar.actionPath and sharedSteps.stepId = tar.sharedStepsActionPath))
		
	LEFT join tbl_TestActionResult tar2 on tar2.ActionPath = '' and tar2.TestRunId = ttr.TestRunId and tar2.TestResultId = ttr.TestResultId

	left join Constants on ttr.RunBy = Constants.TeamFoundationId
	left join tbl_Attachment ta on ta.ActionPath = tar.ActionPath and ta.TestRunId = ttr.TestRunId and ta.FileName like '%.png'
	left join tbl_AttachmentContent tac on ta.AttachmentId = tac.AttachmentId
	left join tbl_Attachment ta2 on ta2.TestRunId = ttr.TestRunId and ta2.FileName = 'SystemInformation.xml'
	left join tbl_AttachmentContent tac2 on tac2.AttachmentId = ta2.AttachmentId
where 
	TS.SuiteId in (Select SuiteId from SuiteCTE)
	and (WILAW.Rev = ISNULL(ttr.TestCaseRevision, (Select MAX(Rev) from WorkItemsLatestAndWere where ID = WILAW.ID)))
	and steps.rev = (select MAX(s.rev) from #tempStepsTable2 s where s.testId = WILAW.ID and s.rev <= ISNULL(ttr.TestCaseRevision, (select MAX(s.rev) from #tempStepsTable2 s where s.testId = WILAW.ID)))
order by TS.SuiteId, TestCaseId, stepSequence, sharedStepsStepSequence

drop table #tempTestStepsXML
drop table #tempStepsTable
drop table #tempStepsTable2
drop table #tempStepsTestResult

---

Jesper Fernström
QWise Software engineering – refactored!