How to: Create Task Groups Dashboard on Azure DevOps

This script and report extract all Task 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 Task 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. Taskgroups List = use this REST API to list all Task Groups on each Team Project

$uriTaskGroups = $UriOrganization + "$($project.id)/_apis/distributedtask/taskgroups?api-version=6.1-preview.1"    
$TaskGroupsResult = Invoke-RestMethod -Uri $uriTaskGroups -Method get -Headers $AzureDevOpsAuthenicationHeader    
Foreach ($taskgroup in $TaskGroupsResult.value)    
{
    Write-Host $taskgroup.name
}

4. After extract all Task 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 report using Team Project Name field;
• Task Group Name (2) = Filter report using Task Group Name field;
• Task Name (3) = Filter using Task Name;
• Task Group Informations (4) = List all information about Task Groups (Team Project, Task Group, Task Group Version, Task Name, Task Name Version, Task Enabled).