Ever wondered which user has what license activated and e.g. which specific feature is activated? Recently I was challenged to see who has the Exchange mailbox feature enabled and who not out of the active user base. Due to the huge user-base this would have taken hours to review manually. Using PowerShell for this, connecting to Office 365, exporting the data eventually to a CSV file and filtering it in Microsoft Excel made this way easier.
The challenge here is that Microsoft uses SKU’s – or licenses – that again can have various features enabled or disabled. Let’s say you have a E5 Plan (license) assigned to your user, you still can disabled various features within this plan, e.g. Microsoft Exchange.
If you take a look at the following website, you find a whole list of GUIDs / IDs of all those various features.
In case of the Microsoft Exchange Mailbox feature – we are talking about this GUID: efb87545-963c-4e0d-99df-69c6916d9eb0
Once I had identified the GUID the next step was to grab users from a specific on premise Active Directory OU and query them against Microsoft Azure on the Office 365 environment as for their assigned licenses/features. The results then are collected in a PowerShell object and eventually saved in a defined file name in a CSV format that you easily can filter in Excel afterwards.
Please keep in mind that you will need RSAT tools (PowerShell) and Azure/Office 365 connectivity, rights etc. in order for this to work.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | #script looks for all users in a base OU path #it checks for the license defined per user #it will write a CSV file in the path the script is in: O365UserMailboxList.csv #Base OU path - needs to be set $BaseOU = "DC=domain,DC=local" #Exchange mailbox license ID - see link for full list #https://docs.microsoft.com/en-us/azure/active-directory/users-groups-roles/licensing-service-plan-reference $ExchangeMailboxId = "efb87545-963c-4e0d-99df-69c6916d9eb0" #Script starts below - no additional changes should be needed - besides the last line for the output file name $tbl = New-Object System.Data.DataTable "Results" $col1 = New-Object System.Data.DataColumn UserPrincipalName $col2 = New-Object System.Data.DataColumn ADUserEnabled $col3 = New-Object System.Data.DataColumn ExchangeMailboxEnabled $col4 = New-Object System.Data.DataColumn ErrorMessage $tbl.Columns.Add($col1) $tbl.Columns.Add($col2) $tbl.Columns.Add($col3) $tbl.Columns.Add($col4) $ADUsersFromOU = Get-ADUser -SearchBAse $BaseOU -filter * Connect-AzureAD ForEach ($ADUser In $ADUsersFromOU) { $i += 1 $p = [math]::Round(($i/$ADusersFromOU.Count*100),0) Write-Host "..processing $i of"$ADusersFromOU.Count"- $p% - "$ADUser.UserPrincipalName $MailboxEnabled = $false $ErrorMessage = "" try { $UserLicenses = Get-AzureADUser -objectid $ADUser.UserPrincipalName | Select -ExpandProperty AssignedPlans | Where {$_.CapabilityStatus -eq "Enabled"} } catch { $ErrorMessage = $Error[0] #Write-Host "....Error: "$Error[0] } ForEach ($ULicense In $UserLicenses) { If ($ULicense.ServicePlanId -eq $ExchangeMailboxId) { $MailboxEnabled = $true break; } } $row = $tbl.NewRow() $row.UserPrincipalName = $ADUser.UserPrincipalName $row.ADUserEnabled = $ADUser.Enabled $row.ExchangeMailboxEnabled = $MailboxEnabled $row.ErrorMessage = $ErrorMessage $tbl.Rows.Add($row) } $tbl |ft #adjust this export-file name if you want to $tbl | Export-Csv -Path .\O365UserMailboxList.csv |
