Use the Microsoft Graph PowerShell SDK to Retrieve Details of Microsoft 365 Subscriptions
A reader noted that the number of service plans covered by Microsoft 365 licenses grows over time and that it’s hard to keep track of changes. Most Microsoft 365 licenses are composed of multiple service plans, like Exchange Online Plan 2 and SharePoint Online Plan 1. The service plans tell Microsoft 365 what functionality the user who holds a license can access.
Subscriptions refer to the set of products that a Microsoft 365 tenant has used over time. The subscriptions for a product can be free, for a limited trial period, or paid-for on a monthly or annual basis. Information about subscriptions can be found in the Billing section of the Microsoft 365 admin center (Figure 1).
The Evolution of Service Plans
New service plans appear over time. Microsoft sometimes informs administrators about a new service plan through a message center notification. For instance, MC682559 (19 October 2023) announces the Purview Discovery service plan, which controls access to certain Purview applications (like content search). This is a good example of the expansion of licensing to control different aspects of Microsoft 365. Having functionality controlled by a service plan is a good thing because it is then easy to exercise per-user control over who can use the functionality by enabling or disabling the relevant service plan. Other service plans arrive when Microsoft launches new apps, like Microsoft Loop or the many different parts of the Viva Suite.
Between new applications and an expansion of service plans to control preexisting functionality, the number of service plans has grown steadily over the past few years. Office 365 E3 now spans 33 service plans, some of which are for old apps like Kaizala and StaffHub. At the other end of the spectrum, Microsoft 365 E5 has 89 service plans.
As mentioned above, having service plans to control functionality is a good thing, but the growth in service plans can make it hard to track what features are included in various licenses.
Making Sense of Product (SKU) and Service Plan GUIDs
Internally, Microsoft 365 apps refer to products like Office 365 E3 and Enterprise Mobility and Security by their SKU identifier. This is a GUID, just like the identifiers used for the service plans in a product. Reporting GUIDs is not very human-friendly and we need a way to resolve the GUIDs into a more understandable form. My go-to here is to use the downloadable CSV file available from the Microsoft 365 product and service plan reference page. I’m a fan of using this data to resolve GUIDs to human-friendly product and service plan names in scripts like the Microsoft 365 licensing report.
The file contains details of the identifiers for all the available Microsoft 365 products (SKUs) and service plans together with names and display names. For example, Figure 2 shows some of the service plan records for the Microsoft 365 A5 for Facility product, including one licensing the holder to use Minecraft Education Edition.
Microsoft updates the product data download periodically and it’s a great resource to help people working with licenses to understand how everything connects together. It’s possible that the product data file misses some service plans. For example, at the time of writing the Microsoft 365 E5 product includes service plans named Copilot, Avatars for Teams (additional), and Customer Lockbox (A) that aren’t in the product data file. These are likely to be new service plans added to Microsoft 365 E5 since the last time Microsoft generated the downloadable CSV. In these situations, you can edit the product data file with Excel to add the missing records before importing the data into PowerShell arrays or hash tables for use in scripts.
Writing a Script to Report Subscriptions
I used the Microsoft Graph PowerShell SDK to create a script to report tenant subscriptions and their service plans. The basic structure of the script is as follows:
- Connect to the Graph with Connect-MgGraph -Scopes Directory.Read.All.
- Run the Get-MgSubscribedSku cmdlet to retrieve the set of products that the tenant has subscribed to.
- Check if the downloaded product data file is available. If it is, import the data and create two hash tables. The first contains product identifiers and product names. The second holds service plan identifiers and names. The script uses the hash table to resolve an identifier into a human-friendly display name.
- For each product, find the number of units (licenses) bought, consumed, and available plus the renewal date for subscriptions where a monthly fee is charged. This information can be used to plan when budget funds are needed to pay for subscriptions.
- Extract the set of service plans for each product and store the data in a list.
- After collecting all the data, generate an HTML report. I wanted some conditional formatting to highlight products with three or fewer available licenses. There are many examples of how to generate conditional formatting for HTML with PowerShell. Most generate individual table rows and insert the conditional formatting in a row as necessary (a good example is in the script explained in an article about reporting DNS configurations). I used the approach explained in this StackOverflow article to convert the data to XML and modify an attribute.
- The report has a separate section for subscriptions and for each subscribed product. The script also generates a CSV file with subscription information.
Figure 3 shows the report output for my tenant. You can see how the report highlights license availability (or not) with the conditional formatting.
Because it doesn’t access a lot of Graph or workload data, the script should run quickly on any tenant. The slowest part is running Get-MgSubscribedSku to fetch the current subscriptions, but even the largest tenant is likely to have fewer than 20 different subscriptions. You can download a copy of the script from GitHub.
Understanding Subscriptions and Service Plans
This is just another form of reporting data that’s accessible to tenant administrators that perhaps you might never think of going near. But sometimes it is interesting to know what’s under the hood, such as when you want to run the Set-MgUserLicense cmdlet to disable one or more service plans for user accounts, or to know exactly what’s included in a license. Or if you’re like me and want to spend a couple of hours playing with PowerShell to see what kind of report you can generate. It takes all kinds to make the world.
