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

<#
.DESCRITION
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
https://gallery.technet.microsoft.com/scriptcenter/Office-365-licenses-and-ee8837a0
 
.PARAMETER
After starting the script it will ask for the credentials to connect to Office 365
 
.OUTPUT
CSV specified by $csvpath
 
.NOTES
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"
"PROJECTESSENTIALS" = "Project Lite"
"PROJECTCLIENT" = "Project Professional"
"PROJECTONLINE_PLAN_1" = "Project Online"
"PROJECTONLINE_PLAN_2" = "Project Online and PRO"
"ECAL_SERVICES" = "ECAL"
"EMS" = "Enterprise Mobility Suite"
"RIGHTSMANAGEMENT_ADHOC" = "Windows Azure Rights Management"
"MCOMEETADV" = "PSTN conferencing"
"SHAREPOINTSTORAGE" = "SharePoint storage"
"PLANNERSTANDALONE" = "Planner Standalone"
"CRMIUR" = "CMRIUR"
"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 = @{
    "DisplayName"='';
    "Domain"='';
    "UPN"='';
    "IsLicensed"='';
    "Errors"='';
    "ImmutableID"='';
    "BlockCredential"='';
}
 
#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)
        }else{
            $licensename = $license.skupartnumber
        }
        $licensehash.Add($licensename,'')

 
        # 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)
            }else{
                $servicename = $service
            }
            if($servicename -notin $licensehash.keys){
                $licensehash.add($servicename,'')
            }
        }
    }
} 

# 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)
                }else{
                    $servicename = $service.ServicePlan.ServiceName
                }
                if($servicename -in $licensehash.keys){
                    $licensedUser.$servicename = $service.ProvisioningStatus
                }
            }
        

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

 

 

Advertisements