How to: Mapping YAML Multi-stage and Approvals Required on Azure DevOps

This script and report list all YAML multi-stages and respective executions that depend on Approvals within the Azure DevOps organization

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

Let’s understand each command used.

1. The PowerShell script will receive the following parameters:

• $PAT = Personal Access token to connect on Azure DevOps;
• $Organization = Organization URL to list all Team Projects, Build Definitions and approval required;
• $Connstr = connection string to Azure SQL Database that stores the report information. To create this report, it’s necessary to create previously an Azure SQL Server and Database and run the 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. Definitions List = uses this REST API to list all Build Definitions on each Team Project

$uriBuildDefinitions = "$($UriOrganization)$($project.id)/_apis/build/definitions?api-version=6.1-preview.7"
$BuildDefintionsResult = Invoke-RestMethod -Uri $uriBuildDefinitions -Method get -Headers $AzureDevOpsAuthenicationHeader
Foreach ($buildDef in $BuildDefintionsResult.value)
{
  Write-Host $buildDef.id
}

4. Builds List = uses this REST API to list all InProgress Builds on each Build Definition

 $uriBuilds="$($UriOrganization)$($project.id)/_apis/build/builds?definitions=$($buildDef.id)&queryOrder=startTimeAscending"
 $Builds = Invoke-RestMethod -Uri $uriBuilds -Method get -Headers $AzureDevOpsAuthenicationHeader
 $Builds = $Builds.value | Where-Object {$_.status -eq 'InProgress'}
 Foreach ($build in $Builds)
 {
   Write-Host $build.id
 }

5. Timeline Get = uses this REST API to get InProgress Stage Approval (step Checkpoint.Approval) on each Build runs

$uriBuildTimeline = "$($UriOrganization)$($project.id)/_apis/build/builds/$($build.id)/timeline?api-version=6.0"
$Timeline = Invoke-RestMethod -Uri $uriBuildTimeline -Method get -Headers $AzureDevOpsAuthenicationHeader
$Timeline = $Timeline.records
$CheckpointApproval = $Timeline | Where-Object {($_.name -eq "Checkpoint.Approval") -and ($_.state -eq "inProgress")}
if ($CheckpointApproval)
{
   $Checkpoint = $Timeline | Where-Object {($_.id -eq $CheckpointApproval.parentId) -and ($_.type -eq "Checkpoint")}
   $PendingStage = $Timeline | Where-Object {($_.id -eq $Checkpoint.parentId) -and ($_.type -eq "Stage")}
   Write-Host $PendingStage.name
}

6. After extract all Team Projects, Build Definitions, and InProgress Stage Approval, this information is stored in a table in Azure SQL.

7. After insert information into a table, connect this database on Power BI:

• Project Name (1) = Filter report using Project Name field;
• Build Definition Name (2) = Filter report using Build Definition Name field;
• Stage Name (3) = filter report using Stage Name field;
• Environment Name (4) = filter report using Environment Name field;
• YAML Multi-Stage and Approval Required (5) = List all information about Team Projects, Build Ids, Build Definitions, Build Numbers, Stages, and Environments;
• Charts (6) = Charts to view aggregate information about this report.