How to: Team Card Rule Settings Mapping on Azure DevOps

This script and report extract all Team Card Rule Settings (Rule Name, Rule Criteria, and Card Color) within the Azure DevOps organization

Many customers that use Azure DevOps prefer to change Team Card Rule Settings, but when you have different configurations, it’s more complicated to standardized our teams and card colors for that everyone uses these same settings. Thinking about mapping Team Card Rule Settings 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 Card Rule Settings 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 Get = uses this REST API to get Backlog Levels on each 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. Cardrulesettings Get = uses this REST API to get card Rule settings on each BacklogLevel board

$uriCardRuleSettings = $UriOrganization + "$($project.id)/$($team.id)/_apis/work/boards/$($teamBoard.name)/cardrulesettings?api-version=6.1-preview.2"            
$TeamCardRuleSettings = Invoke-RestMethod -Uri $uriCardRuleSettings -Method get -Headers $AzureDevOpsAuthenicationHeader            
[array]$propertyRules = ($TeamCardRuleSettings.rules | Get-Member -MemberType NoteProperty).Name            
if ($propertyRules)
{
  Foreach ($cardRuleSetting in $TeamCardRuleSettings.rules)
  {
    Foreach ($fill in $cardRuleSetting.fill)
    {
      Write-Host $fill.name    
    }
  }
}

6. After extract all Team Card Rule Settings (Rule Name, Rule Criteria, and Card Color), 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;
• Team Backlog Level (3) = filter report using Team Backlog Level (Stories, Features, Epics) field;
• Team Card Rule Settings Informations (4) = List all information about Team Card Rule Settings (Team Project, Team Name, Team Backlog Level Board, Rule Name, Rule Criteria, and Card Color, which is represented by the background color on the respective line).

One more time, I wanted to thank Ewerton Rodrigues Jordão for the great help with PowerShell.