How to: Create Variable Groups (Libraries) Dashboard on Azure DevOps

This script and report extract all Variables Groups on each Team Project within Azure DevOps

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 Variable Groups 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. Variablegroups Get Variable Groups = use this REST API to list all Variable Groups on each Team Project

$uriVariableGroups = $UriOrganization + "$($project.id)/_apis/distributedtask/variablegroups?api-version=6.0-preview.2"    
$VariableGroupsResult = Invoke-RestMethod -Uri $uriVariableGroups -Method get -Headers $AzureDevOpsAuthenicationHeader    
Foreach ($vg in $VariableGroupsResult.value)    
{
    Write-Host $vg.name
}

4. After extract all Variable Groups, this information is stored in a table in Azure SQL.

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

• Project Name (1) = Filter Team Project Name that contains Variable Groups;
• Variable Group Type (2) = Filter type of Variable Groups (Azure key vault and/or Azure DevOps);
• Variable Group (3) = Filter using Variable Group Name;
• Variable Group Informations(4) = List all information about Variable Groups (Team Project, Type, Key Vault name, variables, and values).