How to: Team Projects, Fields, and Picklists Mapping on Azure DevOps

This script and report extract all Team Projects, Fields, and Picklists within the Azure DevOps organization

Many customers that use Azure DevOps prefer to change System Process Templates (Agile, Basic, Scrum, and CMMI), thus creating your process using a feature Customize Process Templates. Therefore, it’s possible to create new work item types, fields, rules, etc.

But when you have different Process Templates, Work Item Types, and Fields, it is often necessary to identify all Picklists Fields that are used on these inherited processes. Thinking about mapping Team Projects and picklists fields with allowed values on Azure DevOps, this 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 Projects, Fields, and Pick Lists 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. Fields List = uses this REST API to list all picklist fields on the respective Team Project

$uriProjectPickList = $UriOrganization + "$($project.id)/_apis/wit/fields?api-version=6.0"    
$ProjectsPickListResult = Invoke-RestMethod -Uri $uriProjectPickList -Method get -Headers $AzureDevOpsAuthenicationHeader    
$ProjectsPickListResult = $ProjectsPickListResult.value | where-object {$_.isPicklist -eq $true}

4. Lists Get = uses this REST API to get all allowed values on the respective picklist

$uriPickList = $UriOrganization + "_apis/work/processes/lists/$($fieldPickList.picklistId)?api-version=6.0-preview.1"        
$PickListResult = Invoke-RestMethod -Uri $uriPickList -Method get -Headers $AzureDevOpsAuthenicationHeader        
$items = $PickListResult.items        
$items = "$items".Replace(" ","`r`n")

5. After extracting all Team Projects, Fields, and Picklists, this information is stored in a table in Azure SQL.

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

• Team Project (1) = Filter report using Team Project field;
• Field Reference Name (2) = Filter report using Field Reference Name field;
• Report information (3) = List all information about Team Projects, Fields, Data Type, and Allowed Values