How to: Create Dashboard to visualize Branches ahead/behind on GitHub

This script and report extract all repositories and branches on GitHub 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 GitHub organization;
• $Organization = GitHub Organization name;
• $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. List organization repositories = use this REST API to list repositories for the specified organization

$uriRepositories = "$($UriOrganization)/repos"
$RepositoriesResult = Invoke-RestMethod -Headers $headers -Uri $uriRepositories
foreach ($repo in $RepositoriesResult)
{
    Write-Host $repo.name
    Write-Host $repo.default_branch
}

3. List branches = use this REST API to list branches on each repository

$urlBranchesRepo = $repo.branches_url.Replace('{/branch}','')    
$BranchesRepoResult = Invoke-RestMethod -Headers $headers -Uri $urlBranchesRepo    
foreach ($branchRepo in $BranchesRepoResult)
{
    Write-Host $branchRepo.name
}

4. Branches compare = use this REST API to compare a default branch and another branch

$uriCompare = "$($UriRepos)/$($repo.name)/compare/$($repo.default_branch)...$($branchRepo.name)"        
$ComparesResult = Invoke-RestMethod -Headers $headers -Uri $uriCompare
Write-Host $ComparesResult.ahead_by 
Write-Host $ComparesResult.behind_by

5. After extracting all repositories, branches and commits ahead/behind, this information is stored in a table in Azure SQL.

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

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