Quick and Easy Office 365 License Assignment Report

Leveraging Get-AzureADSubscribedSku to Find Office 365 E3 Accounts

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 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 User | Out-GridView
Quick and Easy Office 365 License Report
Figure 1: Quick and Easy Office 365 License Report

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.

P.S. Proving that PowerShell can solve problems in multiple ways, a previous post explains how to use the Microsoft Online Services module to retrieve and report license information.


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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.