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
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.

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.

7 Replies to “Quick and Easy Office 365 License Assignment Report”

  1. Thank You! This was the only report I could find that uses Get-AzureADUser with Get-AzureADSubcribed to pull the full licensing information. I needed CompanyName as part of the report, which was easily added.

  2. Thanks for the report. But upon getting the report for my environment, one user had O365 A5 Faculty license but actually not visible either on Azure AD or Office 365 (direct and inherited both). How can that be possible? How the report showing he has O365 A5 Faculty license?

  3. Thanks Tony, is there a way to add in same report to show the license assignment path to a user (Direct, Inherited via a group etc.)?

    1. Not easily… You’d have to check if group-based licensing is used first, find the users covered by group licensing, and the licenses assigned by the groups, and take those facts into account when reporting. Very doable, but not a couple of lines.

Leave a Reply

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