How to: Board Columns and Swimlanes Mapping on Azure DevOps

This script and report extract all Board Columns and Swimlanes on Team Settings within the Azure DevOps organization

Many customers that use Azure DevOps prefer to change Team Settings (Board Columns and Swimlanes), but when you have different configurations, it’s more complicated to standardized our teams and verifies that everyone uses these same settings. Thinking about mapping Configuration Boards on Azure DevOps, the following report was created:

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 all Team Settings (Board Columns and Swimlanes) on Azure DevOps organization;
• $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 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. 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. Boards List = uses this REST API to list all boards on each respective Team

$uriTeamBoards = $UriOrganization + "$($project.id)/$($team.id)/_apis/work/boards?api-version=6.1-preview.1"        
$TeamBoardsResult = Invoke-RestMethod -Uri $uriTeamBoards -Method get -Headers $AzureDevOpsAuthenicationHeader        
Foreach ($teamboard in $TeamBoardsResult.value)        
{
    Write-Host $teamboard.name
}

5. Boards Get = uses this REST API to get the respective board on each respective Team

$TeamBoardsBacklogLevelResult = Invoke-RestMethod -Uri $teamboard.url -Method get -Headers $AzureDevOpsAuthenicationHeader            
Foreach ($boardColumn in $TeamBoardsBacklogLevelResult.columns)            {
    Write-Host $boardColumn.Name
}
Foreach ($boardLane in $TeamBoardsBacklogLevelResult.rows)            {
    Write-Host $$boardLane.name
}

6. After extract all Team Settings (Board Columns and Swimlanes), 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;
• Backlog Level (3) = Filter report using Backlog Level (Epics, Features, Stories) field;
• Board Column (4) = Filter report using Board Column field;
• Swimlane (5) = Filter report using Swimlane field;
• Board Columns (6) = List all information about Projects, Teams, Backlog Levels, and respective Board columns. If the respective columns are mapped on Backlog levels, the same will be marked (green ticks on a grid). In this example, Team C isn’t using customized Kanban columns (Analyze, Development, and Testing);
• Board Swimlanes (7) = List all information about Projects, Teams, Backlog Levels, and respective Swimlanes. If the respective swimlanes are mapped on Backlog levels, the same will be marked (green ticks on a grid). In this example, Team C isn’t using customized swimlane Technical Debt.