Azure DevOps - getting project stats for all projects

If you have quite a lot of projects in your Azure DevOps organization and is looking for a way to filter the inactive projects out, that’s what  we will discuss today.

 

This is going to be based on the data the we see in the "Project stats" section on the landing page of an ADO project. This data gets fetched using the  "Contribution/HierarchyQuery/project" REST Api, which for some reason doesn't seem to be documented. So we may have to keep an eye out for any changes.

 

The apis lets you get project stats like:

1. Work Items : created and updated
2. Repos : Commits pushed with the number of authors, PRs created and completed

the range can be specified, and accepts a maximum value of 30 days.

If you use TFVC, that data is also returned - like the number of changesets and authors. I am not using it for the calculation here as we don't use TFVC. You can also get the metrics for builds and releases - just in case you are interested, the api for build metrics looks like: 

https://dev.azure.com/{orgname}/{projectid}/_apis/build/Metrics/Daily?minMetricsTime=2020-05-09T14%3A16%3A11.100Z

 

And the one for release looks like: 

https://vsrm.dev.azure.com/{orgname}/{projectid}/_apis/Release/metrics?minMetricsTime=2020-05-09T00%3A00%3A00.000Z

  

The below script checks if there was any activity on boards or repos for the projects, for the last 30 days - and then if there was none, will mark them as inactive. It just checks whether the sum of the stats for Work Items and Repos is 0. The script takes the organization name and a pat with collection level rights as the inputs, and the output is a csv file with the project stats exported.

 

Hope you find this useful!

	param (
	[Parameter(Mandatory=$true)]
	[string] $accountName = "",
	[Parameter(Mandatory=$true)]
	[string] $pat = ""
	)
	# Create the AzureDevOps auth header
	$base64authinfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(":$pat"))
	$vstsAuthHeader = @{"Authorization"="Basic $base64authinfo"}
	$allHeaders = $vstsAuthHeader + @{"Content-Type"="application/json"; "Accept"="application/json"}
	$projectsList=@()
	try
	{
	#get list of projects in the org
	$projectsJson=@()
	$continuationToken=$null
	do{
	$getProjectsUrl= "https://dev.azure.com/$accountName/_apis/projects?continuationtoken=$continuationToken&api-version=5.1"
	$projectsResult = Invoke-WebRequest -Headers $allHeaders -Method GET "$getProjectsUrl"
	if ($projectsResult.StatusCode -ne 200)
	{
	Write-Output $projectsResult.Content
	throw "Failed to query projects"
	}
	$continuationToken = $projectsResult.Headers.'x-ms-continuationtoken'
	$projectsJson += ConvertFrom-Json $projectsResult.Content
	}
	while($continuationToken)
	#get the project stats for each project
	$processingCount =0
	for($p=0; $p -lt $projectsJson.Count; $p++)
	{
	foreach($project in $projectsJson[$p].value)
	{
	$isActive = $True
	$processingCount++
	Write-Output ("Processimg project " + $processingCount + " of " + $projectsJson.value.count)
	$getProjectStatsUrl = "https://dev.azure.com/$accountName/_apis/Contribution/HierarchyQuery/project/"+$project.id + "?api-version=5.0-preview.1"
	$getProjectStatsBody = @{
	"contributionIds"= @("ms.vss-work-web.work-item-metrics-data-provider-verticals", "ms.vss-code-web.code-metrics-data-provider-verticals")
	"dataProviderContext" = @{
	"properties" =@{
	"numOfDays"=30
	"sourcePage"=@{
	"url"=("https://dev.azure.com/$accountName/"+ $project.name)
	"routeId"="ms.vss-tfs-web.project-overview-route"
	"routeValues" =@{
	"project" = $project.id
	"controller"="Apps"
	"action"="ContributedHub"
	"serviceHost"=$accountName
	}
	}
	}
	}
	} | ConvertTo-Json -Depth 5
	$projectStatsResult = Invoke-WebRequest -Uri "$getProjectStatsUrl" -Headers $allHeaders -Method Post -Body $getProjectStatsBody
	$projectStatsJson = ConvertFrom-Json $projectStatsResult.Content
	#extract the data for project stats
	$projectWitCompleted = $projectStatsJson.dataProviders.'ms.vss-work-web.work-item-metrics-data-provider-verticals'.workMetrics.workItemsCompleted
	$projectWitCreated = $projectStatsJson.dataProviders.'ms.vss-work-web.work-item-metrics-data-provider-verticals'.workMetrics.workItemsCreated
	$projectCommitsPushed = $projectStatsJson.dataProviders.'ms.vss-code-web.code-metrics-data-provider-verticals'.gitmetrics.commitsPushedCount
	$projectPRsCreated = $projectStatsJson.dataProviders.'ms.vss-code-web.code-metrics-data-provider-verticals'.gitmetrics.pullRequestsCreatedCount
	$projectPRsCompleted = $projectStatsJson.dataProviders.'ms.vss-code-web.code-metrics-data-provider-verticals'.gitmetrics.pullRequestsCompletedCount
	$projectAuthors = $projectStatsJson.dataProviders.'ms.vss-code-web.code-metrics-data-provider-verticals'.gitmetrics.authorsCount
	#set to 0 if null.
	if(!$projectCommitsPushed){$projectCommitsPushed=0}
	if(!$projectPRsCreated){$projectPRsCreated=0}
	if(!$projectPRsCompleted){$projectPRsCompleted=0}
	if(!$projectAuthors){$projectAuthors=0}
	#if the sum of metrics is 0, mark the project as inactive.
	if($projectWitCompleted + $projectWitCreated + $projectCommitsPushed + $projectPRsCreated + $projectPRsCompleted + $projectAuthors -eq 0){
	$isActive = $False
	}
	#build the project details.
	$projectDetailsObject=@{
	ProjectName = $project.name
	ProjectID = $project.id
	ProjectBoardStats="Work Items created : $projectWitCreated, Work Items completed : $projectWitCompleted"
	ProjectPRStats="PRs opened : $projectPRsCreated, PRs completed : $projectPRsCompleted"
	ProjectCommitStats = "$projectCommitsPushed commits pushed by $projectAuthors authors"
	Active = $isActive
	}
	$obj = New-Object -Type PSObject -Prop $projectDetailsObject
	$projectsList += $obj
	}
	}
	#export to csv
	$projectsList | Select-Object -Property ProjectName, ProjectID, ProjectBoardStats, ProjectPRStats, ProjectCommitStats, Active | Export-CSV -Path (".\$accountName"+"_ProjectDetails.csv") -NoTypeInformation
	Write-Output "Processes successfully!"
	}
	catch
	{
	throw "Failed to get project stats. Details : $_"
	}

view raw GetProjectStats.ps1 hosted with ❤ by GitHub