Where could I get the number of existing unit tests

Question

I like to get the number of the unit tests in each build which were running last year. So that I could see if the developers write some new tests.

For that, I get a view of the TFS warehouse database and find the view TestResultView which exactly gives me the data which I need.

Query example:

SELECT BuildId, YEAR(ResultDate) as year, MONTH(ResultDate) as month , DAY(ResultDate) as day, COUNT(*) AS Passed, 0 AS Failed FROM TestResultView
WHERE BuildDefinitionName = 'Test'
AND TestResultAttributesOutcome = 'Passed'
GROUP BY BuildId, YEAR(ResultDate), MONTH(ResultDate), DAY(ResultDate)
UNION ALL
SELECT BuildId, YEAR(ResultDate) as year, MONTH(ResultDate) as month, DAY(ResultDate) as day, 0 as Passed, COUNT(*) AS Failed FROM TestResultView
WHERE BuildDefinitionName = 'Test'
AND TestResultAttributesOutcome = 'Failed'
GROUP BY BuildId, YEAR(ResultDate), MONTH(ResultDate), DAY(ResultDate)

The only problem is that I only see the last four days.

Do I use the wrong view, database or is there any other way to get this information?

Thanks much for your help.

Andy Li-MSFT · Answer 1 · 2018-01-19T07:41:37.813

You can try to use the REST API to get the number of existing unit tests (Can only get the run unit tests, if the unit tests never never been run before, then we cannot get them).

Get the list of test runs first, then get the test results for the specific test run.

Get a list of test runs (REST API) :

GET https://server:8080/tfs/DefaultCollection/project/_apis/test/runs?api-version=1.0

Get a list of test results (REST API):

GET https://server:8080/tfs/Defaultcollection/project/_apis/test/runs/{runid}/results?api-version=3.0-preview

You can simply use below PowerShell script to get the count of unit tests for each test run in a project, then you can export the list to a .csv file (open in Excel) to calucate the number of unit tests in a specific project. You can filter by the test completeDate, or run the script periodically, then compare the count and test names to idetify the new added tests.

Param(
   [string]$collectionurl = "http://server:8080/tfs/Collection", 
   [string]$project = "ProjectName",
   [string]$user = "username",
   [string]$token = "password"
)

# Base64-encodes the Personal Access Token (PAT) appropriately
$base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $user,$token)))

#Get test runs
$testrunsUrl = "$collectionurl/$project/_apis/test/runs?api-version=1.0"
$testruns = (Invoke-RestMethod -Uri $testrunsUrl -Method Get -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)})

#Get unit tests from each test run
$UnitTestResults = @()

foreach ($testrun in $testruns.value)
{
$testrunID = $testrun.id

#Get test results for specific test run
$baseUrl = "$collectionurl/$project/_apis/test/runs/$testrunID/results?api-version=3.0-preview"
$response = Invoke-RestMethod -Uri $baseUrl -Method Get -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)}
$unittests = $response.value

$customObject = new-object PSObject -property @{
          "TestRunID" = $testrunID
          "CountOfUnitTest" = $response.count
          "UnitTestname" = @($unittests.testCaseTitle -join ',')|Select-Object
          "outcome" = @($unittests.outcome -join ',')|Select-Object 
          "completedDate" = @($unittests.completedDate -join ',')|Select-Object 
        } 

    $UnitTestResults += $customObject
}

$UnitTestResults | Select `
                TestRunID,
                CountOfUnitTest, 
                UnitTestname,
                outcome,
                completedDate | Where {$_.completedDate -like "*2017*"} #|export-csv -Path C:\LC\UnitTest.csv -NoTypeInformation

@user2523397 Have you resolved the issue by above answer? any update? — Andy Li-MSFT, Jan 25 '18 at 01:53

Where could I get the number of existing unit tests

1 Answers1