Forum Discussion
Azure DevOps - Collect several statistics from your az DevOps Organizations using PowerShell
I published some other PowerShell scripts to manage or follow Azure DevOps platform:
- Azure DevOps - How to collect all accounts from Organization using PowerShell
- Azure DevOps - Send an email to each Project Administrator with Account listed into using PowerShell
But some other questions are opened immediately:
- What is the usage of our Azure DevOps platform?
- Can we confirm the success or fail from usage point of view?
- What is the maturity of my developer population related to DevOps (Repos, branches, pipelines, tests, …) ?
- What is the volume of user stories per type, with specific focus on current and previous years ?
- …
So, this script will help you to have information responding to some of those questions and much more.
It's easy to read and adapt, based on Azure DevOps API proposed by Microsoft.
#Installation Azure CLI: https://docs.microsoft.com/en-us/cli/azure/install-azure-cli-windows?view=azure-cli-latest&tabs=azure-powershell
#Invoke-WebRequest -Uri https://aka.ms/installazurecliwindows -OutFile .\AzureCLI.msi; Start-Process msiexec.exe -Wait -ArgumentList '/I AzureCLI.msi /quiet'; rm .\AzureCLI.msi
#Activation Az DevOps: az devops configure --defaults organization=$Organization
#region -- Customized Script Settings (need to be reviewed) ---
# ================== / Parameters to Adapt \ ==================
[string]$GlobalPATForAllORganizations= "YourPersonalAccessTokenIfGlobalone"
[string]$JSonStatsFolderPath = "C:\DEVOPSSTATISTICS"
# ================== \ Parameters to Adapt / ==================
#endregion
#region -- Internal Script Settings ---
$OrganizationList = @()
[string]$PAT = ""
[string]$OrganizationName = ""
[string]$Organization = ""
[string]$ProjectStatisticsJSonFilePath = ""
[string]$ProjectListJSonFilePath = ""
$DataRefreshDate = Get-Date -Format "yyyy-MM-dd"
[string]$ProjectProcessModelProperty = ""
[string]$ProjectProcessModelIDProperty = ""
[int]$TotalProjectPerOrganization = 0
[int]$ProjectInProgress = 0
[int]$TotalRepositoriesPerProject = 0
[int]$TotalBranchesPerProject = 0
[int]$TotalWorkItemsPerProject = 0
[int]$TotalOtherItems = 0
[int]$TotalWorkItemsPerProjectCurrentYear = 0
[int]$TotalOtherItemsCurrentYear = 0
[int]$TotalWorkItemsPerProjectPreviousYear = 0
[int]$TotalOtherItemsPreviousYear = 0
[int]$CurrentYear = 0
[int]$PreviousYear = 0
[string]$PreviousyearStart = ""
[string]$PreviousyearEnd = ""
$StandardWorkItems= "Bug", "Epic", "Feature", "Issue", "Task", "Test Case", "User Story" #To adapt if process in not Agile model
[string]$RepoBranchDetails = ""
[string]$WorkItemCountPerType = ""
[string]$MyWiQlCommand = ""
[string]$uriAccount = ""
[int]$PipelineCount = 0
[int]$TestPlansCount = 0
$ProjectStatisticsOveralOrganization = @()
[string]$ProjectStatisticsOveralOragnizationJSonFilePath = "$JSonStatsFolderPath\DevOps-ProjectStatistics.json"
#endregion
#region -- Each Az DevOps Organization --
#organization1
$PAT = $GlobalPATForAllORganizations #https://dev.azure.com/organization1/_usersSettings/tokens
$OrganizationName = "organization1"
$OrganizationWithPAT = New-Object PSObject -property @{OrganizationName=$OrganizationName;OrganizationPAT=$PAT}
$OrganizationList += $OrganizationWithPAT
#organization2
$PAT = $GlobalPATForAllORganizations #https://dev.azure.com/organization2/_usersSettings/tokens
$OrganizationName = "organization2"
$OrganizationWithPAT = New-Object PSObject -property @{OrganizationName=$OrganizationName;OrganizationPAT=$PAT}
$OrganizationList += $OrganizationWithPAT
#organization3
$PAT = $GlobalPATForAllORganizations #https://dev.azure.com/organization3/_usersSettings/tokens
$OrganizationName = "organization3"
$OrganizationWithPAT = New-Object PSObject -property @{OrganizationName=$OrganizationName;OrganizationPAT=$PAT}
$OrganizationList += $OrganizationWithPAT
#endregion
foreach($MyOrganization in $OrganizationList)
{
write-host " --------------------------------------------------------------------" -ForegroundColor White -BackgroundColor DarkYellow
write-host " ----- Organization :", $MyOrganization.OrganizationName ," ------" -ForegroundColor White -BackgroundColor DarkYellow
write-host " --------------------------------------------------------------------" -ForegroundColor White -BackgroundColor DarkYellow
$TotalProjectPerOrganization = 0
$ProjectInProgress = 0
$CurrentYear = $((Get-Date).year)
$PreviousYear = $((Get-Date).year - 1)
$AzureDevOpsAuthenicationHeader = @{Authorization = 'Basic ' + [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(":$($MyOrganization.OrganizationPAT)")) }
$Organization = "https://dev.azure.com/$($MyOrganization.OrganizationName)/"
$ProjectStatisticsPerOrganization = @()
$ProjectStatisticsJSonFilePath = "$JSonStatsFolderPath\$($MyOrganization.OrganizationName)-ProjectStatistics.json"
$ProjectListJSonFilePath = "$JSonStatsFolderPath\$($MyOrganization.OrganizationName)-ProjectList.json"
echo $($MyOrganization.OrganizationPAT) | az devops login --org $Organization
az devops configure --defaults organization=$Organization
#$allProjects = az devops project list --org $Organization --top 100 | ConvertFrom-Json | Select-Object -ExpandProperty value | Sort-Object name #Select 1st project to validate script
#$allProjects = az devops project list --org $Organization --top 10000 | ConvertFrom-Json | Select-Object -ExpandProperty value | Where-Object name -eq "MyProjectName" | Sort-Object name #Select filtered project
$allProjects = az devops project list --org $Organization --top 10000 | ConvertFrom-Json | Select-Object -ExpandProperty value | Sort-Object name #Select all projects
#$allProjects
#$allProjects | ConvertTo-Json | Out-File -FilePath $ProjectListJSonFilePath -Encoding UTF8 #If you want to save a copy
$TotalProjectPerOrganization = $allProjects.Count
foreach($myProject in $allProjects)
{
$ProjectInProgress += 1
$TotalRepositoriesPerProject = 0
$TotalWorkItemsPerProject = 0
$TotalOtherItems = 0
$TotalWorkItemsPerProjectCurrentYear = 0
$TotalOtherItemsCurrentYear = 0
$TotalWorkItemsPerProjectPreviousYear = 0
$TotalOtherItemsPreviousYear = 0
$TotalBranchesPerProject = 0
$PipelineCount = 0
$TestPlansCount = 0
$RepoBranchDetails = ""
$WorkItemCountPerType = ""
$ProjectProcessModelProperty = ""
$ProjectProcessModelIDProperty = ""
$ProcessModelWIWithStates = @()
write-host " ---------------------------------------------------------------------------------------------------- " -ForegroundColor Yellow
write-host " -- Project Name:", $myProject.Name, "[", $ProjectInProgress, " of ", $TotalProjectPerOrganization, "] --" -ForegroundColor Yellow
write-host " > Last Update:", $myProject.lastUpdateTime -ForegroundColor DarkYellow
#write-host " > Project Description:", $myProject.Description -ForegroundColor DarkYellow
write-host " ---------------------------------------------------------------------------------------------------- " -ForegroundColor Yellow
# ------- GET PROJECT PROCESS MODEL DETAILS -------
write-host " - - - - - "
$uriAccount = $Organization + "_apis/projects/"+ $myProject.id +"/properties?api-version=6.0-preview.1"
$response = Invoke-RestMethod -Uri $uriAccount -Headers $AzureDevOpsAuthenicationHeader -Method Get -ContentType "application/json"
$ProjectProcessModelIDProperty = $response.value.Where({$_.name -eq "System.ProcessTemplateType"}).value
$uriAccount = $Organization + "_apis/work/processes/{"+ $ProjectProcessModelIDProperty +"}?api-version=6.0-preview.2"
$response = Invoke-RestMethod -Uri $uriAccount -Headers $AzureDevOpsAuthenicationHeader -Method Get -ContentType "application/json"
$ProjectProcessModelProperty = $response.name
write-host " - Project Process Model:", $ProjectProcessModelProperty, "-ID:", $ProjectProcessModelIDProperty -ForegroundColor Green
$uriAccount = $Organization + "_apis/work/processes/{"+ $ProjectProcessModelIDProperty +'}/workitemtypes?$expand=States&api-version=6.0-preview.2'
$response = Invoke-RestMethod -Uri $uriAccount -Headers $AzureDevOpsAuthenicationHeader -Method Get -ContentType "application/json"
$ProcessModelWIWithStatesTemp = $response.value
foreach($myProcessModelWITypeWithStates in $ProcessModelWIWithStatesTemp)
{
write-host " - WI Type:", $myProcessModelWITypeWithStates.Name
foreach($MyWIState in $myProcessModelWITypeWithStates.states)
{
write-host " -> WI Type States:", $MyWIState.name, "- Category:", $MyWIState.stateCategory
$ProcessModelWIWithStates += New-Object -TypeName PSObject -Property @{
WI_Type=$myProcessModelWITypeWithStates.Name
WI_Type_States=$MyWIState.name
WI_Type_States_Category=$MyWIState.stateCategory
}
}
}
$ProcessModelWIWithStates = $ProcessModelWIWithStates | ConvertTo-Json
# ------- GET DETAILS FOR REPOSITORIES AND BRANCHES -------
$ProjectRepositories = az repos list --org $Organization --project $myProject.Name | ConvertFrom-Json
#$ProjectRepositories
$TotalRepositoriesPerProject = $ProjectRepositories.Count
foreach($myRepository in $ProjectRepositories)
{
write-host " - Repository Name:", $myRepository.name, "- ID:", $myRepository.id -ForegroundColor Cyan
$RepositoryBranches = az repos ref list --org $Organization --project $myProject.Name --repository $myRepository.id | ConvertFrom-Json
write-host " >>> Branch Total:", $RepositoryBranches.count -ForegroundColor Cyan
$TotalBranchesPerProject += $RepositoryBranches.count
$RepoBranchDetails += "[$($myRepository.name)]:$($RepositoryBranches.count);"
}
if($RepoBranchDetails.EndsWith(";"))
{
$RepoBranchDetails = $RepoBranchDetails.Substring(0,$RepoBranchDetails.Length-1)
}
# ------- GET DETAILS FOR WORK ITEMS AND TYPES -------
write-host " - - - - - TOTAL WORK ITEMS - - - - - "
$MyWorkItemPerType = @()
$MyWiQlCommand = "Select [System.Id] FROM WorkItems WHERE [System.TeamProject]='$($myProject.Name)'"
$body = @{ query = $MyWiQlCommand }
$bodyJson=@($body) | ConvertTo-Json
$uriAccount = $Organization + "_apis/wit/wiql?api-version=6.0"
$response = Invoke-RestMethod -Uri $uriAccount -Headers $AzureDevOpsAuthenicationHeader -Method Post -ContentType "application/json" -Body $bodyJson
$ProjectWorkItemsAPI= $response.workItems
write-host " - Work Items Total:", $ProjectWorkItemsAPI.count -ForegroundColor Magenta
$TotalWorkItemsPerProject = $ProjectWorkItemsAPI.Count
$TotalOtherItems = $TotalWorkItemsPerProject
#check for each Type of Work Item
$MyWorkItemCount = New-Object PSObject
foreach($MyWorkItemType in $StandardWorkItems)
{
$MyWiQlCommand = "Select [System.Id] FROM WorkItems WHERE [System.TeamProject]='$($myProject.Name)' AND [System.WorkItemType] = '$($MyWorkItemType)' "
$body = @{ query = $MyWiQlCommand }
$bodyJson=@($body) | ConvertTo-Json
$uriAccount = $Organization + "_apis/wit/wiql?api-version=6.0"
$response = Invoke-RestMethod -Uri $uriAccount -Headers $AzureDevOpsAuthenicationHeader -Method Post -ContentType "application/json" -Body $bodyJson
$ProjectWorkItemsAPI= $response.workItems
if($ProjectWorkItemsAPI.count -gt 0)
{
write-host " >>> WorkItems Type (API):", $MyWorkItemType, " - Total:", $ProjectWorkItemsAPI.count -ForegroundColor Magenta
$MyWorkItemCount | Add-Member -type NoteProperty -Name $($MyWorkItemType) -Value $($ProjectWorkItemsAPI.count)
$TotalOtherItems -= $ProjectWorkItemsAPI.count
}
else
{
$MyWorkItemCount | Add-Member -type NoteProperty -Name $($MyWorkItemType) -Value 0
}
}
# ------- GET DETAILS FOR WORK ITEMS AND TYPES FOR CURRENT YEAR -------
write-host " - - - - - CURRENT YEAR WORK ITEMS - - - - - "
$MyWorkItemCountCurrentYear = @()
$MyWiQlCommand = "Select [System.Id] FROM WorkItems WHERE [System.TeamProject]='$($myProject.Name)' AND [System.CreatedDate] >= @StartOfYear"
$body = @{ query = $MyWiQlCommand }
$bodyJson=@($body) | ConvertTo-Json
$uriAccount = $Organization + "_apis/wit/wiql?api-version=6.0"
$response = Invoke-RestMethod -Uri $uriAccount -Headers $AzureDevOpsAuthenicationHeader -Method Post -ContentType "application/json" -Body $bodyJson
$ProjectWorkItemsAPI= $response.workItems
write-host " - Work Items Total Current Year:", $ProjectWorkItemsAPI.count -ForegroundColor Magenta
$TotalWorkItemsPerProjectCurrentYear = $ProjectWorkItemsAPI.Count
$TotalOtherItemsCurrentYear = $TotalWorkItemsPerProjectCurrentYear
#check for each Type of Work Item
$MyWorkItemCountCurrentYear = New-Object PSObject
foreach($MyWorkItemType in $StandardWorkItems)
{
$MyWiQlCommand = "Select [System.Id] FROM WorkItems WHERE [System.TeamProject]='$($myProject.Name)' AND [System.WorkItemType] = '$($MyWorkItemType)' AND [System.CreatedDate] >= @StartOfYear"
$body = @{ query = $MyWiQlCommand }
$bodyJson=@($body) | ConvertTo-Json
$uriAccount = $Organization + "_apis/wit/wiql?api-version=6.0"
$response = Invoke-RestMethod -Uri $uriAccount -Headers $AzureDevOpsAuthenicationHeader -Method Post -ContentType "application/json" -Body $bodyJson
$ProjectWorkItemsAPI= $response.workItems
if($ProjectWorkItemsAPI.count -gt 0)
{
write-host " >>> Current Year WorkItems Type (API):", $MyWorkItemType, " - Total:", $ProjectWorkItemsAPI.count -ForegroundColor Magenta
$MyWorkItemCountCurrentYear | Add-Member -type NoteProperty -Name $($MyWorkItemType) -Value $($ProjectWorkItemsAPI.count)
$TotalOtherItemsCurrentYear -= $ProjectWorkItemsAPI.count
}
else
{
$MyWorkItemCountCurrentYear | Add-Member -type NoteProperty -Name $($MyWorkItemType) -Value 0
}
}
# ------- GET DETAILS FOR WORK ITEMS AND TYPES FOR PREVIOUS YEAR -------
write-host " - - - - - PREVIOUS YEAR WORK ITEMS - - - - - "
$PreviousyearStart = "1/1/$($PreviousYear)"
$PreviousyearEnd = "12/31/$($PreviousYear)"
$MyWorkItemCountPreviousYear = @()
$MyWiQlCommand = "Select [System.Id] FROM WorkItems WHERE [System.TeamProject]='$($myProject.Name)' AND [System.CreatedDate] >= '$($PreviousyearStart)' AND [System.CreatedDate] <= '$($PreviousyearEnd)'"
$body = @{ query = $MyWiQlCommand }
$bodyJson=@($body) | ConvertTo-Json
$uriAccount = $Organization + "_apis/wit/wiql?api-version=6.0"
$response = Invoke-RestMethod -Uri $uriAccount -Headers $AzureDevOpsAuthenicationHeader -Method Post -ContentType "application/json" -Body $bodyJson
$ProjectWorkItemsAPI= $response.workItems
write-host " - Work Items Total Previous Year:", $ProjectWorkItemsAPI.count -ForegroundColor Magenta
$TotalWorkItemsPerProjectPreviousYear = $ProjectWorkItemsAPI.Count
$TotalOtherItemsPreviousYear = $TotalWorkItemsPerProjectPreviousYear
#check for each Type of Work Item
$MyWorkItemCountPreviousYear = New-Object PSObject
foreach($MyWorkItemType in $StandardWorkItems)
{
$MyWiQlCommand = "Select [System.Id] FROM WorkItems WHERE [System.TeamProject]='$($myProject.Name)' AND [System.WorkItemType] = '$($MyWorkItemType)' AND [System.CreatedDate] >= '$($PreviousyearStart)' AND [System.CreatedDate] <= '$($PreviousyearEnd)'"
$body = @{ query = $MyWiQlCommand }
$bodyJson=@($body) | ConvertTo-Json
$uriAccount = $Organization + "_apis/wit/wiql?api-version=6.0"
$response = Invoke-RestMethod -Uri $uriAccount -Headers $AzureDevOpsAuthenicationHeader -Method Post -ContentType "application/json" -Body $bodyJson
$ProjectWorkItemsAPI= $response.workItems
if($ProjectWorkItemsAPI.count -gt 0)
{
write-host " >>> Previous Year WorkItems Type (API):", $MyWorkItemType, " - Total:", $ProjectWorkItemsAPI.count -ForegroundColor Magenta
$MyWorkItemCountPreviousYear | Add-Member -type NoteProperty -Name $($MyWorkItemType) -Value $($ProjectWorkItemsAPI.count)
$TotalOtherItemsPreviousYear -= $ProjectWorkItemsAPI.count
}
else
{
$MyWorkItemCountPreviousYear | Add-Member -type NoteProperty -Name $($MyWorkItemType) -Value 0
}
}
# ------- GET COUNTER FOR PIPELINES -------
write-host " - - - - - "
$uriAccount = $Organization + $myProject.Name + "/_apis/pipelines?api-version=6.0-preview.1"
$response = Invoke-RestMethod -Uri $uriAccount -Headers $AzureDevOpsAuthenicationHeader -Method Get -ContentType "application/json"
if($response.count -gt 0)
{
$PipelineCount= $response.count
}
write-host " - azDevOps BuildPipeline Total:", $PipelineCount -ForegroundColor Green
# ------- GET COUNTER FOR TEST PLANS -------
write-host " - - - - - "
$uriAccount = $Organization + $myProject.Name + "/_apis/testplan/plans?api-version=6.0-preview.1"
$response = Invoke-RestMethod -Uri $uriAccount -Headers $AzureDevOpsAuthenicationHeader -Method Get -ContentType "application/json"
if($response.value.count -gt 0)
{
$TestPlansCount= $response.value.count
}
write-host " - azDevOps Test Plans Total:", $TestPlansCount -ForegroundColor White
# ------- CONSOLIDATE ALL INFORMATION -------
$ProjectStatisticsPerOrganization += New-Object -TypeName PSObject -Property @{
DevOpsOrganizationName=$MyOrganization.OrganizationName
ProjectName=$myProject.Name
ProjectProcessModel=$ProjectProcessModelProperty
ProcessModelWIWithStates=$ProcessModelWIWithStates
ProjectLastModifiedDate=$myProject.lastUpdateTime
TotalRepositories=$ProjectRepositories.Count
TotalBranchesOveralRepo=$TotalBranchesPerProject
RepositoriesBranchesDetails=$RepoBranchDetails
TotalWorkItems=$TotalWorkItemsPerProject
WI_Bug=$MyWorkItemCount.Bug #To adapt if process in not Agile model
WI_Epic=$MyWorkItemCount.Epic #To adapt if process in not Agile model
WI_Feature=$MyWorkItemCount.Feature #To adapt if process in not Agile model
WI_Issue=$MyWorkItemCount.Issue #To adapt if process in not Agile model
WI_Task=$MyWorkItemCount.Task #To adapt if process in not Agile model
WI_TestCase=$MyWorkItemCount.'Test Case' #To adapt if process in not Agile model
WI_UserStory=$MyWorkItemCount.'User Story' #To adapt if process in not Agile model
WI_Other=$TotalOtherItems #To adapt if process in not Agile model
"TotalWorkItemsCurrentYear$($CurrentYear)"=$TotalWorkItemsPerProjectCurrentYear
"WI_BugCurrentYear_$($CurrentYear)"=$MyWorkItemCountCurrentYear.Bug #To adapt if process in not Agile model
"WI_EpicCurrentYear_$($CurrentYear)"=$MyWorkItemCountCurrentYear.Epic #To adapt if process in not Agile model
"WI_FeatureCurrentYear_$($CurrentYear)"=$MyWorkItemCountCurrentYear.Feature #To adapt if process in not Agile model
"WI_IssueCurrentYear_$($CurrentYear)"=$MyWorkItemCountCurrentYear.Issue #To adapt if process in not Agile model
"WI_TaskCurrentYear_$($CurrentYear)"=$MyWorkItemCountCurrentYear.Task #To adapt if process in not Agile model
"WI_TestCaseCurrentYear_$($CurrentYear)"=$MyWorkItemCountCurrentYear.'Test Case' #To adapt if process in not Agile model
"WI_UserStoryCurrentYear_$($CurrentYear)"=$MyWorkItemCountCurrentYear.'User Story' #To adapt if process in not Agile model
"WI_OtherCurrentYear_$($CurrentYear)"=$TotalOtherItemsCurrentYear #To adapt if process in not Agile model
"TotalWorkItemsPreviousYear_$($PreviousYear)"=$TotalWorkItemsPerProjectPreviousYear
"WI_BugPreviousYear_$($PreviousYear)"=$MyWorkItemCountPreviousYear.Bug #To adapt if process in not Agile model
"WI_EpicPreviousYear_$($PreviousYear)"=$MyWorkItemCountPreviousYear.Epic #To adapt if process in not Agile model
"WI_FeaturePreviousYear_$($PreviousYear)"=$MyWorkItemCountPreviousYear.Feature #To adapt if process in not Agile model
"WI_IssuePreviousYear_$($PreviousYear)"=$MyWorkItemCountPreviousYear.Issue #To adapt if process in not Agile model
"WI_TaskPreviousYear_$($PreviousYear)"=$MyWorkItemCountPreviousYear.Task #To adapt if process in not Agile model
"WI_TestCasePreviousYear_$($PreviousYear)"=$MyWorkItemCountPreviousYear.'Test Case' #To adapt if process in not Agile model
"WI_UserStoryPreviousYear_$($PreviousYear)"=$MyWorkItemCountPreviousYear.'User Story' #To adapt if process in not Agile model
"WI_OtherPreviousYear_$($PreviousYear)"=$TotalOtherItemsPreviousYear
BuildPipelineTotal=$PipelineCount
TestPlansTotal=$TestPlansCount
DataRefreshDate = $DataRefreshDate
}
write-host " ---------------------------------------------------------------------------------------------------- " -ForegroundColor Yellow
}
$ProjectStatisticsPerOrganization | ConvertTo-Json | Out-File -FilePath $ProjectStatisticsJSonFilePath -Encoding UTF8
$ProjectStatisticsOveralOrganization += $ProjectStatisticsPerOrganization
write-host " --------------------------------------------------------------------" -ForegroundColor White -BackgroundColor DarkYellow
}
$ProjectStatisticsOveralOrganization | ConvertTo-Json | Out-File -FilePath $ProjectStatisticsOveralOragnizationJSonFilePath -Encoding UTF8
After this execution, you can retrieve one JSON file per organization and an aggregated one to load it into your preferred reporting tool (Excel or Power BI).
You can also easily schedule this script to get the JSON file in Power BI automatically and have something following your delivery team.
The final goal of that module is to see and show the platform adoption and communicate around this.
References used to build that script:
- https://docs.microsoft.com/en-us/rest/api/azure/devops/?view=azure-devops-rest-6.1
- https://docs.microsoft.com/en-us/azure/devops/boards/work-items/workflow-and-state-categories
- https://docs.microsoft.com/en-us/azure/devops/boards/queries/wiql-syntax?view=azure-devops
- https://newsignature.com/articles/59293/
- https://dev.to/omiossec/getting-started-with-azure-devops-api-with-powershell-59nn
No RepliesBe the first to reply
