Using Powershell custom tables to get more useful license usage information from O365

When you want to check the number of licenses in your O365 environment, you use the command get-msolaccountsku, which returns a set of information on the licenses that are part of your environment.  This shows the total number of licenses, the number that have been assigned to users (“consumed”) and the number of licenses that are in a warning state.

While working with a customer that has a number of active SKU ID’s, I needed to improve on this to add a column which has a count of unassigned licenses for easy reference, so here’s a Powershell script which is also a simple example of how you can use custom table formats to generate custom output on the fly.

Most of the format entries are a straight replica of what comes back from the get-msolaccountsku command but the line highlighted in red does a calculation to show the number of unassigned licenses for that SkuID.  The expression element of a table format is in the form of a script block so you can do some really interesting stuff by inserting whatever code you want.  Inside the block, the $_ variable refers to the object that the table is processing at that particular moment

# get-o365licensecounts.ps1
# Outputs license information for O365 including a column showing # of unassigned licenses by SKU
# Format string to define the table
$f = @{expr={$_.AccountSkuID};label="AccountSkuId"},

# output the info using the format we just defined.
# sorted by active units -descending.
Get-MsolAccountSku | sort activeunits -desc | ft $f -auto

Default output from get-msolaccountsku:..

… and with the script using the custom format…:

Much better.

For more information on how custom tables work, see this TechNet article.