Quick and Easy Office 365 License Assignment Report

Leverage Get-AzureADSubscribedSku to Find Office 365 E3 Accounts and Create Office 365 Licensing Report

I love receiving suggestions from readers. After posting my note about Microsoft’s epic failure to communicate the real facts about mailbox auditing by default, Ofir Doron pointed out a good way to create a list of Office 365 E3 accounts that could then be used to enable mailbox auditing. You still need to take care of shared mailboxes, so I’ve updated the script to process both user and shared mailboxes.

The basis of the suggestion is to use the GUID of the Office 365 E3 license to identify accounts to check. The GUID is found by running the Get-AzureADSubscribedSku cmdlet from the Azure Active Directory module to return details of the SKUs (stock control units) available in the tenant. The EnterprisePack SKU in the list is Office 365, so the SKU identifier we need to use is 6fd2c87f-b296-42f0-b197-1e91e994b900. This value seems to be the same for all tenants.

Get-AzureADSubscribedSku | Select Sku*, ConsumedUnits

SkuId                                SkuPartNumber                ConsumedUnits
-----                                -------------                -------------
1f2f344a-700d-42c9-9427-5cea1d5d7ba6 STREAM                                   6
b05e124f-c7cc-45a0-a6aa-8cf78c946968 EMSPREMIUM                               5
6fd2c87f-b296-42f0-b197-1e91e994b900 ENTERPRISEPACK                          25
f30db892-07e9-47e9-837c-80727f46fd3d FLOW_FREE                                3
a403ebcc-fae0-4ca2-8c8c-7a907fd6c235 POWER_BI_STANDARD                        6
26d45bd9-adf1-46cd-a9e1-51e9a5524128 ENTERPRISEPREMIUM_NOPSTNCONF             5
90d8b3f8-712e-4f7b-aa1e-62e7ae6cbe96 SMB_APPS                                 3
8c4ce438-32a7-4ac5-91a6-e22ae08d9c8b RIGHTSMANAGEMENT_ADHOC                   4

The script in the post about mailbox auditing uses this command to fetch a set of mailboxes for Office 365 E3 accounts:

$Office365E3 = "6fd2c87f-b296-42f0-b197-1e91e994b900"
$Mbx = Get-AzureADUser -All $True | ? {$_.AssignedLicenses -Match $Office365E3}

We then process those mailboxes to make sure that they are enabled for mailbox auditing.

Leveraging Knowledge Learned

Moving on from mailbox auditing, a delight of PowerShell is that you can repurpose some new knowledge to solve other problems. In this case, I want to create a report of license assignments in the tenant. In other words, which accounts have been assigned the licenses returned by Get-AzureADSubscribedSku.

This script loops through all the license SKUs to find the accounts assigned each SKU. We put things together in a PowerShell list object and output it via Out-GridView (Figure 1). You need to be connected to Azure Active Directory before running the script.

$Report = [System.Collections.Generic.List[Object]]::new() # Create output file 
$Skus = Get-AzureADSubscribedSku | Select Sku*, ConsumedUnits 
ForEach ($Sku in $Skus) {
   Write-Host "Processing license holders for" $Sku.SkuPartNumber
   $SkuUsers = Get-AzureADUser -All $True | ? {$_.AssignedLicenses -Match $Sku.SkuId}
   ForEach ($User in $SkuUsers) {
      $ReportLine  = [PSCustomObject] @{
          User       = $User.DisplayName 
          UPN        = $User.UserPrincipalName
          Department = $User.Department
          Country    = $User.Country
          SKU        = $Sku.SkuId
          SKUName    = $Sku.SkuPartNumber} 
         $Report.Add($ReportLine) }}
$Report | Sort-Object User | Out-GridView

Simple, easy, and a great example of how a little PowerShell can go a long way. And because the code is PowerShell and available to all, you can take it and amend it to match your needs.

Use the Microsoft Graph PowerShell SDK to Create an Office 365 Licensing Report

Important Update: Microsoft will deprecate the Azure AD and MSOL PowerShell modules in June 2023. After Microsoft 365 moves to a new license management platform, scripts that use the license assignment cmdlets from the Azure AD and MSOL modules will cease working after March 31, 2023. With this in mind, you should update any scripts that use these modules for automation tasks, including license management, to use cmdlets from the Microsoft Graph PowerShell SDK instead. Fortunately, I’ve written a Practical365.com article to explain how to create a licensing report with SDK cmdlets.

---

Need more information about how to manage Office 365 licenses with PowerShell? Look no further than the Office 365 for IT Pros eBook. It’s packed full of examples.