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)
{
$i++
$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