How to: Create Dashboard to visualize Branches ahead/behind

This script and report extract all repositories and branches on each Team Project in Azure DevOps to identify the gap between each branch and the default branch

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 Repositories and Branches on each project;
• $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 = use this REST API to list all projects on the 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. Repositories List = use this REST API to list all repositories on each Team Project

$uriRepositories = $UriOrganization + "$($project.id)/_apis/git/repositories?api-version=6.1-preview.1"    
$RepositoriesResult = Invoke-RestMethod -Uri $uriRepositories -Method get -Headers $AzureDevOpsAuthenicationHeader    
Foreach ($repo in $RepositoriesResult.value)    
{
    Write-Host $repo.name
}

4. Stats List = use this REST API to list all branches on each repository

$uriRepositoryStats = $UriOrganization + "$($project.id)/_apis/git/repositories/$($repo.id)/stats/branches?api-version=6.1-preview.1"            
$RepositoryStatsResult = Invoke-RestMethod -Uri $uriRepositoryStats -Method get -Headers $AzureDevOpsAuthenicationHeader            
Foreach ($repostats in $RepositoryStatsResult.value)            
{
    Write-Host $repostats.name
}

5. After extract all branches, and stats, this information is stored in a table in Azure SQL.

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

• Project Name (1) = Filter report using Project Name field;
• Repository Name (2) = Filter report using Repository Name field;
• Branch Name (3) = Filter using Branch Name field;
• Branches Information (4) = List all information about Branches and Stats (Team Project, Repository, Branch, Behind Count, Ahead Count).