How to: Test Plans Configurations Mapping on Azure DevOps

This script and report extract all Test Plans Configurations and Team Projects within the Azure DevOps organization

An original script is available on my GitHub repository. See below this script:

Let’s go understand each command used.

1. PowerShell script will receive the following parameters:

• $PAT = Personal Access token to connect on Azure DevOps;
• $Organization = Organization URL to list all Projects and respective Test Plans Configurations;
• $Connstr = connection string to Azure SQL Database that stores the report information. To create this report, it’s necessary to create previously a Azure SQL Server and Database and run a script below:

2. Projects List = uses this REST API to list all Team Projects on Azure DevOps organization

$AzureDevOpsAuthenicationHeader = @{Authorization = 'Basic ' + [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(":$($PAT)")) }
$UriOrganization = "https://dev.azure.com/$($Organization)/" 
$uriProject = $UriOrganization + "_apis/projects?`$top=500"
$ProjectsResult = Invoke-RestMethod -Uri $uriProject -Method get -Headers $AzureDevOpsAuthenicationHeader
Foreach ($project in $ProjectsResult.value)
{
    Write-Host $project.name
}

3. Configurations List = uses this REST API to list Test Plans Configurations on each Team Project

$uriTestPlansConfigurations = $UriOrganization + "$($project.id)/_apis/testplan/configurations?api-version=6.1-preview.1"    
$TestPlansConfigurationsResult = Invoke-RestMethod -Uri $uriTestPlansConfigurations -Method get -Headers $AzureDevOpsAuthenicationHeader    
Foreach ($tpconfig in $TestPlansConfigurationsResult.value)    
{
    Write-Host $tpconfig.name
}

3. After extract all Test Plans Configurations, this information is stored in a table in Azure SQL.

4. After insert information into a table, I connected this database on Power BI:

• Project Name (1) = Filter report using Project Name field;
• Test Plan Configuration Name (2) = Filter report using Test Plan Configuration Name field;
• Configuration Name (3) = Filter report using Configuration Name field;
• Configuration Value (4) = Filter report using Configuration Value field;
• Information (5) = List all information about Team Projects, Test Plan Configuration Name (on rows), and respective Configuration Variable Name and Configuration Variable Value (on columns). If the Test Plan Configuration uses the respective Configuration Name and Configuration Variables, the same will be marked (green ticks on a grid).