How to: List all Build Definitions and their latest executions on Azure DevOps

This script and report list all Build Definitions and respective latest execution 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. 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 latest executions;
• $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 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. 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 ($builDef in $BuildDefintionsResult.value)
{
    Write-Host $buildDef.name
}

4. Latest Get = uses this REST API to get the latest build for a Build Definition

$uriLatestBuild = $UriOrganization + "$($project.id)/_apis/build/latest/$($builDef.id)?api-version=6.1-preview.1"        
$LatestBuildResult = Invoke-RestMethod -Uri $uriLatestBuild -Method get -Headers $AzureDevOpsAuthenicationHeader
Write-Host $LatestBuildResult.buildNumber

5. After extract all Team Projects, Build Definitions and the latest Build execution, this information is stored in a table in Azure SQL.

6. 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;
• Build Result (3) = filter report using Build Result field;
• Build Reason (4) = filter report using Build Reason field;
• Requested For (5) = filter report using Requested For field;
• Branch (6) = filter report using Branch field;
• Build Definitions and latest executions (7) = List all information about Team Projects, Build Definitions, Build Number, Build Reason, Build Result, Build Requested For, Branch Name, Commit Number, Build Start Time, Build Duration, and respective Link to redirect it to Azure DevOps;
• Charts (8) = Charts to view aggregate information about this report.