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

This script and report list all Release Definitions and respective latest executions 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, Release 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 Release Definitions on each Team Project

$uriReleaseDefinitions = $uriReleases + "$($project.id)/_apis/release/definitions?api-version=6.1-preview.4"    
$ReleaseDefintionsResult = Invoke-RestMethod -Uri $uriReleaseDefinitions -Method get -Headers $AzureDevOpsAuthenicationHeader    
Foreach ($releaseDef in $ReleaseDefintionsResult.value)    
{
    Write-Host $releaseDef.name
}

4. Releases List = uses this REST API to get the latest Release for a Release Definition

$uriLatestRelease = $uriReleases + "$($project.id)/_apis/release/releases?definitionId=$($releaseDef.id)&queryOrder=descending&`$top=1&`$expand=environments&api-version=6.0"        
$LatestReleaseResult = Invoke-RestMethod -Uri $uriLatestRelease -Method get -Headers $AzureDevOpsAuthenicationHeader        
if ($LatestReleaseResult.count -gt 0)        
{
    Write-Host $LatestReleaseResult.value[0].name
}

5. After extract all Team Projects, Release Definitions and the latest Release executions, 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;
• Release Definition Name (2) = Filter report using Release Definition Name field;
• Release Environment Name (3) = Filter report using Release Environment Name field;
• Release Environment Result (4) = filter report using Release Environment Result field;
• Requested For (5) = filter report using Requested For field;
• Release Definitions and latest executions (6) = List all information about Team Projects, Release Definitions, Environment Name, Release Number, Release Start Time, Release Result, Release Requested For, and respective Link to redirect it to Azure DevOps;
• Charts (7) = Charts to view aggregate information about this report.