Time to Review Licensing When Prices Increase

On September 9, I explained the steps to convert a PowerShell script from the Azure AD cmdlets due to stop working on March 31, 2023 to equivalents from the Microsoft Graph SDK for PowerShell. I then followed up by noting some of the issues involved in using the SDK for interactive scripts. Given Microsoft’s announcement of price increases for Office 365 and Microsoft 365 licenses due in March 2022, it’s a good time to audit the set of licenses in a tenant and ask some questions about the distribution of licenses across accounts. To do that, we need a licensing report.

Many examples of generating such a report exist based on the cmdlets which will stop working next June (here’s one version), so in this article I’ll go through the steps to generate a licensing report using SDK cmdlets.

Stage 1: Extract Licensing Data for the Tenant

The basic steps in generating a report are in two stages. First, we create two data (CSV) files containing:

  • The product licenses (SKUs) used in the tenant.
  • The service plans belonging to the product licenses. A service plan is something like Exchange Online or Teams which is bunded in a product license like Office 365 E3.

Because every tenant is different, we have some code (available from GitHub) to generate these files. We connect to the Graph, select the beta profile to ensure that license data is available, and use the Get-MgSubscribedSku cmdlet to fetch the license data for the tenant. We then export the list of products (SKUs) to a CSV before looping through the products to extract the service plans and exporting them to a second CSV.

Connect-MgGraph 
Select-MgProfile beta
[Array]$Skus = Get-MgSubscribedSku

# Generate CSV of all product SKUs used in tenant
$Skus | Select SkuId, SkuPartNumber, DisplayName  | Export-Csv -NoTypeInformation c:\temp\ListOfSkus.Csv
# Generate list of all service plans used in SKUs in tenant
$SPData = [System.Collections.Generic.List[Object]]::new()
ForEach ($S in $Skus) {
   ForEach ($SP in $S.ServicePlans) {
     $SPLine = [PSCustomObject][Ordered]@{  
         ServicePlanId = $SP.ServicePlanId
         ServicePlanName = $SP.ServicePlanName
         ServicePlanDisplayName = $SP.ServicePlanName }
     $SPData.Add($SPLine)
 }
}
$SPData | Sort ServicePlanId -Unique | Export-csv c:\Temp\ServicePlanData.csv -NoTypeInformation

This code generates two files. However, the files need some massaging before we can use them because the generated data contains GUIDs to identify the licenses and service plans plus code names. For example, 6fd2c87f-b296-42f0-b197-1e91e994b900 is the GUID identifying Office 365 E3, which has a code name of ENTERPRISEPACK, while 3fb82609-8c27-4f7b-bd51-30634711ee67 is an example of a service plan GUID. This is code BPOS_S_TODO_3, the To Do service plan included in Office 365 E5.

We could use these values in the report, but it’s nicer to have user-friendly (or user-understandable) names. We therefore edit the CSV files to add values in the DisplayName column containing the user-friendly names. Figure 1 shows editing of the CSV file holding service plan data.

Updating service plan information with display names
Figure 1: Updating service plan information with display names

After you’re finished editing the CSV files, they should both have three fields per record. Here’s an extract of the SKU information (top) and Service Plan information (bottom):

SKU information:

SkuId	                                SkuPartNumber	    DisplayName
078d2b04-f1bd-4111-bbd4-b4b1b354cef4	AAD_PREMIUM         Azure AD Premium P1
84a661c4-e949-4bd2-a560-ed7766fcaf2b	AAD_PREMIUM_P2      Azure AD Premium P2
c52ea49f-fe5d-4e95-93ba-1de91d380f89	RIGHTSMANAGEMENT    Azure Information Protection P1
90d8b3f8-712e-4f7b-aa1e-62e7ae6cbe96	SMB_APPS            Business Apps (free)
 
Service Plan Information:

ServicePlanId	                        ServicePlanName	                ServicePlanDisplayName
041fe683-03e4-45b6-b1af-c0cdc516daee	POWER_VIRTUAL_AGENTS_O365_P2    Power Virtual Agents for Office 365 P2
0683001c-0492-4d59-9515-d9a6426b5813	POWER_VIRTUAL_AGENTS_O365_P1    Power Virtual Agents for Office 365 P1
07699545-9485-468e-95b6-2fca3738be01	FLOW_O365_P3	                Flow for Office 365 P3
0898bdbb-73b0-471a-81e5-20f1fe4dd66e	KAIZALA_STANDALONE              Kaizala Standalone
0f9b09cb-62d1-4ff4-9129-43f4996f83f4	FLOW_O365_P1                    Flow for Office 365 P1

Interpreting the values used by Microsoft is an art into itself. The Product names and service plan licensing page is a valuable resource, but sometimes it’s a matter of guesswork based on your knowledge of the products and service plans in use.

Finally, after editing the SKU and Service Plan information, rename the files to match the expected names used in the reporting script. These are:

  • c:\temp\SkuDataComplete.csv: The product data
  • c:\temp\ServicePlanDataComplete.csv: The service plan data.

Of course, you can use whatever names you like if you update the script code to match.

Stage 2: Generate the Report

The steps involved in creating a report are straightforward.

  • Connect to the Microsoft Graph as before. You’ll need to consent to the following permissions: Directory.AccessAsUser.All, Directory.ReadWrite.All, and AuditLog.Read.All.
  • Check that the input data files are available and exit if not.
  • Fetch the set of Azure AD user accounts using the Get-MgUser cmdlet.
  • Loop through the set of user accounts.
  • For each licensed account (some accounts like those used for resource or shared mailboxes don’t need licenses), extract the license data and check if any license has disabled service plans. Check the information against the input data files to get human-friendly names.
  • Record the license information in a PowerShell list.
  • After processing the user accounts, create a HTML report and CSV file containing the license information.

Here’s the main loop (you can download the complete script from GitHub):

ForEach ($User in $Users) {
If ([string]::IsNullOrWhiteSpace($User.AssignedLicenses) -eq $False) { # Only process account if it has some licenses
  Write-Host "Processing" $User.DisplayName
  [array]$LicenseInfo = $Null; [array]$DisabledPlans = $Null
  ForEach ($License in $User.AssignedLicenses) {
     If ($SkuHashTable.ContainsKey($License.SkuId) -eq $True) { # We found a match in the SKU hash table
        $LicenseInfo += $SkuHashTable.Item($License.SkuId) }
     Else { # Nothing doing, so output the SkuID
        $LicenseInfo += $License }
  # Report any disabled service plans in licenses
   If ([string]::IsNullOrWhiteSpace($License.DisabledPlans) -eq $False ) { # Check if disabled service plans in a license
     ForEach ($DisabledPlan in $License.DisabledPlans) { # Try and find what service plan is disabled
       If ($ServicePlanHashTable.ContainsKey($DisabledPlan) -eq $True) { # We found a match in the Service Plans hash table
          $DisabledPlans += $ServicePlanHashTable.Item($DisabledPlan) }
       Else { # Nothing doing, so output the Service Plan ID
          $DisabledPlans += $DisabledPlan }
      } # End ForEach disabled plans
    } # End if check for disabled plans  
   } # End Foreach Licenses
  # Report information
  [string]$DisabledPlans = $DisabledPlans -join ", " 
  [string]$LicenseInfo = $LicenseInfo -join (", ")
  $ReportLine = [PSCustomObject][Ordered]@{  
       User            = $User.DisplayName
       UPN             = $User.UserPrincipalName
       Country         = $User.Country
       Department      = $User.Department
       Title           = $User.JobTitle
       Licenses        = $LicenseInfo
      "Disabled Plans" = $DisabledPlans }
  $Report.Add($ReportLine)
  } #end If account is licensed
  Else { $UnlicensedAccounts++}
} # End ForEach Users

Figure 2 shows the license information as generated by the script. As you can see, we include the country and department properties from the Azure AD accounts to allow sorting of the information based on those properties. Because this is PowerShell code, it’s easy to add any property available in Azure AD user accounts to the set included in the report.

Using Out-GridView to view the license information
Figure 2: Using Out-GridView to view the license information

Figure 3 shows the HTML version of the report.

HTML version of the licensing report for a Microsoft 365 tenant
Figure 3: HTML version of the licensing report for a Microsoft 365 tenant

Apart from anything else, reports like this also highlight deficiencies in user information stored in Azure AD. In Figure 3, you can see that several accounts lack a country property while some lack a job title. These deficiencies affect the information displayed in different places within Microsoft 365 such as user profile cards and the organization view for people available in Teams.

Update (November 23, 2022): I’ve included some code in the script to generate a new section in the HTML report to detail the SKU usage for the tenant.

Running the Report

Generating a licensing report could be something you do quarterly or semi-annually. If that’s the case, it’s probably OK to run the script using an interactive Graph SDK connection. On the other hand, if you want a more regular output, consider creating a new registered app in Azure AD to use to run the script (app-only access) or use Azure Automation.

Remember, this code is PowerShell and what I have written is there to explore and explain the principles behind generating a licensing report. Now that you know what to do, let your imagination run riot and let us know what kind of interesting ways you exploit license data.

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

    Is there a way in the HTML report information section at the bottom to list totals for each license type?

    1. Avatar photo
      Tony Redmond

      Well, you could do something like this and include the output in the report:

      report | group licenses | sort count, name | ft count, name

      Count Name
      —– —-
      1 Enterprise Mobility and Security E5, Office 365 E3, Viva Topics, Power BI Standard (free)
      1 Microsoft Flow Free, Office 365 E5 without Audio Conferencing
      1 Microsoft Flow Free, Rights Management Ad-Hoc, Office 365 E3, Viva Topics
      1 Office 365 E3, Microsoft Flow Free
      1 Office 365 E3, Viva Topics, Microsoft Flow Free, Rights Management Ad-Hoc
      1 Office 365 E3, Viva Topics, Power BI Standard (free), Microsoft Flow Free, Enterprise Mobility and Security E5
      1 Office 365 E5 without Audio Conferencing, Viva Topics, Microsoft Flow Free
      1 Office 365 E5 without Audio Conferencing, Viva Topics, Microsoft Flow Free, Power BI Standard (free)
      1 Office 365 E5 without Audio Conferencing, Viva Topics, Microsoft Flow Free, Power BI Standard (free), Enterpri…
      1 Office 365 E5 without Audio Conferencing, Viva Topics, Microsoft Stream, Rights Management Ad-Hoc
      1 Power BI Standard (free), Rights Management Ad-Hoc
      1 Rights Management Ad-Hoc, Microsoft Teams Exploratory, Power BI Standard (free), Microsoft Flow Free, Microsof…
      1 Viva Topics, Enterprise Mobility and Security E5, Office 365 E3
      1 Viva Topics, Office 365 E3
      2 Office 365 E3, Viva Topics
      2 Office 365 E3, Viva Topics, Microsoft Flow Free
      14 Office 365 E3

      This is also possible as it gives you the overall count of consumed units per license.

      Get-MgSubscribedSku | Format-Table SkuId, SkuPartNumber, ConsumedUnits

      SkuId SkuPartNumber ConsumedUnits
      —– ————- ————-
      1f2f344a-700d-42c9-9427-5cea1d5d7ba6 STREAM 2
      b05e124f-c7cc-45a0-a6aa-8cf78c946968 EMSPREMIUM 4
      4016f256-b063-4864-816e-d818aad600c9 TOPIC_EXPERIENCES 14
      6fd2c87f-b296-42f0-b197-1e91e994b900 ENTERPRISEPACK 25
      f30db892-07e9-47e9-837c-80727f46fd3d FLOW_FREE 11
      a403ebcc-fae0-4ca2-8c8c-7a907fd6c235 POWER_BI_STANDARD 6
      26d45bd9-adf1-46cd-a9e1-51e9a5524128 ENTERPRISEPREMIUM_NOPSTNCONF 5
      710779e8-3d4a-4c88-adb9-386c958d1fdf TEAMS_EXPLORATORY 1
      90d8b3f8-712e-4f7b-aa1e-62e7ae6cbe96 SMB_APPS 0
      093e8d14-a334-43d9-93e3-30589a8b47d0 RMSBASIC 0
      8c4ce438-32a7-4ac5-91a6-e22ae08d9c8b RIGHTSMANAGEMENT_ADHOC 5

    2. Avatar photo
      Tony Redmond

      Just because it’s the holiday season and everyone is feeling jolly, I updated the report script to include a SKU summary usage section in the HTML output. Enjoy!

  2. Jens

    Hi Tony,

    the script itself is working and is displaying (almost) all data perfectly fine. But the “Licences” column in the report file doesn’t show anything. User with multiple licences do have a “,” in their respective row. So the script does recognize the values but can’t display them. Any advice?

    1. Avatar photo
      Tony Redmond

      Did you create the lookup files for the license and service plan names?

      1. Jens

        Thanks for the fast reply!
        Yes. I renamed the initially created files to SkuDataComplete & ServicePlanDataComplete.
        If they have different names the script breaks with the correct error code “Can’t find the xxx Data File”.

        1. Avatar photo
          Tony Redmond

          Do this:

          1. Connect to the Graph and Select-MgProfile Beta
          2. Use Get-MgUser to populate the $User variable with details of a user who has some licenses.
          3. Run this code (extracted from the script) to see what happens

          If ([string]::IsNullOrWhiteSpace($User.AssignedLicenses) -eq $False) { # Only process account if it has some licenses
          Write-Host “Processing” $User.DisplayName
          [array]$LicenseInfo = $Null; [array]$DisabledPlans = $Null
          ForEach ($License in $User.AssignedLicenses) {
          If ($SkuHashTable.ContainsKey($License.SkuId) -eq $True) { # We found a match in the SKU hash table
          $LicenseInfo += $SkuHashTable.Item($License.SkuId) }
          Else { # Nothing doing, so output the SkuID
          $LicenseInfo += $License }
          # Report any disabled service plans in licenses
          If ([string]::IsNullOrWhiteSpace($License.DisabledPlans) -eq $False ) { # Check if disabled service plans in a license
          ForEach ($DisabledPlan in $License.DisabledPlans) { # Try and find what service plan is disabled
          If ($ServicePlanHashTable.ContainsKey($DisabledPlan) -eq $True) { # We found a match in the Service Plans hash table
          $DisabledPlans += $ServicePlanHashTable.Item($DisabledPlan) }
          Else { # Nothing doing, so output the Service Plan ID
          $DisabledPlans += $DisabledPlan }
          } # End ForEach disabled plans
          } # End if check for disabled plans
          } # End Foreach Licenses

          $LicenseInfo should store the license information. This is what I see when I run the code for a user:

          Processing James Ryan
          PS> $LicenseInfo
          Office 365 E3
          Viva Topics
          Microsoft Flow

    2. Marcin

      Basically, SkuDataComplete.csv is missing “DisplayName” column which is generated by the script which extracts licensing data for the tenant.

      Please correct line 14 in “CreateCSVFilesForSKUsAndServicePlans.ps1”:

      $Skus | Select SkuId, SkuPartNumber, DisplayName | Export-Csv -NoTypeInformation c:\Users\adminmxr\Downloads\SkuDataCompletev2.Csv

      1. Avatar photo
        Tony Redmond

        Aha. I see what happened. The text contains a warning that the file must be updated with display names but it’s not very clear. I’ve amended the text for the script (so that the column exists in the CSV file) and the descriptive text. Thanks!

  3. Blaalyn

    Is there advice for this error? I both consented to the needed permissions directly in Graph AND an App Reg in Azure (don’t know if needed) –

    Finding licensed Azure AD accounts…
    Get-MgUser : Calling principal does not have required MSGraph permissions AuditLog.Read.All

    1. Avatar photo
      Tony Redmond

      You need to consent to the Graph AuditLog.Read.All permission.

      Connect-MgGraph -Scope AuditLog.Read.All should do the trick.

  4. Marco Quattrini

    Hi Tony,
    thank you for the script: works like a charm!
    Is there in your opinion a way to have in the output file the Enabled service plans instead of the Disabled ones?
    Thank you.

    MQ

    1. Avatar photo
      Tony Redmond

      I love the Office 365 for IT Pros eBook because I write something down there that I am bound to forget and then someone asks me a question like this… Here’s how to find the service plans assigned to a user account:

      [array]$AllLicenses = Get-MgUserLicenseDetail -UserId Kim.Akers@Office365itpros.com | Select-Object -ExpandProperty ServicePlans | Sort-Object ServicePlanId -Unique
      $AllLicenses | ? {$_.ProvisioningStatus = “Success”}
      $AllLicenses

      AppliesTo ProvisioningStatus ServicePlanId ServicePlanName
      ——— —————— ————- —————
      User Success 041fe683-03e4-45b6-b1af-c0cdc516daee POWER_VIRTUAL_AGENTS_O365_P2
      User Success 0feaeb32-d00e-4d66-bd5a-43b5b83db82c MCOSTANDARD
      Company Success 113feb6c-3fe4-4440-bddc-54d774bf0318 EXCHANGE_S_FOUNDATION
      User Success 14ab5db5-e6c4-4b20-b4bc-13e36fd2227f ATA
      User Success 17ab22cd-a0b3-4536-910a-cb6eb12696c0 DYN365_CDS_VIRAL
      User Success 199a5c09-e0ca-4e37-8f7c-b05d533e1ea2 MICROSOFTBOOKINGS
      User Success 2049e525-b859-401b-b2a0-e0a31c4b1fe4 BI_AZURE_P0
      User Success 2789c901-c14e-48ab-a76a-be334d9d793a FORMS_PLAN_E3
      Company Success 2b815d45-56e4-4e3a-b65c-66cb9175b560 ContentExplorer_Standard
      User Success 2e2ddb96-6af9-4b1d-a3f0-d6ecfd22edb2 ADALLOM_S_STANDALONE
      User Success 31b4e2fc-4cd6-4e7d-9c1b-41407303bd66 PROJECT_O365_P2
      User Success 33c4f319-9bdd-48d6-9c4d-410b750a4a5a MYANALYTICS_P2
      User Success 41781fb2-bc02-4b7c-bd55-b576c07bb09d AAD_PREMIUM
      User Success 43de0ff5-c92c-492b-9116-175376d08c38 OFFICESUBSCRIPTION
      User Success 4ff01e01-1ba7-4d71-8cf8-ce96c3bbcf14 DYN365_CDS_O365_P2
      User Success 50e68c76-46c6-4674-81f9-75456511b170 FLOW_P2_VIRAL
      User Success 5136a095-5cf0-4aff-bec3-e84448b38ea5 MIP_S_CLP1
      User Success 5689bec4-755d-4753-8b61-40975025187c RMS_S_PREMIUM2
      User Success 57ff2da0-773e-42df-b2af-ffb7a2317929 TEAMS1
      User Success 5dbe027f-2339-4123-9542-606e4d348a72 SHAREPOINTENTERPRISE
      User Success 6c57d4b6-3b23-47a5-9bc9-69f17b4947b3 RMS_S_PREMIUM
      User Success 7547a3fe-08ee-4ccb-b430-5077c5041653 YAMMER_ENTERPRISE
      User Success 76846ad7-7776-4c40-a281-a386362dd1b9 FLOW_O365_P2
      Company Success 882e1d05-acd1-4ccb-8708-6ee03664b117 INTUNE_O365
      User Success 8a256a2b-b617-496d-b51b-e76466e88db0 MFA_PREMIUM
      User Success 8c7d2df8-86f0-4902-b2ed-a0458298f3b3 Deskless
      Company Success 94065c59-bc8e-4e8b-89e5-5138d471eaff MICROSOFT_SEARCH
      User Success 94a54592-cd8b-425e-87c6-97868b000b91 WHITEBOARD_PLAN2
      User Success 95b76021-6a53-4741-ab8b-1d1f3d66a95a CDS_O365_P2
      User Success 9e700747-8b1d-45e5-ab8d-ef187ceec156 STREAM_O365_E3
      User Success a23b959c-7ce8-4e57-9140-b90eb88a9e97 SWAY
      User Success aebd3021-9f8f-4bf8-bbe3-0ed2f4f047a1 KAIZALA_O365_P3
      User Success b737dad2-2f6c-4c65-90e3-ca563267e8b9 PROJECTWORKMANAGEMENT
      User Success b74d57b2-58e9-484a-9731-aeccbba954f0 GRAPH_CONNECTORS_SEARCH_INDEX_TOPICEXP
      User Success b76fb638-6ba6-402a-b9f9-83d28acb3d86 VIVA_LEARNING_SEEDED
      User Success bea4c11e-220a-4e6d-8eb8-8ea15d019f90 RMS_S_ENTERPRISE
      User Success c1ec4a95-1f05-45b3-a911-aa3fa01094f5 INTUNE_A
      User Success c68f8d98-5534-41c8-bf36-22fa496fa792 POWERAPPS_O365_P2
      User Success c815c93d-0759-4bb8-b857-bc921a71be83 CORTEX
      User Success c87f142c-d1e9-4363-8630-aaea9c4d9ae5 BPOS_S_TODO_2
      Company Success db4d623d-b514-490b-b7ef-8885eee514de Nucleus
      User Success e95bec33-7c88-4a70-8e19-b10bd9d0c014 SHAREPOINTWAC
      User Success eec0eb4f-6444-4f95-aba0-50c24d67f998 AAD_PREMIUM_P2
      User Success efb87545-963c-4e0d-99df-69c6916d9eb0 EXCHANGE_S_ENTERPRISE

  5. Andrejs

    Hello,

    Please kindly assist, how to run MS Visio usage report? My purpose is to know users, when they have last time used MS Visio in order to remove MS Visio licence from those, who are not using this app at all or use very rarely.

    Thanks

  6. Mike

    Hi, great scripts thanks. At the moment the -Filter parameter seems broken, but removing that has worked for now.

    Is it possible to run this at a partner tenant level and iterate through customer tenants?

    Thanks again

    1. Avatar photo
      Tony Redmond

      There’s only one filter in the script and it works just fine. What filter are you referring to?

      [Array]$Users = Get-MgUser -Filter “UserType eq ‘Member'” -All | Sort DisplayName

      It is possible to run for multiple tenants. You’d need to connect to each tenant seperately to collect the data.

      1. Mike

        There appears to be an issue with the latest release that has broken the -Filter parameter:

        We’ve traced the issue back to CSDL metadata that was used to generate the module. We’ve unlisted the broken versions as we wait for a fix to be made to the CSDL metadata in microsoftgraph/msgraph-metadata#208.

        Please follow uninstallation steps below to remove the unlisted version, then reinstall the latest module (1.11.1)

        So there’s no way to adapt the script to loop through each delegated tenant? That would be a real time-saver, not to mention only needing the partner credentials!

        Thanks for the quick reply.

  7. Harish tej

    Hi Tony

    Thanks for the script , the script mentioned in [https://office365itpros.com/2020/03/18/quick-and-easy-office-365-license-assignment-report/] is giving license status of the users, but users having multiple licenses getting displayed with their license in multiple rows.
    user1 ——- ENTERPRISEPREMIUM
    user1 ——- AAD_PREMIUM

    Is it possible to display the users having multiple licenses to gets displayed in single row like below?
    user1 ——- AAD_PREMIUM,ENTERPRISEPREMIUM

    1. Avatar photo
      Tony Redmond

      Of course. You can join license details together with a -Join to produce a string containing all of the license names. In fact, the script mentioned in this article does just that in lines like:

      [string]$DisabledPlans = $DisabledPlans -join “, ”
      [string]$LicenseInfo = $LicenseInfo -join (“, “)

      1. Harish Tej

        Hi Tony for the above script, I can get Licenses in a single line

        But for the below Script, I am not able to do this

        $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

      2. Harish Tej

        Hi Tony for the above script, I can get Licenses in a single line.

        But for the below Script, I am not able to do this.

        $Report = [System.Collections.Generic.List[Object]]::new()
        $Users = Get-MsolUser -All | where {$_.isLicensed -eq $true}
        Write-Host “Processing Users”
        ForEach ($User in $Users) {
        $SKUs = @(Get-MsolUser -UserPrincipalName $User.UserPrincipalName | Select -ExpandProperty Licenses)
        ForEach ($Sku in $Skus) {
        $Sku = $Sku.AccountSkuId.Split(“:”)[1]
        Switch ($Sku) {
        “AAD_PREMIUM” { $License = “Azure AD Premium P1” }
        “AAD_PREMIUM_P2” { $License = “Azure AD Premium P2” }
        “ENTERPRISEPREMIUM” { $License = “Enterprise Mobility & Security E5” }
        “AADPREMIUM” { $License = “Azure AD Premium P2” }
        “ENTERPRISEPACK” { $License = “Office 365 E3” }
        “ENTERPRISEPREMIUM_NOPSTNCONF” { $License = “Office 365 E5 No PSTN” }
        default { $License = “Unlicensed” }
        } #End Switch
        $ReportLine = [PSCustomObject][Ordered]@{
        User = $User.UserPrincipalName
        SKU = $Sku -join(“,”)
        License = $License
        Name = $User.DisplayName
        Title = $User.Title
        City = $User.City
        Country = $User.UsageLocation
        Department = $User.Department
        CreatedOn = Get-Date($User.WhenCreated) -Format g}
        $Report.Add($ReportLine) }
        }

        1. Avatar photo
          Tony Redmond

          Please don’t use the old AzureAD and MSOL cmdlets. They will stop working in early 2023. Use the Microsoft Graph PowerShell SDK cmdlets instead as I explained in the article. I can’t help you with the old cmdlets.

          1. Harish Tej

            Sure Tony, Thanks for update 🙂 will work with PowerShell SDK cmdlets.

    2. Harish tej

      Thanks Tony, I missed this part …. but thanks for mentioning and spending some time on this.

  8. Joerg

    Hi Tony,
    I am looking for a way with the powershell graph module to access the information about a license expiration date. We have prepaid licenses in our tenant an would like to include the expiration date in a report.
    Do you know if there is a way to get that information via powershell. It is available in the Admin Center.

    1. Avatar photo
      Tony Redmond

      I don’t think this is currently possible. I have looked for this data in the past and couldn’t find it. Maybe the data will be available after Microsoft 365 moves to the new licensing platform on August 26.

  9. Tim

    Thanks for this Tony – you are a treasure to the community!

  10. Martin Sellars

    Switching to new tech is always easier with a good guide… thanks Tony.
    I’m not a big fan of maintaining text files, so I changed that section to retrieve the friendly names on the fly… unsupported APIs can be risky, but this is read only so if it breaks, I can always go back to a file.

    Connect-AzAccount -Tenant “tenantname.onmicrosoft.com”

    $resource = ‘74658136-14ec-4630-ad9b-26e160ff0fc6’;

    $token1 = (Get-AzAccessToken -ResourceUrl $resource -TenantId (Get-AzContext).Tenant.Id.ToString()).token

    $accountskus = Invoke-RestMethod ‘https://main.iam.ad.ext.azure.com/api/AccountSkus’ -Headers @{Authorization = “Bearer $($token1)”; “x-ms-client-request-id” = [guid]::NewGuid().ToString(); “x-ms-client-session-id” = [guid]::NewGuid().ToString()}
    $ImportSkus = $accountskus | select skuid,accountskuid,name | sort skuid -unique
    $ImportServicePlans = $accountskus | %{$_.servicestatuses|%{$_.serviceplan|select serviceplanid,servicename,displayname}} | sort serviceplanid -unique

    $SkuHashTable = @{}
    ForEach ($Line in $ImportSkus) { $SkuHashTable.Add([string]$Line.SkuId, [string]$Line.Name) }
    $ServicePlanHashTable = @{}
    ForEach ($Line2 in $ImportServicePlans) { $ServicePlanHashTable.Add([string]$Line2.ServicePlanId, [string]$Line2.DisplayName) }

    1. Avatar photo
      Tony Redmond

      I’m glad the post was helpful. All I can do is explain the relevant principles. After that, it’s up to you to decide how to apply the principles in the context of your organization and its business requirements.

  11. Peter Vogl

    Any idea how to get the expiration dates of licenses such as (Get-MsolSubscription).NextLifecycleDate?

  12. Dave

    Thank you for this. I am trying to determine when a specific license, in this case an E3 Security and Mobility license, was added for all users (less than 100).

  13. Brenkster

    Thanks Tony, now it works like a charm.

  14. Brenkster

    I have the same output as Eetu, I’m connected to the beta endpoint.
    The “licenses” row shows “Microsoft.Graph.PowerShell.Models.MicrosoftGraphAssignedLicense”.

    After line 41 ( $SkuHashTable ) I get the following:
    Exception calling “Add” with “2” argument(s): “Item has already been added. Key in dictionary: ” Key being added: ””
    MethodInvocationException:
    Line |
    11 | … icePlans) { $ServicePlanHashTable.Add([string]$Line2.ServicePlanId, [ …

    1. Avatar photo
      Tony Redmond

      Have you the file c:\temp\ServicePlanDataComplete.csv with Service Plan information? Have you changed it to add something which would cause a duplicate in the hash table (you can’t have a duplicate key – the SKU Id is the key).

      1. Brenkster

        The serviceplandatacomplete looks like this:
        “SkuId”,”SkuPartNumber”,”Displayname”
        “c5928f49-12ba-48f7-ada3-0d743a3601d5″,”VISIOCLIENT”,”VISIO ONLINE PLAN 2″
        The SKUdatacomplete looks like this:
        “ServicePlanId”,”ServicePlanName”,”DisplayName”
        “604ec28a-ae18-4bc6-91b0-11da94504ba9″,”TEAMS_ADVCOMMS”,”Microsoft 365 Advanced Communications”

        1. Avatar photo
          Tony Redmond

          The script depends on being able to load the contents of two CSV files into hash tables. After loading, the $SkuHashTable should look like this:

          Name Value
          —- —–
          4fb214cb-a430-4a91-9c91-497… Microsoft Teams Rooms Premium
          dab7782a-93b1-4074-8bb1-0e6… Microsoft 365 Business Basic
          c52ea49f-fe5d-4e95-93ba-1de… Azure Information Protection P1
          6a0f6da5-0b87-4190-a6ae-9bb… Windows 10 Enterprise E3
          4b585984-651b-448a-9e53-3b1… Office 365 F3

          And the $ServicePlanHashTable should look like this:

          Name Value
          —- —–
          95b76021-6a53-4741-ab8b-1d1… Common Data Services Office 365 P2
          8a256a2b-b617-496d-b51b-e76… Multi-factor authentication premium
          94065c59-bc8e-4e8b-89e5-513… Microsoft Search
          afa73018-811e-46e9-988f-f75… Common Data Services for Office 365 P3
          531ee2f8-b1cb-453b-9c21-d21… Excel Premium
          6db1f1db-2b46-403f-be40-e39… Customer Key
          31b4e2fc-4cd6-4e7d-9c1b-414… Project for Office 365 P2
          6dc145d6-95dd-4191-b9c3-185… Communications DLP
          8c098270-9dd4-4350-9b30-ba4… Microsoft Cloud App Security for Office 365

          If the hash tables don’t load for some reason, the script won’t be able to resolve SKUIds into display names or Service Plan Ids into display names. The CSV files are loaded in these lines:

          $ImportSkus = Import-CSV c:\temp\SkuDataComplete.csv
          $ImportServicePlans = Import-CSV c:\temp\ServicePlanDataComplete.csv
          $SkuHashTable = @{}
          ForEach ($Line in $ImportSkus) { $SkuHashTable.Add([string]$Line.SkuId, [string]$Line.DisplayName) }
          $ServicePlanHashTable = @{}
          ForEach ($Line2 in $ImportServicePlans) { $ServicePlanHashTable.Add([string]$Line2.ServicePlanId, [string]$Line2.ServicePlanDisplayName) }

          Can you execute the lines outside the script (the code is straightforward PowerShell and doesn’t need to be connected to the Graph or anything else)?

          I assume you built the CSV files using the code in the article?

          1. Brenkster

            The csv was built from the code in the article, I think the line ForEach ($Line2 in $ImportServicePlans) { $ServicePlanHashTable.Add([string]$Line2.ServicePlanId, [string]$Line2.ServicePlanDisplayName) } should be $Line2.DisplayName) } at the end, as there is no ServicePlanDisplayName in the csv.

            After running the 2 import commands I end up with this:
            $SkuHashTable
            Name Value
            —- —–
            Microsoft 365 Advanced Communications

            $ServicePlanHashTable
            Name Value
            —- —–
            VISIO ONLINE PLAN 2
            Could it be the separation character? Mine is , (comma)

          2. Avatar photo
            Tony Redmond

            I see what happened. I changed the name of the field in my CSV file when I was editing the display names for service plans to make them more readable. I’ve updated the article to make this clear. The contents of the service plan CSV file should look like the following (4 lines of data plus headers shown):

            ServicePlanId ServicePlanName ServicePlanDisplayName
            041fe683-03e4-45b6-b1af-c0cdc516daee POWER_VIRTUAL_AGENTS_O365_P2 Power Virtual Agents for Office 365 P2
            0683001c-0492-4d59-9515-d9a6426b5813 POWER_VIRTUAL_AGENTS_O365_P1 Power Virtual Agents for Office 365 P1
            07699545-9485-468e-95b6-2fca3738be01 FLOW_O365_P3 Flow for Office 365 P3
            0898bdbb-73b0-471a-81e5-20f1fe4dd66e KAIZALA_STANDALONE Kaizala Standalone

        2. Nelson P

          Hi Brenkster,

          I have the same problem. It returns Microsoft.Graph.PowerShell.Models.MicrosoftGraphAssignedLicense,
          What have you done to fix this?

          Thanks,
          NP

          1. Avatar photo
            Tony Redmond

            Are you sure you have connected using the beta profile? The V1.0 endpoint doesn’t return license information.

  15. Eetu Hämäläinen

    Hi, very nice script. I’m not sure what I’m doing wrong I followed these steps but on the report on the “Licenses” blade I can see only Microsoft.Graph.PowerShell.Models.MicrosoftGraphAssignedLicense, Microsoft.Graph.PowerShell.Models.MicrosoftGraphAssignedLicense. Instead of friendly DisplayName I created as you suggested.

    1. Avatar photo
      Tony Redmond

      Are you connected to the beta endpoint? The license data is not returned by the V1.0 endpoint.

      1. Eetu Hämäläinen

        Thanks! This worked, also I suggest to run one line at a time and then check the outcomes to see everything works as expected. 🙂

        1. Avatar photo
          Tony Redmond

          Good to hear that you’re up and running. As to the suggestion, once you’ve created and updated the CSV files, there should be little need to go near them and they can be left alone. They should load accurately every time!

        2. Nelson P

          Hi Eetu,

          I have the same problem. It returns Microsoft.Graph.PowerShell.Models.MicrosoftGraphAssignedLicense,
          What have you done to fix this?

          Thanks,
          NP

Leave a Reply