Just When Did a User Account Receive a License?

My article about using the Microsoft Graph PowerShell SDK to generate a licensing report for a tenant is quite popular. The intention of the script is to illustrate the principles of retrieving license information for Azure AD user accounts with the SDK cmdlets. It’s not to deliver a fully-finished licensing report solution. If you’re looking for a more polished report, you can either consider an ISV license management product or upgrade the script to meet your needs.

A recent comment asked about retrieving license assignment data to know when accounts receive licenses. It’s an interesting question and I’m sure it’s information that administrators might find useful in some circumstances.

Azure AD Account Properties Used by Licensing

Azure AD user accounts have two properties of interest for licensing. The first is assignedLicenses, which details the assigned licenses for the account. This example shows that the account has one license identified by an SKU and that three of the service plans are disabled (see this article for details about license management with the Microsoft Graph PowerShell SDK).

$User.AssignedLicenses | Format-List

DisabledPlans        : {31b4e2fc-4cd6-4e7d-9c1b-41407303bd66, aebd3021-9f8f-4bf8-bbe3-0ed2f4f047a1, a23b959c-7ce8-4e57-9140-b90eb88a9e97}
SkuId                : 6fd2c87f-b296-42f0-b197-1e91e994b900
AdditionalProperties : {}

The second property is assignedPlans and contains the service plans from all assigned licenses. A service plan is a not-for-sale license component like Exchange_S_Enterprise, the Exchange Online component that’s bundled into Office 365 E3 and Office 365 E5. The assignedPlans property notes if a service plan is enabled or deleted (removed) and the assignment date. Here’s an edited version of what you might see for an account with an Office 365 E5 license:

$User.AssignedPlans | Where-Object {$_.CapabilityStatus -eq "Enabled"} | Format-Table ServicePlanId, Service, AssignedDateTime

ServicePlanId                        Service                       AssignedDateTime
-------------                        -------                       ----------------
199a5c09-e0ca-4e37-8f7c-b05d533e1ea2 exchange                      15/08/2022 18:25:31
0feaeb32-d00e-4d66-bd5a-43b5b83db82c MicrosoftCommunicationsOnline 16/11/2021 13:48:13
2789c901-c14e-48ab-a76a-be334d9d793a OfficeForms                   16/11/2021 13:48:13
9e700747-8b1d-45e5-ab8d-ef187ceec156 MicrosoftStream               16/11/2021 13:48:13
e95bec33-7c88-4a70-8e19-b10bd9d0c014 SharePoint                    16/11/2021 13:48:13
57ff2da0-773e-42df-b2af-ffb7a2317929 TeamspaceAPI                  16/11/2021 13:48:13
43de0ff5-c92c-492b-9116-175376d08c38 MicrosoftOffice               16/11/2021 13:48:13
33c4f319-9bdd-48d6-9c4d-410b750a4a5a exchange                      16/11/2021 13:48:13
b737dad2-2f6c-4c65-90e3-ca563267e8b9 ProjectWorkManagement         16/11/2021 13:48:13
c87f142c-d1e9-4363-8630-aaea9c4d9ae5 To-Do                         16/11/2021 13:48:13
7547a3fe-08ee-4ccb-b430-5077c5041653 YammerEnterprise              16/11/2021 13:48:13

Multiple service plans are listed for the same service. That’s because some services (like Exchange Online) are responsible for the delivery of multiple services. For example, Exchange Online delivers both email services and the information barriers service to accounts with Office 365 E5 licenses.

TEC Talk: Five Things Microsoft 365 Security Administrators Should Do in 2023

Don’t miss Tony Redmond’s free TEC Talk on March 23rd at 11:00 am EST.

Figuring Out License Assignment Dates

The assignment information for a service plan includes a timestamp. Is it therefore valid to find a service plan associated with a license and assume that its assignment date reflects when an administrator assigned the license to an account?

Regretfully, no. The problem is that the license assignment information reported by the Graph APIs doesn’t distinguish between human-initiated and system-initiated actions. Microsoft tweaks licenses all the time to add or remove service plans (for example, to introduce the Viva Engage Core service plan for products like Office 365 E3). These actions occur in the background and tenant administrators aren’t aware of when they happen. The APIs treat a license update performed by Microsoft as the same as when an administrator assigns a license or disables a service plan for a user account. In a nutshell, this means that the reported assignment date is valid but tells you nothing about the last time an administrator managed licenses for an account.

Figure 1 illustrates the problem. This information comes from selecting a known service plan (information protection) from the Office 365 E3 SKU and using it to report the assignment date for the license. I see accounts like Paul Robichaux whom I know received their license well before August 12, 2019.

Azure AD License Assignment Timestamps
Figure 1: Azure AD License Assignment Timestamps

Nevertheless, the license assignment data is accurate in one sense in that someone or something assigned the license to the account on that date. The possibility that the action might have been a reassignment of a previously held license is another matter.

Tracking License Assignment Dates

To analyze license assignment dates for accounts, I wrote a script that:

  • Defines a hash table of license/SKU identifier (the key) and a service plan identifier that I know exists in the SKU. For example, the Teams Premium license (989a1621-93bc-4be0-835c-fe30171d6463) includes the Teams Webinars Pro service plan (78b58230-ec7e-4309-913c-93a45cc4735b).
  • Runs the Get-MgUser cmdlet to find all licensed users.
  • For each user, find the set of currently enabled licenses and service plans. Then loop through the licenses to check the assigned date for a service plan that belongs to that license (that’s where the hash table comes in).
  • Report the date for each user (Figure 1 shows an extract).
  • The script also outputs an assignment date for each license (European dates shown).
The SharePoint Syntex license was assigned to James Ryan on 01/12/2022 16:59
The Viva Suite license was assigned to James Ryan on 01/12/2022 16:59
The Office 365 E3 license was assigned to James Ryan on 16/11/2021 13:49
The Viva Topics license was assigned to James Ryan on 15/08/2022 18:27
The Flow (Free) license was assigned to James Ryan on 24/04/2019 17:59

Here’s the code. The information about product/SKU and service plan identifiers is available online.

# Create a hash table where the keys are the SKU (product) identifiers and the values are
# the GUIDs of a service plan covered by the SKU (some SKUs have only one service plan).
$SPLookup = @{}
# Office 365 E5 without PSTN Conferncing - Advanced eDiscovery (Advanced eDiscovery)
$SpLookup.Add("26d45bd9-adf1-46cd-a9e1-51e9a5524128", "4de31727-a228-4ec3-a5bf-8e45b5ca48cc")
# EMS E5 - AAdvanced Threat Analytics
$SpLookup.Add("b05e124f-c7cc-45a0-a6aa-8cf78c946968", "14ab5db5-e6c4-4b20-b4bc-13e36fd2227f")
# Teams Premium Test - uses Microsoft Webinars Pro
$SPLookup.Add("36a0f3b3-adb5-49ea-bf66-762134cf063a", "78b58230-ec7e-4309-913c-93a45cc4735b")
# Teams Premium - uses Microsoft Webinars Pro
$SPLookup.Add("989a1621-93bc-4be0-835c-fe30171d6463", "78b58230-ec7e-4309-913c-93a45cc4735b")
# Office 365 E3 (EnterprisePack) - Information Protection Standard
#$SPLookup.Add("6fd2c87f-b296-42f0-b197-1e91e994b900", "5136a095-5cf0-4aff-bec3-e84448b38ea5")
$SPLookup.Add("6fd2c87f-b296-42f0-b197-1e91e994b900", "e95bec33-7c88-4a70-8e19-b10bd9d0c014")
# SharePoint Syntex - Intelligent Content Services for SPO
$SPLookup.Add("f61d4aba-134f-44e9-a2a0-f81a5adb26e4", "fd2e7f90-1010-487e-a11b-d2b1ae9651fc")
# Viva Topics - Cortex
$SPLookup.Add("4016f256-b063-4864-816e-d818aad600c9", "c815c93d-0759-4bb8-b857-bc921a71be83")
# Viva Suite - Viva Goals
$SPLookup.Add("61902246-d7cb-453e-85cd-53ee28eec138", "b44c6eaf-5c9f-478c-8f16-8cea26353bfb")
# Flow Free - Process Simple (Flow)
$SPLookup.Add("f30db892-07e9-47e9-837c-80727f46fd3d", "50e68c76-46c6-4674-81f9-75456511b170")
# Power BI Standard - Azure Analysis BI P0
$SPLookup.Add("a403ebcc-fae0-4ca2-8c8c-7a907fd6c235", "2049e525-b859-401b-b2a0-e0a31Finds c4b1fe4")
# Teams Exploratory - Deskless
$SPLookup.Add("710779e8-3d4a-4c88-adb9-386c958d1fdf", "8c7d2df8-86f0-4902-b2ed-a0458298f3b3")
# Stream - Stream P1
$SPLookup.Add("1f2f344a-700d-42c9-9427-5cea1d5d7ba6", "acffdce6-c30f-4dc2-81c0-372e33c515e")
# Rights management ad hoc
$SPLookup.Add("8c4ce438-32a7-4ac5-91a6-e22ae08d9c8b", "7a39d7dd-e456-4e09-842a-0204ee08187b")

[array]$Users = Get-MgUser -Filter “assignedLicenses/`$count ne 0 and userType eq 'Member'” -ConsistencyLevel eventual -CountVariable Records -All | Sort DisplayName
If (!($Users)) { Write-Host "Whoops! Can't find any accounts..." ; exit }
$LicenseInfo = [System.Collections.Generic.List[Object]]::new() 

ForEach ($User in $Users) {
[array]$Plans = $User.AssignedPlans | Where-Object {$_.CapabilityStatus -eq "Enabled"} | Select Service, ServicePlanid, AssignedDateTime  | Sort-Object ServicePlanId
[array]$AssignedLicenses = Get-MgUserLicenseDetail -UserId $User.Id | Select-Object SkuId, SkuPartNumber

ForEach ($Sku in $AssignedLicenses) {
    $PlanForLookUp = $SPLookup[$Sku.SkuId]
    $AssignedDate = $Plans | Where-Object {$_.ServicePlanId -eq $PlanForLookup} | Select-Object -ExpandProperty AssignedDateTime
    If ($AssignedDate) {
      Write-Host ("The {0} license was assigned to {1} on {2}" -f ($SkuNameTable[$Sku.SkuId]), $User.DisplayName, (Get-Date ($AssignedDate) -format g)) 
      $DataLine  = [PSCustomObject] @{
           User         = $User.DisplayName
           Id           = $User.Id
           Sku          = $Sku.SkuId
           License      = $SkuNameTable[$Sku.SkuId]
           AssignedDate = (Get-Date ($AssignedDate) -format g) }
       $LicenseInfo.Add($DataLine)
    } Else { 
      Write-Host ("The {0} license assigned to {1} is in an unknown or disabled state" -f ($SkuNameTable[$Sku.SkuId]), $User.DisplayName)
      $DataLine  = [PSCustomObject] @{
           User         = $User.DisplayName
           Id           = $User.Id
           Sku          = $Sku.SkuId
           License      = $SkuNameTable[$Sku.SkuId]
           AssignedDate = "Unknown" }
       $LicenseInfo.Add($DataLine)
    } 
  }
}

Using the License Assignment State API

PowerShell being PowerShell, there’s usually an alternative method to explore to do something. In this instance, we can use the Graph License Assignment State API to retrieve the timestamp stored in the Graph for the last time the license assignment changed. This is a relatively new API, and its timestamps only go back to mid-2022. However, it might be an option to explore.

The Azure AD Product names and service plan identifiers for licensing page includes the option to download a CSV file containing license and service plan identifiers. Microsoft updates the CSV file regularly and it’s a useful tool to have.

In my case, I wanted to resolve product identifiers into human-understandable names, so I downloaded the file and loaded it into an array. I then selected the properties I needed, sorted them to create unique values, and loaded the data into a hash table for fast lookup. If your tenant uses some newly-introduced licenses, you might need to “patch” the data by inserting them into the table. I do this below for the Teams Premium test and Viva Suite licenses.

$SkuDataFile = "C:\temp\Product names and service plan identifiers for licensing.csv"
$SkuNameTable = @{}
[array]$MicrosoftSkuData = Import-CSV $SkuDataFile | Select-Object Product_Display_Name, Guid | Sort-Object Guid -Unique
ForEach ($S in $MicrosoftSkuData) { $SkuNameTable.Add($S.Guid,$S.Product_Display_Name) }
$SkuNameTable.Add("36a0f3b3-adb5-49ea-bf66-762134cf063a", "Teams Premium (Test)")
$SkuNameTable.Add("61902246-d7cb-453e-85cd-53ee28eec138", "Viva Suite")

To find the license assignment state for a user account, we can do this:

$Uri = ("https://graph.microsoft.com/beta/users/{0}?`$select=licenseAssignmentStates" -f $User.Id)
$Data = Invoke-MgGraphRequest -Uri $Uri
ForEach ($License in $Data.licenseAssignmentStates) {
 Write-Host ("{0}: {1} last updated on {2}" -f $User.DisplayName, $SkuNameTable[$license.skuid], $License.lastupdatedDateTime ) }

The code generates output like:

James Ryan: SharePoint Syntex last updated on 01/12/2022 16:59:09
James Ryan: Microsoft Flow Free last updated on 01/12/2022 16:59:09
James Ryan: Office 365 E3 last updated on 01/12/2022 16:59:09
James Ryan: Viva Suite last updated on 01/12/2022 16:59:09
James Ryan: Viva Topics last updated on 15/08/2022 18:27:49

Using the Audit Log to Track License Assignments

The audit log is the other source of information about license assignments. I’ve covered this topic previously and concluded that although the data revealed by audit events needs some work to uncover, it does tell you when administrators assigned or removed licenses from user accounts. The problem is that the audit log is only available for Office 365 E3 licenses (and above) and audit events are only available for 90 days (Office 365 E3) or 365 days (Office 365 E5). Many organizations get around this problem by offloading audit data to a SIEM like Splunk or Microsoft Sentinel.

Various Methods Exist, All Flawed

Getting back to the original question, various methods exist to track license assignments:

  • Use a service plan-assigned date.
  • Use the license assignment state date.
  • Use the audit data.

All methods have their downsides. It would be nice if Microsoft noted a timestamp for the last administrator-initiated change for each license assigned to user accounts, but they don’t and leave the task of assignment interpretation to administrators. Just another thing to add to the list…

The Microsoft 365 Kill Chain and Attack Path Management

An effective cybersecurity strategy requires a clear and comprehensive understanding of how attacks unfold. Read this whitepaper to get the expert insight you need to defend your organization!

About the Author

Tony Redmond

Tony Redmond has written thousands of articles about Microsoft technology since 1996. He is the lead author for the Office 365 for IT Pros eBook, the only book covering Office 365 that is updated monthly to keep pace with change in the cloud. Apart from contributing to Practical365.com, Tony also writes at Office365itpros.com to support the development of the eBook. He has been a Microsoft MVP since 2004.

Comments

  1. Ameer

    Where do we get the billingenddate for the subcriptions of our tenants in O365 Products without using the MsolSubscription in powershell

    1. Avatar photo
      Tony Redmond

      Billing date for subscriptions is coming (or so I hear from Microsoft)

  2. Carl Knecht

    I really wanted something like this to be native from Microsoft but found the same — automated changes by Microsoft reset the date making it an unreliable data source. I wrote a script that gathers user licensing information similar to your methods above, compares to log file from the previous run, and keeps a running date for when that subscription was initially assigned. I store everything on a SPO site and have that data going back to 2019 when I first started. It’s been VERY helpful when making additional decisions about reclamation, especially comparing to things like the Project usage report. Newly assigned license that hasn’t been used in 90 days after assignment? Older license that hasn’t been used in 180 days? Those get made available for others to use. Without knowing when the assignment occurred, those decisions can’t be made. Having the initial assignment data within a week’s worth of time has been good enough for us.

    Original script was using AzureAD module, but I migrated to Microsoft Graph PowerShell SDK last year. There is probably some solution out there that would have taken care of all this for us, but this was also a great way to learn how to interact with the Microsoft Graph PowerShell SDK and serves us well, even if there is occasional maintenance needed.

      1. Derek

        Besides the audit log for User License Updated (individual changes), we would also need to check AD group membership changes and AD group assigned licenses, correct?

Leave a Reply