none
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!
    Monday, December 20, 2010 1:58 PM

Answers

  • 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!
    Tuesday, January 11, 2011 4:01 PM

All replies

  • 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!
    Monday, December 20, 2010 4:57 PM
  • 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!
    Monday, December 20, 2010 5:22 PM
  • 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!
    Monday, December 20, 2010 7:15 PM
  • 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!
    Tuesday, January 11, 2011 4:01 PM
  • 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.

    Friday, March 25, 2011 9:22 PM
  • 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!
    Tuesday, April 05, 2011 12:20 PM
  • Hi,

    Excellent! Thank you very much Jesper. This is exactly what my project leads are looking for. Coming from Quality Center to MTM we have encountered some challenges in obtaining certain reports/metrics. This appears to cover just about everything and more.

    If you have refined this query/report in anyway I would love to see it!

    Best regards

    Clint
    • Proposed as answer by Ashok.sa Tuesday, July 09, 2013 9:31 PM
    • Unproposed as answer by Ashok.sa Tuesday, July 09, 2013 9:31 PM
    Thursday, October 18, 2012 3:54 PM
  • Hi Jesper,

    Can this be run on warehouse as well? What are the advantages/disadvantage of running on a warehouse?

    Tuesday, July 09, 2013 9:34 PM
  • If possible it is always preferable to run your queries on the warehouse. In this case I dont believe the wraehouse contains the level of detail needed to find the test results for test steps.


    JESPER FERNSTRÖM
    Transcendent Group

    Sunday, July 14, 2013 1:31 PM