User Profile
Fromelard
Iron Contributor
Joined 9 years ago
User Widgets
Recent Discussions
Re: PowerShell script to delete all Containers from a Storage Account
An updated version to use a loop, reducing the time to clean thousands of container, the request to get the set of Containers is slow when the number is too high, a loop with small value is more efficient and will clean the overall volume faster. For($Counter=0; $Counter -le 300; $Counter++) { Write-Host " LOOP N°:", $Counter $myContainers = Get-AzStorageContainer -Name $ContainerStartName -Context $ctx -MaxCount 100 if ($myContainers.count -gt 0) { foreach($mycontainer in $myContainers) { Write-Host " Deletion of Container:", $myContainers.Name Remove-AzStorageContainer -Name $mycontainer.Name -Force -Context $ctx } } }PowerShell Script to apply the retention Label in a full Site Collection
This script could help you to apply a Label in an entire Site Collection (All document libraries into all subsites). It's using an old command for "Set-PnPLabel" (I was not able to apply it with the last version of this "command"). You will need to have the PnP.PowerShell module version "1.12.0", this is why the start of the script is related to this component installation, you can remove it once your action is applied as expected. # ----------------------------------------------------------------------------------- #COMPONENT INSTALLATION #Get-InstalledModule -Name PnP.PowerShell #Find-Module -Name PnP.PowerShell -RequiredVersion 1.12.0 #Install-Module -Name PnP.PowerShell -RequiredVersion 1.12.0 -Force #Get-InstalledModule -Name PnP.PowerShell # ----------------------------------------------------------------------------------- [string]$SiteCollectionRelativeURL = "MySiteCollection" [string]$LabeltoApply = "Auto-delete 15 years" [string]$TeamSiteToUpdate = "https://MySPTenant.sharepoint.com/sites/$SiteCollectionRelativeURL/" [string]$ListName = "" # ----------------------------------------------------------------------------------- Import-Module PnP.PowerShell -DisableNameChecking Connect-PnPOnline -Url $TeamSiteToUpdate -UseWebLogin # ----------------------------------------------------------------------------------- #Get-PnPLabel $AllDocLibraries = Get-PnPList | Where-Object {$_.Hidden -eq $false} if ($AllDocLibraries.Count -gt 0) { foreach ($myDocLib in $AllDocLibraries) { $ListName = $myDocLib.Title $MyList = Get-PnPList -Identity $ListName $CurrentLabel = Get-PnPLabel -List $ListName -ValuesOnly if ($CurrentLabel.TagName -ne $LabeltoApply) { Write-Host " >>>> LABEL ", $LabeltoApply , "- NEED TO BE APPLIED on DocLib", $ListName -ForegroundColor Red Set-PnPLabel -List $ListName -Label $LabeltoApply -SyncToItems $false } else { Write-Host " >>>> LABEL ", $LabeltoApply , "- Yet Applied on DocLib", $ListName -ForegroundColor Green } #Get-PnPLabel -List $ListName -ValuesOnly } } $AllSubWebs = Get-PnPSubWeb -Recurse if($AllSubWebs.count -gt 0) { foreach ($mysubweb in $AllSubWebs) { Write-Host $mysubweb.Url -ForegroundColor Yellow Connect-PnPOnline -Url $mysubweb.Url -UseWebLogin $AllDocLibraries = Get-PnPList | Where-Object {$_.Hidden -eq $false} if ($AllDocLibraries.Count -gt 0) { foreach ($myDocLib in $AllDocLibraries) { $ListName = $myDocLib.Title $MyList = Get-PnPList -Identity $ListName $CurrentLabel = Get-PnPLabel -List $ListName -ValuesOnly if ($CurrentLabel.TagName -ne $LabeltoApply) { Write-Host " >>>> LABEL ", $LabeltoApply , "- NEED TO BE APPLIED on DocLib", $ListName -ForegroundColor Red Set-PnPLabel -List $ListName -Label $LabeltoApply -SyncToItems $false } else { Write-Host " >>>> LABEL ", $LabeltoApply , "- Yet Applied on DocLib", $ListName -ForegroundColor Green } } } } } This script was used to prevent the automatic cleanup implemented in the full tenant to documents older than 10 years. I used it to apply this on site collections containing more than 200 subsites and 10 doc lib mini per subsites. You can use it and adapt it as you need for your specific context. FabPowerShell script to delete all Containers from a Storage Account
After move the BootDiag settings out of the Custom Storage Account, the original Storage Account used for are still consuming space for nothing. This is part of the standard Clean Up stream need to be consider into the FinOps Plan. This script will help you to clean these Storage Accounts quickly and avoid cost paid for nothing. Connect-AzAccount #Your Subscription $MySubscriptionToClean = "MyGuid-MyGuid-MyGuid-MyGuid-MyGuid" $MyStorageAccountName = "MyStorageAccountForbootdiags" $MyStorageAccountKey = "MySAKeyWithAllCodeProvidedByYourStorageAccountSetting+MZ3cUvdQ==" $ContainerStartName = "bootdiag*" #Set subscription ID Set-AzContext -Subscription $MySubscriptionToClean Get-AzContext $ctx = New-AzStorageContext -StorageAccountName $MyStorageAccountName -StorageAccountKey $MyStorageAccountKey $myContainers = Get-AzStorageContainer -Name $ContainerStartName -Context $ctx -MaxCount 1000 foreach($mycontainer in $myContainers) { Remove-AzStorageContainer -Name $mycontainer.Name -Force -Context $ctx } I used this script to remove millions of BootDiag Containers from several Storage Accounts. You can also use it for any other cleanup use case if you need it. FabRe: Powershell Script to remove all Blobs from Storage account
Script updated with the deletion command: [string]$myConnectionString = "DefaultEndpointsProtocol=https;AccountName=YourStorageAccountName;AccountKey=YourKeyFromStorageAccountConnectionString;EndpointSuffix=core.windows.net" [string]$ContainerName = "YourBlobContainerName" [int]$blobCountAfter = 0 [int]$blobCountBefore = 0 $context = New-AzStorageContext -ConnectionString $myConnectionString $blobCountBefore = (Get-AzStorageBlob -Container $ContainerName -Context $context).Count Write-Host "Total number of blobs in the container Before deletion: $blobCount" -ForegroundColor Yellow Get-AzStorageBlob -Container $ContainerName -Context $context | ForEach-Object { $_ | Remove-AzStorageBlob -ICloudBlob $_.ICloudBlob -Context $context } $blobCountAfter = (Get-AzStorageBlob -Container $ContainerName -Context $context).Count Write-Host "Total number of blobs in the container After deletion : $blobCount" -ForegroundColor Green It's also possible to add the batch set using the maxcount Get-AzStorageBlob -Container $ContainerName -Context $context -MaxCount 10000 FabPowershell Script to remove all Blobs from Storage account
With large number of Blobs in Storage Account, the manual cleanup from the Portal is more complicated and time consuming, as it's per set of 10'000. This script is simple and and can be executed in background to clean all items from a defined Blob Container. You have to specify the Storage Account connection string and the blob container name. [string]$myConnectionString = "DefaultEndpointsProtocol=https;AccountName=YourStorageAccountName;AccountKey=YourKeyFromStorageAccountConnectionString;EndpointSuffix=core.windows.net" [string]$ContainerName = "YourBlobContainerName" [int]$blobCountAfter = 0 [int]$blobCountBefore = 0 $context = New-AzStorageContext -ConnectionString $myConnectionString $blobCountBefore = (Get-AzStorageBlob -Container $ContainerName -Context $context).Count Write-Host "Total number of blobs in the container Before deletion: $blobCount" -ForegroundColor Yellow Get-AzStorageBlob -Container $ContainerName -Context $context | ForEach-Object { $_ | Remove-AzureStorageBlob # or: Remove-AzureStorageBlob -ICloudBlob $_.ICloudBlob -Context $ctx } $blobCountAfter = (Get-AzStorageBlob -Container $ContainerName -Context $context).Count Write-Host "Total number of blobs in the container After deletion : $blobCount" -ForegroundColor Green It was used for large blob storage container with more than 5 millions of blob items. Sources: https://learn.microsoft.com/en-us/powershell/module/az.storage/new-azstoragecontext?view=azps-13.0.0#examples https://learn.microsoft.com/en-us/answers/questions/1637785/what-is-the-easiest-way-to-find-the-total-number-o https://stackoverflow.com/questions/57119087/powershell-remove-all-blobs-in-a-container FabRe: Azure - PowerShell script to change the Table Retention in Azure Log Analytics Workspaces
After this script I also used this solution to force the purge of the biggest tables - https://smsagent.blog/2022/01/06/purging-table-data-from-a-log-analytics-workspace/ I used the command because the API was not really working in my case: - https://learn.microsoft.com/en-us/powershell/module/az.operationalinsights/new-azoperationalinsightspurgeworkspace?view=azps-11.6.0&viewFallbackFrom=azps-11.4.0 The script executed was : ----- Import-module Az Connect-AzAccount # Az parameters $TenantId = "qqqqqq-zzzz-wwww-yyyy-xxxxxxxxxxxx" $Subscription = "MySubscriptionName" $subscriptionId = "uuuuu-zzzz-wwww-yyyy-xxxxxxxxxxxx" $ResourceGroupName = "MyResourceGroupName" $WorkspaceName = "MyloganalyticsWorkspaceName" # Purge parameters $table = "ContainerLog" $column = "TimeGenerated" $operator = "<" $values = "2024-01-30" $Response = New-AzOperationalInsightsPurgeWorkspace -ResourceGroupName $ResourceGroupName -WorkspaceName $WorkspaceName -Column $column -OperatorProperty $operator -Value $values -Table $table #-key "Key" $operationId = $Response.OperationId ---- After this you have to get the PurgeID task to execute this command and monitor the cleanup: $Params = @{ ResourceGroupName = $ResourceGroupName WorkspaceName = $WorkspaceName purgeId = "purge-xxxxx-qqqq-zzzz-oooo-kkkkkkkkk" #$operationId } Get-AzOperationalInsightsPurgeWorkspaceStatus @params ------- Fabrice RomelardAzure - PowerShell script to change the Table Retention in Azure Log Analytics Workspaces
With large scale implementation of Azure, the Log Analytics Workspace volume could increase and the default value for retention is quite long if you are not changing it. This PowerShell script will help you to reset the 2 retention values applied in Workspace Tables (Live and Total). I applied a selection criteria based in name as we are using a naming convention with status (prod, vs nonprod), you can anyway adapt this part with your context. #Install-Module -Name Az -Repository PSGallery -Force Import-module Az Connect-AzAccount $RetentionDays = 30 $TotalRetentionDays = 30 $AzureRetentionDays = 90 $AzureTotalRetentionDays = 90 $namecriteria = "nonprod" $All_Az_Subscriptions = Get-AzSubscription Foreach ($Az_Subscription in $All_Az_Subscriptions) { ################################################### #Set the context Write-Host "Working on subscription ""$($Az_Subscription.Name)""" Set-AzContext -SubscriptionObject $Az_Subscription | Out-Null $AllWorkspaces = Get-AzOperationalInsightsWorkspace foreach ($myWorkspace in $AllWorkspaces) { Write-Host " ---------------", $myWorkspace.Name ,"---------------- " -foregroundcolor "gray" if ($myWorkspace.Name -match $namecriteria) { Write-Host " >>> WORKSPACE TO APPLY RETENTION ADJUSTMENT:", $myWorkspace.Name -foregroundcolor "green" if ($myWorkspace.retentionInDays -gt $RetentionDays) { Write-Host " >>> APPLYING DEFAULT RETENTION PERIOD:", $RetentionDays -foregroundcolor "yellow" Set-AzOperationalInsightsWorkspace -ResourceGroupName $myWorkspace.ResourceGroupName -Name $myWorkspace.Name -RetentionInDays $RetentionDays } $GetAllTables = Get-AzOperationalInsightsTable -ResourceGroupName $myWorkspace.ResourceGroupName -WorkspaceName $myWorkspace.Name foreach ($MyTable in $GetAllTables) { if (($MyTable.Name -eq "AzureActivity") -or ($MyTable.Name -eq "Usage")) { if (($MyTable.RetentionInDays -gt $AzureRetentionDays) -or ($MyTable.TotalRetentionInDays -gt $AzureTotalRetentionDays)) { Write-Host " >>> APPLYING SPECIFIC RETENTION PERIOD:", $AzureRetentionDays, "- TABLE:", $MyTable.Name -foregroundcolor "yellow" Update-AzOperationalInsightsTable -ResourceGroupName $MyTable.ResourceGroupName -WorkspaceName $MyTable.WorkspaceName -TableName $MyTable.Name -RetentionInDays $AzureRetentionDays -TotalRetentionInDays $AzureTotalRetentionDays } else { Write-Host " >>> NO CHANGE FOR RETENTION PERIOD FOR TABLE:", $MyTable.Name -foregroundcolor "green" } } else { if (($MyTable.RetentionInDays -gt $RetentionDays) -or ($MyTable.TotalRetentionInDays -gt $RetentionDays)) { Write-Host " >>> APPLYING NEW RETENTION PERIOD:", $RetentionDays, "- TABLE:", $MyTable.Name -foregroundcolor "yellow" Update-AzOperationalInsightsTable -ResourceGroupName $MyTable.ResourceGroupName -WorkspaceName $MyTable.WorkspaceName -TableName $MyTable.Name -RetentionInDays $RetentionDays -TotalRetentionInDays $TotalRetentionDays } else { Write-Host " >>> NO CHANGE FOR RETENTION PERIOD FOR TABLE:", $MyTable.Name -foregroundcolor "green" } } } } else { Write-Host " >>> WORKSPACE NOT CONCERNED BY THIS CHANGE:", $myWorkspace.Name -foregroundcolor "green" } } } With this script, we reduced the Workspace cost for non prod drastically maintaining only the last 30 days live without any archive. The material used for this script is: https://learn.microsoft.com/en-us/azure/azure-monitor/logs/data-retention-archive?tabs=portal-3%2Cportal-1%2Cportal-2 https://learn.microsoft.com/en-us/powershell/module/az.operationalinsights/get-azoperationalinsightsworkspace?view=azps-11.6.0 https://learn.microsoft.com/en-us/powershell/module/az.operationalinsights/update-azoperationalinsightstable?view=azps-11.6.0 Fabrice RomelardAzure - PowerShell Script to delete a specific Tag for any resources in all your Subscriptions
A classical question after many months of usage and delegation to different admin is related to the TAG Cleanup. You can be faced to a large diversity of Tags created at one moment, but not useful and mainly not maintained. This small script will help you to execute this cleanup in all your subscriptions you are in charge. Import-module Az Connect-AzAccount [string]$TagName = "YourSpecificTagKey" $TagCount = 0 $All_Az_Subscriptions = Get-AzSubscription Foreach ($Az_Subscription in $All_Az_Subscriptions) { Write-Host " " Write-Host " --------------------------------------- " Write-Host "Working on subscription ""$($Az_Subscription.Name)""" -foregroundcolor "yellow" $TagCount = 0 Set-AzContext -SubscriptionObject $Az_Subscription | Out-Null $AllTaggedresources = Get-AzResource -TagName $TagName $TagCount = $AllTaggedresources.Count Write-Host " >> TAG "" $($TagName) "" found "" $($TagCount) "" times" -foregroundcolor "green" if($TagCount -gt 0) { $AllTaggedresources.ForEach{ if ( $_.tags.ContainsKey($TagName) ) { $_.tags.Remove($TagName) } $_ | Set-AzResource -Tags $_.tags -Force } } } This script was inspired by these pages: https://stackoverflow.com/questions/54162372/how-to-fix-this-error-in-azure-powershell-can-not-remove-tag-tag-value-becaus https://learn.microsoft.com/en-us/powershell/module/az.resources/set-azresource?view=azps-11.6.0 Fabrice RomelardAzure DevOps - How to modify files during a Build Pipeline execution based on PowerShell
Azure DevOps Build Pipeline can provide several option, but sometime we need to change a part of content extracted from Source code management (e.g. Git) before execute another task. In my case, this case is to comment one specific declaration (#include) placed in couple of JavaScript files. These lines are understood well by the application server using this code, but not by generic JavaScript parser used for example into SonarQube. So into the Pipeline process, it's only a PowerShell task could be used to do that like following. In Yaml mode: steps: - powershell: | Write-Host "------------------------------------------------------------" Write-Host "Get JS File content containing #include line", $(Agent.WorkFolder) Write-Host "------------------------------------------------------------" $AllJSFIleToManage = Get-ChildItem -Path $(Agent.WorkFolder)\*.js -Recurse -Force | Select-String -Pattern "#include " -AllMatches | Foreach {$_.Path} | Select-Object -Unique foreach ($MyFile in $AllJSFIleToManage) { Write-Host "JS File to change -", $MyFile (Get-Content $MyFile -Encoding UTF8) -replace '#include ', '//#include ' | Set-Content $MyFile Write-Host "JS File changed -", $MyFile Write-Host " -----------------------------------" } displayName: 'PowerShell Script remove specific #Include lines from JS files for Sonar' In Visual Editor mode: When the pipeline is running, it will get the source code from Git and change dynamically only the JS files replacing the blocks found with this "#include" by "//#include" to comment the concerned lines in JavaScript. The result of this execution in Pipeline log is like following: Into SonarQube the result is visible via the Source Code navigation option: You can adapt this code with your specific case and need, but PowerShell tasks are really powerful when you need to change something before a specific step only during the Pipeline execution. Fabrice RomelardAzure DevOps - How to monitor the files & folders placed into the Build Pipeline Workfolder
When we are building a new Pipeline into Azure DevOps, we don't have any visibility on the server itself and his content placed into work folder. In many situation, like debugging, that vision is really useful to find a file or a path to apply into another task (like Code Coverage report path). To do that, the simplest option is to place into the pipeline flow one PowerShell step with this simple execution. In Yaml mode: - powershell: | Write-Host "Show all folder content" Get-ChildItem -Path $(Agent.WorkFolder)\*.* -Recurse -Force errorActionPreference: continue displayName: 'PowerShell Script List folder structure' continueOnError: true In visual editor mode: At the next execution, you will have into the Pipeline execution log all folders and files placed into the agent workplace like: You can use the search button or View raw log option to look the file or folder you are looking for. That task could be disable in standard usage, and enable only when you need to debug. Fabrice RomelardAzure 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-59nnAzure DevOps - Send an email to each Project Administrator with Account listed into using PowerShell
We saw with a previous script how have a consolidated view of DevOps usage per organization: Azure DevOps - How to collect all accounts from Organization using PowerShell That solution associated with Power Bi report is giving a good overall view of your IT delivery team and licenses. But because the project management and user permission is under Project Manager umbrella, this new script is looking per organization and per project to send an email to Azure DevOps Project managers listing all accounts (with group, licenses, last connection, etc.) existing into their project. Based on that script, you can better review permission and license management accorded with project owners, and match with real needs. This is also a security basic reviewing the external access (Partner, Suppliers, Customers, etc.). Import-module AzureAD connect-azureAD #region -- Customized Script Settings (need to be reviewed) --- # ================== / Parameters to Adapt \ ================== [string]$GlobalPATForAllORganizations= "yyxxxxyyyyyxxxxxyyyy" [string]$JSonFolderPath = "C:\DEVOPS_PROJECTS" [string]$DomainNameEmail = "@yourdomain.com" # -- Email configuration ---- [string]$DevOpsAdmins = "AdminEmail@yourdomain.com" [string]$SMTPServer = "your.smtp.yourdomain.com" [string]$EmailCoreTitle = "[[yourproject]] - Review Azure DevOps Accounts and licenses associated" [string]$EmailPreCoreContent = "<DIV><P>Dear team,<BR><BR> We are reviewing all Azure DevOps accounts associated with your project <B>[[yourproject]]</B>.<BR> You can find an extract of all accounts listed for your project, and we need your control on each of those.<BR><BR> Please to check each account and confirm if we have to:<BR> <UL> <LI>Remove the account from your project</LI> <LI>Downgrade the license level, from Basic or Visual Studio (license paid) to Stakeholder (free of charge restricted to Work Items) - <a href='https://docs.microsoft.com/en-us/azure/devops/organizations/security/access-levels?view=azure-devops#supported-access-levels'>More details</a></LI> </UL> <BR> Send that reviewed list to this email address '<B>$($DevOpsAdmins)</B>' and the change will be applied.<BR><BR> ---- ACCOUNT INTO YOUR DEVOPS PROJECT: [[yourproject]] ------</P></DIV>" [string]$EmailPostCoreContent = "<DIV><P>-------------------------------------------------------------<BR><BR> Thanks for your help.<BR> Best Regards<BR><BR> DevOps Administrator</P></DIV>" # ================== \ Parameters to Adapt / ================== #endregion #region -- Internal Script Settings --- $OrganizationList = @() $ProjectAdministrators = @() [string]$PAT = "" [string]$OrganizationName = "" [string]$Organization = "" [string]$SecurityGroupsJSonFilePath = "" [string]$UserGroupJSonFilePath = "" [string]$GroupsListJSonFilePath = "" [string]$AccountValidInAzureAD = "" $DataRefreshDate = Get-Date -Format "yyyy-MM-dd" [int]$TotalProjectPerOrganization = 0 [int]$ProjectInProgress = 0 # -- Email configuration ---- [string]$TempEmailAddress = "" [string]$ProjectOwnerEmailAddresses = "" [string]$ListToPutInMail = "" [string]$EmailCoreTitleToUse = "" [string]$EmailPreCoreContentToUse = "" [string]$HTMLHeader = @" <style> TABLE {border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse;} TH {border-width: 1px; padding: 3px; border-style: solid; border-color: black; background-color: #FF6600;} TD {border-width: 1px; padding: 3px; border-style: solid; border-color: black;} </style> "@ #endregion #region -- Each Az DevOps Organization -- #YourOrganization1 $PAT = $GlobalPATForAllORganizations #https://dev.azure.com/YourOrganization1/_usersSettings/tokens $OrganizationName = "YourOrganization1" $OrganizationWithPAT = New-Object PSObject -property @{OrganizationName=$OrganizationName;OrganizationPAT=$PAT} $OrganizationList += $OrganizationWithPAT #YourOrganization2 $PAT = $GlobalPATForAllORganizations #https://dev.azure.com/YourOrganization2/_usersSettings/tokens $OrganizationName = "YourOrganization2" $OrganizationWithPAT = New-Object PSObject -property @{OrganizationName=$OrganizationName;OrganizationPAT=$PAT} $OrganizationList += $OrganizationWithPAT #YourOrganization3 $PAT = $GlobalPATForAllORganizations #https://dev.azure.com/YourOrganization3/_usersSettings/tokens $OrganizationName = "YourOrganization3" $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 $Organization = "https://dev.azure.com/$($MyOrganization.OrganizationName)/" $UserGroupJSonFilePath = "$JSonFolderPath\$($MyOrganization.OrganizationName)-UserGroups.json" $GroupsListJSonFilePath = "$JSonFolderPath\$($MyOrganization.OrganizationName)-AllProjects-List.json" $SecurityGroupsJSonFilePath = "$JSonFolderPath\$($MyOrganization.OrganizationName)-SecurityGroups-List.json" echo $($MyOrganization.OrganizationPAT) | az devops login --org $Organization az devops configure --defaults organization=$Organization #$allProjects = az devops project list --org $Organization --top 1 | 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 "YourProjectname" | 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 $GroupsListJSonFilePath -Encoding UTF8 #If you want to save a copy $TotalProjectPerOrganization = $allProjects.Count foreach($myProject in $allProjects) { $ProjectInProgress += 1 $ProjectAdministrators = @() $EmailPreCoreContentToUse = "" $EmailCoreTitleToUse = "" $ProjectOwnerEmailAddresses = "" $ListToPutInMail = "" $UserGroupsPerOrganizationAndProject = @() write-host " ---------------------------------------------------------------------------------------------------- " -ForegroundColor Yellow write-host " -- Project Name:", $myProject.Name, "[", $ProjectInProgress, " of ", $TotalProjectPerOrganization, "] --" -ForegroundColor Yellow #write-host " > Project Description:", $myProject.Description -ForegroundColor DarkYellow write-host " ---------------------------------------------------------------------------------------------------- " -ForegroundColor Yellow $ProjectSecurityGroups = az devops security group list --org $Organization --project $myProject.Name | ConvertFrom-Json #$ProjectSecurityGroups | Out-File -FilePath $SecurityGroupsJSonFilePath #-Encoding UTF8 #If you want to save a copy foreach($mySecurityGroup in $ProjectSecurityGroups.graphGroups) { write-host " - Security Group Name:", $mySecurityGroup.displayName, "- Descriptor:", $mySecurityGroup.descriptor -ForegroundColor Magenta #write-host " > Security Group Description:", $mySecurityGroup.Description -ForegroundColor DarkMagenta $AllGroupMembers = az devops security group membership list --id $mySecurityGroup.descriptor --org $Organization --relationship members | ConvertFrom-Json #write-host " JSON:", $AllGroupMembers #$AllGroupMembers | ConvertTo-Json | Out-File -FilePath $UserGroupJSonFilePath -Encoding UTF8 #If you want to save a copy [array]$groupMembers = ($AllGroupMembers | Get-Member -MemberType NoteProperty).Name foreach($MyUserInGroup in $groupMembers) { if($AllGroupMembers.$MyUserInGroup.mailAddress -ne $null) { $AccountValidInAzureAD = "" write-host " ==> User Name:", $AllGroupMembers.$MyUserInGroup.displayName, "- Email:", $AllGroupMembers.$MyUserInGroup.mailAddress -ForegroundColor Green if($AllGroupMembers.$MyUserInGroup.mailAddress.endswith($DomainNameEmail)) { $TempEmailAddress = $AllGroupMembers.$MyUserInGroup.mailAddress -replace "'", "''" $MyAzureADUser = Get-AzureADUser -Filter "userPrincipalName eq '$($TempEmailAddress)'" if($MyAzureADUser) { if($MyAzureADUser.AccountEnabled) { $AccountValidInAzureAD = "ACCOUNT VALID IN AZURE ACTIVE DIRECTORY" } else { $AccountValidInAzureAD = "ACCOUNT DISABLE IN AZURE ACTIVE DIRECTORY" } } else { $AccountValidInAzureAD = "ACCOUNT NOT EXIST IN AZURE ACTIVE DIRECTORY" } } else { $AccountValidInAzureAD = "ACCOUNT NOT INTERNAL EMAIL ADDRESS" } $MyUserDetails = az devops user show --user $AllGroupMembers.$MyUserInGroup.mailAddress --org $Organization | ConvertFrom-Json #write-host " JSON:", $MyUserDetails $UserGroupsPerOrganizationAndProject += New-Object -TypeName PSObject -Property @{ UserName=$AllGroupMembers.$MyUserInGroup.displayName mailAddress=$AllGroupMembers.$MyUserInGroup.mailAddress AccountStatusInAzureAD = $AccountValidInAzureAD LicenseType=$MyUserDetails.accessLevel.licenseDisplayName dateCreated=$($MyUserDetails.dateCreated).Substring(0, 10) lastAccessedDate=$($MyUserDetails.lastAccessedDate).Substring(0, 10) ProjectName= $myProject.Name GroupName=$mySecurityGroup.displayName DataRefreshDate = $DataRefreshDate } } } } $EmailCoreTitleToUse = $EmailCoreTitle.replace("[[yourproject]]", $myProject.Name) $ProjectAdministrators = $UserGroupsPerOrganizationAndProject | where { $_.GroupName -eq "Project Administrators" } | Select mailAddress if($ProjectAdministrators.count -gt 0) { foreach ($myProjectAdminEMail in $ProjectAdministrators) { if ($myProjectAdminEMail.mailAddress.endswith($DomainNameEmail)) { $ProjectOwnerEmailAddresses += $myProjectAdminEMail.MailAddress $ProjectOwnerEmailAddresses += ";" } } if($ProjectOwnerEmailAddresses -eq "") { $ProjectOwnerEmailAddresses = $DevOpsAdmins $EmailCoreTitleToUse = "NO INTERNAL PROJECT ADMIN - " + $EmailCoreTitleToUse } } else { $ProjectOwnerEmailAddresses = $DevOpsAdmins $EmailCoreTitleToUse = "NO PROJECT ADMIN - " + $EmailCoreTitleToUse } $UserGroupsPerOrganizationAndProject | ConvertTo-Json #write-host " JSON:", $UserGroupsPerOrganizationAndProject $EmailPreCoreContentToUse = $EmailPreCoreContent.replace("[[yourproject]]", $myProject.Name) #$ListToPutInMail = $UserGroupsPerOrganizationAndProject | Format-Table UserName, mailAddress, AccountStatusInAzureAD, LicenseType, GroupName, dateCreated, lastAccessedDate | Out-String $ListToPutInMail = $UserGroupsPerOrganizationAndProject | ConvertTo-Html -Property UserName, mailAddress, AccountStatusInAzureAD, LicenseType, GroupName, dateCreated, lastAccessedDate -PreContent $EmailPreCoreContentToUse -PostContent $EmailPostCoreContent -Head $HTMLHeader | Out-String #write-host " HTML Table:", $ListToPutInMail #write-host " Project Admins:", $ProjectAdministrators $ProjectOwnerEmailAddresses = $ProjectOwnerEmailAddresses.Trim() if($ProjectOwnerEmailAddresses.EndsWith(";")) { write-host " Remove last char from:", $ProjectOwnerEmailAddresses $ProjectOwnerEmailAddresses = $ProjectOwnerEmailAddresses.Substring(0, $ProjectOwnerEmailAddresses.Length-1) write-host " Last char removed from:", $ProjectOwnerEmailAddresses } write-host " Project Admin Emails:", $ProjectOwnerEmailAddresses write-host " *> Email Title:", $EmailCoreTitleToUse Send-MailMessage -To $ProjectOwnerEmailAddresses.split(';') -From $DevOpsAdmins -Cc $DevOpsAdmins -Subject $EmailCoreTitleToUse -Body $ListToPutInMail -BodyAsHtml -SmtpServer $SMTPServer write-host " ---------------------------------------------------------------------------------------------------- " -ForegroundColor Yellow } write-host " --------------------------------------------------------------------" -ForegroundColor White -BackgroundColor DarkYellow } You have a restricted list of settings to adapt and run the script. In any case, that part need to be integrated into your FinOps process to optimize your license costs as you really need and use. Fabrice Romelard References used to build that script: https://docs.microsoft.com/en-us/cli/azure/ext/azure-devops/devops/project?view=azure-cli-latest https://www.colinsalmcorner.com/az-devops-like-a-boss/ https://tenbulls.co.uk/2020/01/06/using-azure-cli-to-query-azure-devops/Azure DevOps - How to collect all accounts from Organization using PowerShell
When you are managing an Azure DevOps instance, you can have one or many Organizations (depending of your isolation and distribution requirements). Each organization will have a specific Account list (with license associated), but there is no automatic cleanup to remove disable accounts or consolidate all account in one view, and the built-in CSV Export module is limited in usage. This PowerShell Script will connect to all organization you specified in setting (using a dedicated Token you have to use or dedicated per Organization) to get all declared accounts, and loop for each account the Project part of. If No project assigned, you will have "NO PROJECT" as value. Import-module AzureAD connect-azureAD #region -- Customized Script Settings (need to be reviewed) --- # ================== / Parameters to Adapt \ ================== [string]$SharePointTeamSite = "https://tenant.sharepoint.com/sites/YourCollection/" [string]$DocumentLibrary = "YourDocLib" [string]$GlobalPATForAllORganizations= "xxxyyyyxxxxxxyyyyy" #If you have a global PAT [string]$JSonFolderPath = "C:\JSONDEVOPS" [string]$DomainNameEmail = "@emaildomain.com" # ================== \ Parameters to Adapt / ================== #endregion #region -- Internal Script Settings --- $OrganizationList = @() [string]$PAT = "" [string]$OrganizationName = "" [string]$Organization = "" [string]$UserGroupJSonFilePath = "" [string]$UserListJSonFilePath = "" [string]$AccountValidInAzureAD = "" $DataRefreshDate = Get-Date -Format "yyyy-MM-dd" [string]$TempEmailAddress = "" $UserGroupsOveralOrganization = @() $UserGroupOveralOragnizationJSonFilePath = "$JSonFolderPath\DevOps-UserGroups.json" #endregion #region -- Each Az DevOps Organization -- #YourOrganization1 $PAT = $GlobalPATForAllORganizations #If specific https://dev.azure.com/YourOrganization1/_usersSettings/tokens $OrganizationName = "YourOrganization1" $OrganizationWithPAT = New-Object PSObject -property @{OrganizationName=$OrganizationName;OrganizationPAT=$PAT} $OrganizationList += $OrganizationWithPAT #YourOrganization2 $PAT = $GlobalPATForAllORganizations #If specific https://dev.azure.com/YourOrganization2/_usersSettings/tokens $OrganizationName = "YourOrganization2" $OrganizationWithPAT = New-Object PSObject -property @{OrganizationName=$OrganizationName;OrganizationPAT=$PAT} $OrganizationList += $OrganizationWithPAT #YourOrganization3 $PAT = $GlobalPATForAllORganizations #If specific https://dev.azure.com/YourOrganization3/_usersSettings/tokens $OrganizationName = "YourOrganization3" $OrganizationWithPAT = New-Object PSObject -property @{OrganizationName=$OrganizationName;OrganizationPAT=$PAT} $OrganizationList += $OrganizationWithPAT #endregion foreach($MyOrganization in $OrganizationList) { $Organization = "https://dev.azure.com/$($MyOrganization.OrganizationName)/" $UserGroupsPerOrganization = @() $UserGroupJSonFilePath = "$JSonFolderPath\$($MyOrganization.OrganizationName)-UserGroups.json" $UserListJSonFilePath = "$JSonFolderPath\$($MyOrganization.OrganizationName)-UsersList.json" echo $($MyOrganization.OrganizationPAT) | az devops login --org $Organization az devops configure --defaults organization=$Organization $allUsers = az devops user list --org $Organization --top 10000 | ConvertFrom-Json #write-host "All Users:", $allUsers $allUsers | ConvertTo-Json | Out-File -FilePath $UserListJSonFilePath foreach($au in $allUsers.members) { if($au.user.mailAddress.endswith($DomainNameEmail)) { $TempEmailAddress = $au.user.mailAddress -replace "'", "''" $MyAzureADUser = Get-AzureADUser -Filter "userPrincipalName eq '$($TempEmailAddress)'" if($MyAzureADUser) { if($MyAzureADUser.AccountEnabled) { $AccountValidInAzureAD = "ACCOUNT VALID IN AZURE AD" } else { $AccountValidInAzureAD = "ACCOUNT DISABLE IN AZURE AD" } } else { $AccountValidInAzureAD = "ACCOUNT NOT EXIST IN AZURE AD" } } else { $AccountValidInAzureAD = "ACCOUNT NOT INTERNAL EMAIL" } $activeUserGroups = az devops security group membership list --id $au.user.principalName --org $Organization --relationship memberof | ConvertFrom-Json [array]$groups = ($activeUserGroups | Get-Member -MemberType NoteProperty).Name if ($groups.count -gt 0) { foreach ($aug in $groups) { $UserGroupsPerOrganization += New-Object -TypeName PSObject -Property @{ DevOpsOrganizationName=$MyOrganization.OrganizationName principalName=$au.user.principalName displayName=$au.user.displayName mailAddress=$au.user.mailAddress UserID=$au.id AccountValidInAzureAD = $AccountValidInAzureAD LicenseType=$au.accessLevel.licenseDisplayName dateCreated=$au.dateCreated lastAccessedDate=$au.lastAccessedDate ProjectName= $($activeUserGroups.$aug.principalName.Split("\\"))[0] GroupName=$activeUserGroups.$aug.principalName DataRefreshDate = $DataRefreshDate } } } else { $UserGroupsPerOrganization += New-Object -TypeName PSObject -Property @{ DevOpsOrganizationName=$MyOrganization.OrganizationName principalName=$au.user.principalName displayName=$au.user.displayName mailAddress=$au.user.mailAddress UserID=$au.id AccountValidInAzureAD = $AccountValidInAzureAD LicenseType=$au.accessLevel.licenseDisplayName dateCreated=$au.dateCreated lastAccessedDate=$au.lastAccessedDate ProjectName= "[NO PROJECT]" GroupName="[NO PROJECT GROUP]" DataRefreshDate = $DataRefreshDate } } } $UserGroupsPerOrganization | ConvertTo-Json | Out-File -FilePath $UserGroupJSonFilePath $UserGroupsOveralOrganization += $UserGroupsPerOrganization } $UserGroupsOveralOrganization | ConvertTo-Json | Out-File -FilePath $UserGroupOveralOragnizationJSonFilePath Import-Module PnP.PowerShell -DisableNameChecking Connect-PnPOnline -Url $SharePointTeamSite -UseWebLogin $Files = Get-ChildItem -Path $JSonFolderPath -Force -Recurse #Upload All files from the directory ForEach ($File in $Files) { Write-host "Uploading $($File.Directory)\$($File.Name)" #upload a file to sharepoint online using powershell - Upload File and Set Metadata Add-PnPFile -Path "$($File.Directory)\$($File.Name)" -Folder $DocumentLibrary -Values @{"Title" = $($File.Name)} } The result will be a JSON file (saved in SharePoint), you can easily load in Power Bi to build your dedicated Report helping you to adapt Develop license with real usage like: Remove all accounts Disable in Azure AD Downgrade all account license from Basic+ to Stakeholder if no connection since more than 1 year Remove all accounts without any project Any other rules part of your Governance model That could also be adapted to use an Azure Engine to cleanup automatically your Organization. In any case, that part need to be integrated into your FinOps process to optimize your license costs as you really need and use. Fabrice Romelard References used to build that script: https://docs.microsoft.com/en-us/cli/azure/install-azure-cli-windows?view=azure-cli-latest&tabs=azure-powershell https://vinijmoura.medium.com/how-to-list-all-users-and-group-permissions-on-azure-devops-using-azure-devops-cli-54f73a20a4c7 Update: Script cleaned to be easily modified and usedPowerShell script to export Audit log search Data based on userID filter
Office 365 Audit Log platform is helping you to monitor and control activities on your tenant. In some case, it's necessary to export some user activity to detect some problematic usage. FROM WEB ADMIN SITE It's possible to do that export from the dedicated Admin site "Security & Compliance": https://protection.office.com/?rfr=AdminCenter#/unifiedauditlog You can select certain filter options to focus on your question based on: Activity type Start & End DateTime UsersID (email or O365 login) File, folder, url or site this filterset will be used: to execute the search and export the resultset (in CSV format) to create an alert You can find a lot of details related to that Audit Log usage: https://docs.microsoft.com/en-us/office365/securitycompliance/search-the-audit-log-in-security-and-compliance#search-the-audit-log But some important restrictions need to be accepted before work with that solution: Number of lines (or Events) is limited to 50’000 max Number of characters exported to the AuditData details is restricted to the first 3’060 chars FROM POWERSHELL Based on many other articles and blogposts, I wrote a dedicated PowerShell script like a toolkit you can use to connect directly Office 365 Audit Log system and send the filters you need to get the result set in memory. Based on that results, the script will directly extract the AuditData field and convert it into a CSV file with many fields (extracted form the JSON Format) The PowerShell command used is: Search-UnifiedAuditLog - https://docs.microsoft.com/en-us/powershell/module/exchange/policy-and-compliance-audit/search-unifiedauditlog?view=exchange-ps Function Split-O365AuditLogs-FromO365 () { #Get the content to process Write-host " -----------------------------------------" -ForegroundColor Green [string]$username = "YourAdminAccount@yourtenant.onmicrosoft.com" [string]$PwdTXTPath = "C:\SECUREDPWD\ExportedPWD-$($username).txt" $secureStringPwd = ConvertTo-SecureString -string (Get-Content $PwdTXTPath) $UserCredential = New-Object System.Management.Automation.PSCredential $username, $secureStringPwd #This will prompt the user for credential # $UserCredential = Get-Credential $Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-LiveID/ -Credential $UserCredential -Authentication Basic -AllowRedirection Import-PSSession $Session [DateTime]$startDate = "01/01/2019 00:00" #Format: mm/dd/yyyy hh:MM [DateTime]$endDate = "01/11/2019 23:59" #Format: mm/dd/yyyy hh:MM $SpecifiedUserIDs = "Youremailtoscan@yourtenant.com", "Youremailtoscan2@yourtenant.com" #syntax: "<value1>","<value2>",..."<valueX>". $scriptStart=(get-date) $sessionName = (get-date -Format 'u')+'o365auditlog' # Reset user audit accumulator $aggregateResults = @() $i = 0 # Loop counter Do { Write-host " >> Audit Request Details: StartDate=", $startDate, "- EndDate=", $endDate, "SpecifiedUserIDs=", $SpecifiedUserIDs $currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000 -UserIds $SpecifiedUserIDs if ($currentResults.Count -gt 0) { Write-Host (" Finished {3} search #{1}, {2} records: {0} min" -f [math]::Round((New-TimeSpan -Start $scriptStart).TotalMinutes,4), $i, $currentResults.Count, $user.UserPrincipalName ) # Accumulate the data $aggregateResults += $currentResults # No need to do another query if the # recs returned <1k - should save around 5-10 sec per user if ($currentResults.Count -lt 1000) { $currentResults = @() } else { $i++ } } } Until ($currentResults.Count -eq 0) # --- End of Session Search Loop --- # [int]$IntemIndex = 1 $data=@() foreach ($line in $aggregateResults) { Write-host " ItemIndex:", $IntemIndex, "- Creation Date:", $line.CreationDate, "- UserIds:", $line.UserIds, "- Operations:", $line.Operations Write-host " > AuditData:", $line.AuditData $datum = New-Object -TypeName PSObject $Converteddata = convertfrom-json $line.AuditData $datum | Add-Member -MemberType NoteProperty -Name Id -Value $Converteddata.Id $datum | Add-Member -MemberType NoteProperty -Name CreationTimeUTC -Value $Converteddata.CreationTime $datum | Add-Member -MemberType NoteProperty -Name CreationTime -Value $line.CreationDate $datum | Add-Member -MemberType NoteProperty -Name Operation -Value $Converteddata.Operation $datum | Add-Member -MemberType NoteProperty -Name OrganizationId -Value $Converteddata.OrganizationId $datum | Add-Member -MemberType NoteProperty -Name RecordType -Value $Converteddata.RecordType $datum | Add-Member -MemberType NoteProperty -Name ResultStatus -Value $Converteddata.ResultStatus $datum | Add-Member -MemberType NoteProperty -Name UserKey -Value $Converteddata.UserKey $datum | Add-Member -MemberType NoteProperty -Name UserType -Value $Converteddata.UserType $datum | Add-Member -MemberType NoteProperty -Name Version -Value $Converteddata.Version $datum | Add-Member -MemberType NoteProperty -Name Workload -Value $Converteddata.Workload $datum | Add-Member -MemberType NoteProperty -Name UserId -Value $Converteddata.UserId $datum | Add-Member -MemberType NoteProperty -Name ClientIPAddress -Value $Converteddata.ClientIPAddress $datum | Add-Member -MemberType NoteProperty -Name ClientInfoString -Value $Converteddata.ClientInfoString $datum | Add-Member -MemberType NoteProperty -Name ClientProcessName -Value $Converteddata.ClientProcessName $datum | Add-Member -MemberType NoteProperty -Name ClientVersion -Value $Converteddata.ClientVersion $datum | Add-Member -MemberType NoteProperty -Name ExternalAccess -Value $Converteddata.ExternalAccess $datum | Add-Member -MemberType NoteProperty -Name InternalLogonType -Value $Converteddata.InternalLogonType $datum | Add-Member -MemberType NoteProperty -Name LogonType -Value $Converteddata.LogonType $datum | Add-Member -MemberType NoteProperty -Name LogonUserSid -Value $Converteddata.LogonUserSid $datum | Add-Member -MemberType NoteProperty -Name MailboxGuid -Value $Converteddata.MailboxGuid $datum | Add-Member -MemberType NoteProperty -Name MailboxOwnerSid -Value $Converteddata.MailboxOwnerSid $datum | Add-Member -MemberType NoteProperty -Name MailboxOwnerUPN -Value $Converteddata.MailboxOwnerUPN $datum | Add-Member -MemberType NoteProperty -Name OrganizationName -Value $Converteddata.OrganizationName $datum | Add-Member -MemberType NoteProperty -Name OriginatingServer -Value $Converteddata.OriginatingServer $datum | Add-Member -MemberType NoteProperty -Name SessionId -Value $Converteddata.SessionId $data += $datum $IntemIndex += 1 } $datestring = (get-date).ToString("yyyyMMdd-hhmm") $fileName = ("C:\AuditLogs\CSVExport\" + $datestring + ".csv") Write-Host (" >>> writing to file {0}" -f $fileName) $data | Export-csv $fileName -NoTypeInformation Remove-PSSession $Session } Split-O365AuditLogs-FromO365 You can use that script as you want and adapt it with your own needs Attention: AuditData limitation stay into the PowerShell option, but it's a MS Support Bug known as detailed into that following posts, I hope that will be fixed soon: https://techcommunity.microsoft.com/t5/Office-365/Incomplete-data-from-Search-UnifiedAuditLog-cmdlet-for-AzureAD/td-p/240805 https://office365itpros.com/2018/10/22/longer-retention-office365-auditdata/ Additional links: https://angryanalyticsblog.azurewebsites.net/index.php/2018/02/16/power-bi-audit-log-analytics-solution/ https://docs.microsoft.com/en-us/office365/securitycompliance/detailed-properties-in-the-office-365-audit-log https://docs.microsoft.com/en-us/office365/securitycompliance/detailed-properties-in-the-office-365-audit-log https://docs.microsoft.com/en-us/office365/securitycompliance/search-the-audit-log https://www.youtube.com/watch?v=KUyE59E3EFY https://blogs.msdn.microsoft.com/tehnoonr/2018/01/26/retrieving-office-365-audit-data-using-powershell/ https://office365itpros.com/2018/10/22/longer-retention-office365-auditdata/ https://docs.microsoft.com/en-us/powershell/module/exchange/policy-and-compliance-audit/search-unifiedauditlog?view=exchange-ps https://www.sherweb.com/blog/activity-reports-audit-logs-office-365/ http://alexbrassington.com/2016/03/03/splitting-office-365-audit-logs/ https://www.powershellgallery.com/packages/O365_Unified_Auditlog_parser/1.1/Content/O365_Unified_Auditlog_parser.ps1 Fabrice Romelard French version: http://blogs.developpeur.org/fabrice69/archive/2019/01/28/office-365-script-powershell-pour-extraire-les-audit-log-bas-s-sur-des-filtres-fournis.aspxRe: TF51005: The query references a field that does not exist. The error is caused by «[Custom.Reflected
Dear Han, The solution for this issue is explained here: - https://stackoverflow.com/questions/61770963/how-do-i-resolve-tf51005-for-reflectedworkitemid-when-migrating-azure-devops-dat You need to update each of item type you expect to migrate in destination project with a new dedicated field "ReflectedWorkItemId" The best option is to create a new Process Model copying the original once and add this field into the Work Item Type concerned by this loading. FabOffice 365 - Simple PowerShell script to refresh members of an Exchange Distribution Group
When you are in charge to support end user into their daily activities, the Distribution Group is a basic option. In many case, the number of members can change regularly and do that with Outlook screen is not sustainable. This following script will give you the basic option to clear the team members and add all members referenced with their email addresses. It could be adapted easily to use another source (CSV, TXT, ...) as list of emails. [string]$MyDistributionGroupToClean = "MyDistributionGroupName" $AllEmailsToAdd ="email address removed for privacy reasons","email address removed for privacy reasons","email address removed for privacy reasons","email address removed for privacy reasons","email address removed for privacy reasons","email address removed for privacy reasons" # ------------------------------------------------------- #Install-Module -Name ExchangeOnlineManagement -Force Import-Module ExchangeOnlineManagement -DisableNameChecking Connect-ExchangeOnline # ------------------------------------------------------- $CurrentDLGroupMembers = Get-DistributionGroupMember -Identity $MyDistributionGroupToClean -ResultSize Unlimited $CurrentDLGroupMembers #visual control foreach($mymember in $CurrentDLGroupMembers) { $MemberEmail=$mymember.PrimarySMTPAddress if($MemberEmail -eq "") { Write-Host " -> Member:", $mymember.Name, " - ", $mymember.PrimarySMTPAddress, "- NOT REMOVED - NO EMAIL" -ForegroundColor Red } else { Remove-DistributionGroupMember -Identity $MyDistributionGroupToClean -Member $mymember.PrimarySMTPAddress -Confirm:$false Write-Host " -> Member:", $mymember.Name, " - ", $mymember.PrimarySMTPAddress, " - REMOVED" -ForegroundColor Green } } $CurrentDLGroupMembers = Get-DistributionGroupMember -Identity $MyDistributionGroupToClean -ResultSize Unlimited $CurrentDLGroupMembers #visual control foreach($NewAccount in $AllEmailsToAdd) { Add-DistributionGroupMember -Identity $MyDistributionGroupToClean -Member $NewAccount -Confirm:$false Write-Host " -> Member:", $NewAccount, " - ADDED" -ForegroundColor Green } $CurrentDLGroupMembers = Get-DistributionGroupMember -Identity $MyDistributionGroupToClean -ResultSize Unlimited $CurrentDLGroupMembers #visual control This solution is simple to use or delegate to a support team. Fabrice Romelard Sources Used: https://www.powershellgallery.com/packages/ExchangeOnlineManagement/3.0.0 https://learn.microsoft.com/en-us/powershell/module/exchange/add-distributiongroupmember?view=exchange-ps https://learn.microsoft.com/en-us/powershell/module/exchange/update-distributiongroupmember?view=exchange-ps https://o365reports.com/2019/05/23/export-office-365-distribution-group-members-csv/ https://m365scripts.com/exchange-online/how-to-add-bulk-users-to-distribution-group-in-office-365-via-powershell/ https://techcommunity.microsoft.com/t5/office-365/trying-to-add-multiple-users-to-distribution-group-and-getting/m-p/910637SharePoint - PowerShell script to Set Hub Association in SharePoint Online
Dear all, Based on this script published: SharePoint Online: Set Hub Site Association using PowerShell - SharePoint Diary And the PNP resources provided for Hub Site: Get-PnPHubSite | PnP PowerShell Remove-PnPHubSiteAssociation | PnP PowerShell I created this script simply use the list of provide site URL to check if there is yet a Hub configured, if not it's assigning the provide Hub Site URL address as connected. #Config Variables $TenantSiteUrl = "https://yourtenant-admin.sharepoint.com" $HubSiteURL = "https://yourtenant.sharepoint.com/sites/YourHubsite" #Connect to PnP Online Connect-PnPOnline -Url $TenantSiteUrl -UseWebLogin Get-PnPHubSite -Identity $HubSiteURL $SitesConnectedToHub = Get-PnPHubSiteChild -Identity $HubSiteURL $Sites = "https://yourtenant.sharepoint.com/sites/TeamSite1","https://yourtenant.sharepoint.com/sites/TeamSite2","https://yourtenant.sharepoint.com/sites/TeamSite3" #Associate each Site collection with HubSite ForEach ($Site in $Sites) { #Write-host " ----------------------------------------------------------" if($SitesConnectedToHub.Contains($Site)) { Write-host " == $($Site) Yet connected to Hub Site [$($HubSiteURL)] ==" -ForegroundColor Yellow } else { Write-host " == $($Site) Need to be connected to Hub Site [$($HubSiteURL)] ==" -ForegroundColor Red Add-PnPHubSiteAssociation -Site $Site -HubSite $HubSiteURL Write-host " ==> $($Site) Connected to HUB Site !" -ForegroundColor Green } } You only have to configure the correct URLs and run it with SharePoint Admin permission set on your tenant. Fabrice RomelardSharePoint - PowerShell script to force Modern Theme for all existing subsites in a Site Collection
In SharePoint sites, we can a lot of subsites and assign the Modern Theme for each could be long. To simplify that action, I created the following PowerShell script to simplify this update. You will need an access as O365 SPO tenant admin to execute the command "Set-PnPWebTheme" as explained into the official documentation: Set-PnPWebTheme #Register-PSRepository -Default #> Required to force PowerShell module reconfig when install-module command is failing #Install-Module -Name PnP.PowerShell -Force #> Required to install PNP.PowerShell module on your computer [string]$TeamSiteToUpdate = "https://YourTenant.sharepoint.com/sites/YourTeamsite/" [string]$TenantRootURL = "https://YourTenant.sharepoint.com" [string]$SubsiteFullURL = "" [string]$ThemeName = "Your Theme Name" Import-Module PnP.PowerShell -DisableNameChecking #Connect the root teamsite to get the list of SubSites Connect-PnPOnline -Url $TeamSiteToUpdate -UseWebLogin $web = Get-PnpWeb $AllSubWebs = Get-PnPSubWeb -Recurse Disconnect-PnPOnline #Connect the SharePoint Tenant to force the defined Theme into each of the subsites and root site Connect-PnPOnline -Url https://YourTenant-admin.sharepoint.com -UseWebLogin Set-PnPWebTheme -Theme $ThemeName –WebUrl $TeamSiteToUpdate foreach($MySubsite in $AllSubWebs) { $SubsiteFullURL = $TenantRootURL + $MySubsite.ServerRelativeUrl Write-Host "Subsite Name:", $MySubsite.Title, " - Full URL:", $SubsiteFullURL ,"(RelativeURL:", $MySubsite.ServerRelativeUrl, ")" Set-PnPWebTheme -Theme $ThemeName –WebUrl $SubsiteFullURL Write-Host " >>> Theme Applied - ", $ThemeName -ForegroundColor Green } Disconnect-PnPOnline When your script is adapted and executed, you will find the root site and subsites with the correct Theme. You can also use this script to adapt the case with different Theme for subsites versus rootsite Fabrice Romelard
