Monthly Archives: July 2016

Script to dump all your assigned Office 365 user licenses

As part of a licence transition from one level of office 365 to another, I wanted a way to record/audit who had what licences before/after the audit.

The script below dumps all the relevant information to a tab delimited text file.

Before we get to the script, I want to talk about some of the design considerations….

If we were to normalize the data we’d have 3 different sources of raw data:

  • List of users
  • List of possible licenses levels
  • List of possible sublicense levels.

To record all this in a relational database, you’d want a table for each of the above.

Then you’d need a few table to record the relationships and which users have which licenses.

Since many business users aren’t comfortable with relational data structures, I decided to de-normalize the data in favor of making it easy to consume.

In english, that means that much of the data will be repeated.

The fields we collect are:

  • UPN (usually email)
  • Lastname
  • FirstName
  • Displayname (It’s common for utility accounts like conference rooms to not have a first/last name so I include the displayname)
  • The Name of the Licence assigned to the user (the ‘primary’ license, otherwise known as the “MSOLAccountSKU”
  • The name of the sublicense that goes with the Primary *(See below)*
  • The deployment status of the sublicense


Notes about the ‘sublicense’ what I mean here is this: Say you have an E3 license, this is composed of a bunch of ‘sub’ liceneses that you can turn on-off independent of one another, examples of sub-licenses would be Exchange, SharePoint, Sway, Office Online, etc…

The sublicense values are not uniqe, for example, both E1 and E3 have a ‘Sway’ sublicense type.

Here’s another interesting tidbit: All primary licenses have at least one sublicense.

I wanted to flatten the data as much as possible so the sublicense field is a combo of the primary and sublicense

This will all be a lot clearer once you run the script so here it is…

write-host "Note, you must connect to MSOL first before running this script!"
#$users = get-msoluser -all
#$users = get-msoluser -maxresults 10
$users = get-msoluser -all | sort Lastname, firstname
$scriptpath = $(get-location).path
$file = "$scriptpath\MSOLLICENSEUSAGEREPORT_$(get-date -f yyyy-dd-MM_HHmm).csv"

# "sep=`t" | out-file $file #this helps excel open the file correctly...
$line = "UPN($($users.count))`t LastName`t FirstName`tDisplayName`t Primary`t Primary:secondary`t secondaystatus"
write-host $line
$line | out-file $file -append

$i = 0
foreach($user in $users)
	$count = $user.licenses.count
	# note get-msolaccountsku shows all licenses  

	if ($user.isLicensed -eq $false) 
		#write-host "$($user.UserPrincipalName) (0)" -foregroundcolor yellow
		$line = "$($user.UserPrincipalName)`t$($User.LastName)`t $($user.firstname)`t $($user.displayname)`tNonAssigned`tNonAssigned:None`tNone"
		$line | out-file $file -append
		write-host $line -foregroundcolor yellow
	foreach ($license in $user.licenses)
		write-host "$($user.UserPrincipalName) ($count) $($license.AccountSkuID)" -foregroundcolor yellow	
		$active = $license.servicestatus | where {$_.ProvisioningStatus -ne "disabled"}
		if ($active.count -le 0)
		    $Line = "$($user.UserPrincipalName)`t$($User.LastName)`t$($user.firstname)`t$($user.displayname)`t$($license.AccountSkuID)`t$($license.AccountSkuID):NoneAssigned`tEmptyLicense"
			write-host $line -foregroundcolor red
			$line | out-file $file -append
		foreach ($one in $active)
			#Write-Host "   $($one.ServicePlan.ServiceName): $($one.ProvisioningStatus) " -foregroundcolor green 
            $line =  "$($user.UserPrincipalName)`t$($User.LastName)`t$($user.firstname)`t$($user.displayname)`t$($license.AccountSkuID)`t$($license.AccountSkuID):$($one.ServicePlan.ServiceName)`t$($one.ProvisioningStatus)"
			write-host $line 
			$line | out-file $file -append
write-host "-----------------------------------------------------------------------------------"
write-host "Report is complete $i of $($users.count) users pulled for the report accounted for."
write-host "Saved as $file " -foregroundcolor green


PS Script to list all Office 365 licenses and sublicenses available (AccountSKUID and ServiceName)

We’re transitioning some licensing at work and I thought it would be helpful to have a ‘catalog’ of all the available options.

The script below will produce such a list…

#note, you must run connect-msol first!
$licenses = get-msolaccountsku
Write-host "This script will output all the license and sublicense types for your tenant"
write-host "(Technically, these are AccountSkuID and ServiceStatus.Serviceplan.Servicename)" -foregroundcolor gray
write-host " "
write-host "Primary License (AccountSkuID)" -foregroundcolor green
write-host "   Sub License (AccountSkuID.ServiceStatus.ServicePlan.ServiceName)" -foregroundcolor yellow
write-host "----------------------------------------------------------------------------"
foreach ($license in $licenses)
    $line = "{0,-35} {1,18} {2,18}" -f $license.accountskuID, "($($license.activeunits) active)", "($($license.consumedunits) used)" 
    write-host $line -foregroundcolor green
    foreach ($sublicense in $license.servicestatus)
    	write-host  "   $($sublicense.serviceplan.servicename)" -foregroundcolor yellow