How to: Project Stats Mapping on Azure DevOps

This script and report extract all Project Stats (including Boards, Repos, and Pipelines information) within the Azure DevOps organization

Many customers that use Azure DevOps need to identify if projects on your organization are being used. When you navigate a specific project, you have a feature named View project activity, but you have this view only within the project. This report will show this mapping with all the projects in the organization based on the last 30 days' activities.

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 Proejcts and Stats 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. Contribution HierarchyQuery = uses this REST API to get the Project stats section on the landing page on Azure DevOps

$uriProjectStats = $UriOrganization + "_apis/Contribution/HierarchyQuery/project/$($project.id)?api-version=6.1-preview.1"       
$projectStatsBody = @{        
"contributionIds"= @("ms.vss-work-web.work-item-metrics-data-provider-verticals", "ms.vss-code-web.code-metrics-data-provider-verticals", "ms.vss-code-web.build-metrics-data-provider-verticals")        "dataProviderContext" = @{            "properties" =@{                "numOfDays"=30                "sourcePage"=@{                    "url"=($UriOrganization + $project.name)                    "routeId"="ms.vss-tfs-web.project-overview-route"                    "routeValues" =@{                        "project" = $project.id                        "controller"="Apps"                        "action"="ContributedHub"                        "serviceHost"=$Organization                        }                    }                          }            }        }  | ConvertTo-Json -Depth 5     
$projectStatsResult = Invoke-WebRequest -Uri $uriProjectStats -Headers $AzureDevOpsAuthenicationHeader -Method Post -Body $projectStatsBody     
$projectStatsJson = ConvertFrom-Json $projectStatsResult.Content

4. Get Project Metrics = uses this REST API to get build metrics for a project

$uriBuildMetrics = $UriOrganization + "$($project.id)/_apis/build/Metrics/Daily?minMetricsTime=$($monthAgo)"     
$buildMetricsResult = Invoke-RestMethod -Uri $uriBuildMetrics -Method get -Headers $AzureDevOpsAuthenicationHeader    
$totalBuilds = 0    
$buildMetricsResult.value | Where-Object {$_.name -eq 'TotalBuilds'} | ForEach-Object { $totalBuilds+= $_.intValue }

5. Get Release Metrics = uses this REST API to get releases metrics for a project

$totalReleases = 0    
$UriReleaseMetrics = $UriOrganizationRM + "$($project.id)/_apis/Release/metrics?minMetricsTime=minMetricsTime=$($monthAgo)"    
$releaseMetricsResult = Invoke-RestMethod -Uri $UriReleaseMetrics -Method get -Headers $AzureDevOpsAuthenicationHeader    
$releaseMetricsResult.value | ForEach-Object { $totalReleases+= $_.value }

6. After extract all Team Projects and Stats (including Boards, Repos, and Pipelines), this information is stored in a table in Azure SQL.

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

• Project Name(1) = Filter report using Project Name field;
• Project Stats Mapping (4) = List all information about Team Project and Stats (including Boards, Repos, and Pipelines).

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