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
Incomplete Usage Data for Final Day of the Month (Consumption Usage Details API)
I have a daily data collector which runs at around 2am to pull the previous days usage data. This has been working well for a few months although I have noticed that on the first of each month, the data pulled seems incomplete and the total cost for the final day of the month is around half of every other day. Is there any reason why data would be missing for the final day of the month? The specific endpoint I am hitting is: https://management.azure.com/subscriptions/{subscriptionID}/providers/Microsoft.Consumption/usageDetails?api-version=2021-10-01 Any assistance would be appriciated.
