Exporting Office 365 Licenses to CSV

Recently, I was asked by a collegue to review a script that is publically available in the technet gallery (https://gallery.technet.microsoft.com/scriptcenter/Office-365-licenses-and-ee8837a0)

The author mentions in the comments “If you find a way to improve this code, please share it.”  As a result, I thought I’d post my object based version.

Regarding the “English” names for the license, you can update the $sku hashtable to change the name of the license in the exported CSV.

As far as overall design, I start with a hashtable of fixed property names ($licenseHash).  This is the template for our object.  Then, I loop through the licenses and services available in the tenant and add these as keys to the hashtable.  Once I have discovered all the licenses and services, I discover all the users in the tenant.  Foreach user, I take a copy of $licenseHash and hard type it to a [pscustomobject] ($licenseduser).  This allows me to treat each of the licenses as properties of the object.  After mapping the licenses to matching properties, I use Export-CSV to append it to the spreadsheet.

Without further ado, here is the script!

Updated Dec 28 2017 to account for the service status as per JBros comment

This script will create a comma separated file with a line per user and the following columns:
Display Name, Domain, UPN, Is Licensed?, all the SKUs in tenant, all the services,
Errors, ImmutableId and BlockCredential.
Based on previous script by Marcus Tarquinio
After starting the script it will ask for the credentials to connect to Office 365
CSV specified by $csvpath
Version: 1.0
Author: Matthew DeBoer
Creation Date: September 27 2017
Purpose/Change: Initial script development
Import-Module MSOnline
# CSV output path
$csvpath = 'C:\temp\OfficeLicenseCounts.csv'
#Translate SKUs to English
$Sku = @{
"DESKLESSPACK" = "Office 365 (Plan K1)"
"DESKLESSWOFFPACK" = "Office 365 (Plan K2)"
"LITEPACK" = "Office 365 (Plan P1)"
"EXCHANGESTANDARD" = "Office 365 Exchange Online Only"
"STANDARDPACK" = "Enterprise Plan E1"
"STANDARDWOFFPACK" = "Office 365 (Plan E2)"
"ENTERPRISEPACK" = "Enterprise Plan E3"
"ENTERPRISEPACKLRG" = "Enterprise Plan E3"
"ENTERPRISEWITHSCAL" = "Enterprise Plan E4"
"STANDARDPACK_STUDENT" = "Office 365 (Plan A1) for Students"
"STANDARDWOFFPACKPACK_STUDENT" = "Office 365 (Plan A2) for Students"
"ENTERPRISEPACK_STUDENT" = "Office 365 (Plan A3) for Students"
"ENTERPRISEWITHSCAL_STUDENT" = "Office 365 (Plan A4) for Students"
"STANDARDPACK_FACULTY" = "Office 365 (Plan A1) for Faculty"
"STANDARDWOFFPACKPACK_FACULTY" = "Office 365 (Plan A2) for Faculty"
"ENTERPRISEPACK_FACULTY" = "Office 365 (Plan A3) for Faculty"
"ENTERPRISEWITHSCAL_FACULTY" = "Office 365 (Plan A4) for Faculty"
"ENTERPRISEPACK_B_PILOT" = "Office 365 (Enterprise Preview)"
"STANDARD_B_PILOT" = "Office 365 (Small Business Preview)"
"VISIOCLIENT" = "Visio Pro Online"
"POWER_BI_ADDON" = "Office 365 Power BI Addon"
"POWER_BI_INDIVIDUAL_USE" = "Power BI Individual User"
"POWER_BI_STANDALONE" = "Power BI Stand Alone"
"POWER_BI_STANDARD" = "Power-BI standard"
"PROJECTCLIENT" = "Project Professional"
"PROJECTONLINE_PLAN_1" = "Project Online"
"PROJECTONLINE_PLAN_2" = "Project Online and PRO"
"EMS" = "Enterprise Mobility Suite"
"RIGHTSMANAGEMENT_ADHOC" = "Windows Azure Rights Management"
"MCOMEETADV" = "PSTN conferencing"
"SHAREPOINTSTORAGE" = "SharePoint storage"
"PLANNERSTANDALONE" = "Planner Standalone"
"BI_AZURE_P1" = "Power BI Reporting and Analytics"
"INTUNE_A" = "Windows Intune Plan A"
# Connect to Office 365 (need modules installed)
write-verbose "Connecting to Office 365..."
$credential = Get-Credential
Connect-MsolService -Credential $credential
# Get a list of all licences that exist within the tenant
write-verbose "Geting the licenses available in tenant"
$licensetype = Get-MsolAccountSku | Where {$_.ConsumedUnits -ge 1}
# License Object. This forms the property names of the user objects we populate later
$licensehash = @{
#Get all account SKUs in tenant
$AccountSkus = Get-MsolAccountSku
#Loop through each license in tenant and get the sku
foreach ($license in $licensetype)
    if($license.SkuPartNumber -notin $licensehash.keys){
        if($license.SkuPartNumber -in $sku.keys){
            $licensename = $sku.($license.SkuPartNumber)
            $licensename = $license.skupartnumber

        # Get a list of all the services in the tenant
        $services = ($AccountSkus | where {$_.AccountSkuId -eq $license.AccountSkuId}).ServiceStatus.serviceplan.servicename
        ForEach($service in $services){
            if($service -in $sku.keys){
                $servicename = $sku.($service)
                $servicename = $service
            if($servicename -notin $licensehash.keys){

# Get a list of all the users in the tenant
write-verbose "Getting all users in the Office 365 tenant..."
$users = Get-MsolUser -All
# Loop through all users found in the tenant
foreach ($user in $users)
    $displayname = $user.displayname -Replace ",",""
    $licenseduser = [pscustomobject]$licensehash
    $licenseduser.Displayname = $displayname
    $licenseduser.Domain = $user.UserPrincipalName.Split("@")[1]
    $licenseduser.UPN = $user.userprincipalname
    $licenseduser.ImmutableID = $user.immutableid
    $licenseduser.Errors = $user.errors
    $licenseduser.blockcredential = $user.blockcredential
    $licenseduser.IsLicensed = $user.IsLicensed
    if ($user.isLicensed){
        ForEach($userlicense in $user.licenses){
            if($userlicense.AccountSkuID.ToString() -in $licensetype.AccountSKUid){
                $usersku = (($userlicense.accountskuid.tostring()) -split ':')[1]
                if($usersku -in $sku.keys){
                    $usersku = $sku.($usersku)
                if($usersku -in $licensehash.keys){
                    $licensedUser.$usersku = $true
            $UserLicenseConfiguredServices = $userlicense.ServiceStatus | Where{$_.provisioningstatus}
            ForEach($service in $UserLicenseConfiguredServices){
                if($service.ServicePlan.ServiceName -in $sku.keys){
                    $servicename = $sku.($service.ServicePlan.ServiceName)
                    $servicename = $service.ServicePlan.ServiceName
                if($servicename -in $licensehash.keys){
                    $licensedUser.$servicename = $service.ProvisioningStatus

$licenseduser | Export-csv -path $csvpath -Force -Append -notypeinformation