Query Microsoft 365 subscriptions with PowerShell

There are 2 main PowerShell modules for managing Azure AD (and access O365 licenses): AzureAD module and MSOnline module. (There are 2 more modules for AzureAD for preview, but I haven’t worked with them yet).

Let’s say that you want to get all users in a specific office, who have O365 licenses? We can do something like this:

We can do something like this:

Get-AzureADUser  -all $true  | Where { $_.PhysicalDeliveryOfficeName  -eq ‘Office Name’ -and $_.AssignedLicenses -ne $null} | select UserPrincipalName , DisplayName | export-csv [AddCsvPath]


That might work, but.. it might also give you more results than what’s expected. What if some users use PowerBI (Free), Flow (Free) or Teams Exploratory license (free Teams license for those who aren’t yet assigned a license).

If you run the previous command, it will get those users too since their AssignedLicenses isn’t actually null.

To do that, let’s first query all O365 available licenses in our tenant and see what we get. We can run something like the following:

Get-AzureADSubscribedSku

You’ll get a result of ObjectId and SkuPartNumber, the SkuPartNumber is the description for the licenses, and the ObjectId will be formatted like this: [TenantID]_[LicenseId]

For example, for the E5 license, the SkuPartNumber is SPE_E5, and the ObjectId is:

[GUID FOR TENANT]_06ebc4ee-1bb5-47dd-8120-11324bc54e06

Now that we know the license ID exactly, we can use it to get users in the required office, with the required license ID:

Get-AzureADUser  -all $true  | Where { $_.PhysicalDeliveryOfficeName  -eq ‘Office Name’ -and ($_.AssignedLicenses).SkuId -eq "06ebc4ee-1bb5-47dd-8120-11324bc54e06"}  | export-csv [AddCsvPath]

For reference, here are some of the most common license information that you might need:

NameID
E118181a46-0d4e-45cd-891e-60aabd171b4e
E305e9a617-0261-4cee-bb44-138d3ef5d965
E506ebc4ee-1bb5-47dd-8120-11324bc54e06
E5 Developer Licensec42b9cae-ea4f-4ab7-9717-81576235ccac
TEAMS Exploratory710779e8-3d4a-4c88-adb9-386c958d1fdf
Teams Commercial29a2f828-8f39-4837-b8ff-c957e86abe3c
Flow Freef30db892-07e9-47e9-837c-80727f46fd3d

This list will only save you time executing Get-AzureADSubscribedSku !


Leave a Reply

Your email address will not be published. Required fields are marked *