How to: Create Dashboard to visualize Teams and Iteration Capacities

This script and report extract all teams and iteration capacities on each Team Project in 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 Teams and Iteration Capacities 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 = uses 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. Teams Get All Teams = uses this REST API to list all teams on each Team Project

$uriTeams = $UriOrganization + "_apis/projects/$($project.id)/teams"    
$TeamsResult = Invoke-RestMethod -Uri $uriTeams -Method get -Headers $AzureDevOpsAuthenicationHeader    
Foreach ($team in $TeamsResult.value)    
{
    Write-Host $team.name
}

4. Iterations List = uses this REST API to list all iterations on each team

$uriSprintsTeam = $UriOrganization + "$($project.id)/$($team.id)/_apis/work/teamsettings/iterations"        
$SprintsTeamResult = Invoke-RestMethod -Uri $uriSprintsTeam -Method get -Headers $AzureDevOpsAuthenicationHeader        
Foreach ($sprintteam in $SprintsTeamResult.value)        
{
    Write-Host $sprintteam.name
}

5. IterationCapacities = uses this REST API to get total capacity and a total day off on each iteration

$uriTeamIterationCapacities =  $UriOrganization + "$($project.id)/_apis/work/iterations/$($sprintteam.id)/IterationCapacities?api-version=6.1-preview.1"            
$TeamIterationCapacitiesResult = Invoke-RestMethod -Uri $uriTeamIterationcapacities -Method get -Headers $AzureDevOpsAuthenicationHeader
Write-Host $TeamIterationCapacitiesResult.totalIterationCapacityPerDay
Write-Host $TeamIterationCapacitiesResult.totalIterationDaysOff

6. After extract all projects, teams, iterations, and capacities, this information is stored in a table in Azure SQL.

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

• Team Project (1) = Filter report using Team Project field;
• Team Name (2) = Filter report using Team Name field;
• Iteration Name (3) = Filter using Iteration Name field;
• Report Information (4) = List all information about Team Project, Team Name, Iteration Name, and respective Capacity per Day and Days off.