Quick PowerShell Script Does the Trick
Browsing the personal blog of Vasil Michev, the esteemed technical editor of the Office 365 for IT Pros eBook (I have to call him that as otherwise he gets very vexed and causes problems when he edits a chapter), I found an interesting post about using names specified in a CSV file to remove licenses from Office 365 accounts. In fact, I found a logic bug in Vasil’s PowerShell code, which neatly reversed the normal situation when he criticizes my poor attempts at coding.
In any case, the thought came to me that it would be useful to have a script that reported the license assignments to users and output a CSV file that an Office 365 administrator could either use for their own purposes or as an input to Vasil’s script.
You can find license assignment information in the Billing section of the Office 365 Admin Center (select Licenses – Figure 1).
The Office 365 Admin Center also supports the option of exporting license information, but only after you choose a specific license. Anyway, it’s nice to be able to do your own thing in terms of automating administrative processes, which is what PowerShell is all about.
Report Office 365 License Assignments
The quick and dirty PowerShell code in this script fetches details of all licensed accounts in an Office 365 tenant and extracts the license assignment information from each account. The information is written into an array that’s then grouped to calculate a number for each license assignment. We then write the information about to the CSV file, taking care to sort it by license and displayname (just to show how to do multi-property sorts in PowerShell). Here’s the code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
# Quick and dirty code to create a report of license assignments in an Office 365 tenant $Report = [System.Collections.Generic.List[Object]]::new() $Users = Get-MsolUser -All | Where {$_.isLicensed -eq $true} | Select UserPrincipalName, DisplayName, Department, IsLicensed Write-Host "Processing Users" ForEach ($User in $Users) { $SKUs = @(Get-MsolUser -UserPrincipalName $User.UserPrincipalName | Select -ExpandProperty Licenses) ForEach ($Sku in $Skus) { $ReportLine = [PSCustomObject]@{ User = $User.UserPrincipalName SKU = $Sku.AccountSkuId.Split(":")[1] Name = $User.DisplayName Dept = $User.Departmnent} $Report.Add($ReportLine) } } Cls # Write out the information Write-Host "License information" $Groupdata = $Report | Group-Object -Property SKU $Groupdata | Sort Count -Descending | Select Name, Count # Set sort properties so that we get ascending sorts for one property after another $Sort1 = @{Expression='SKU'; Ascending=$true } $Sort2 = @{Expression='Name'; Ascending=$true } $Report | Select SKU, Name, User | Sort-Object $Sort1, $Sort2 | Export-CSV c:\Temp\UserLicenses.CSV -NoTypeInformation |
Figure 2 shows what the on-screen output looks like:
While Figure 3 shows how the data appears in the CSV file.
If you want to make the license names clearer (for example, to translate ENTERPRISEPACK to Office 365 E3), you can add some code to replace the license name before writing it to the output array.
Update May 30, 2020: The code in the GitHub version of the script shows how to resolve SKU names to user-friendly license names.
Another idea is to increase the number of account properties written out to the CSV file to make analysis easier or more productive. For example, you could include properties such as UsageLocation (Country), City, or Department to focus in on license usage in those areas.
The intricacies of dealing with Office 365 licenses via PowerShell are explained in the Office 365 for IT Pros eBook. We might even add this script in a future update!
Perfect!!
How would I go about adding a property such as Department to the script?
I updated the script to add department for you…
Thanks Tony!
Hi Tony, thanks for this great script, I was wondering how to implement adding the friendly names of the SKU’s. I have a list of the SKU’s in this format, $Friendly = @{ “O365_BUSINESS_ESSENTIALS” = “Office 365 Business Essentials”, any help would be appreciated.
Well, there are many ways to approach the problem.
1. A simple If statement to translate the SKU to a friendly name.
2. A switch statement to resolve multiple SKUs. I’d do this for up to 10 SKUs.
3. A hash table loaded with the SKUs and friendly names and a lookup against the table to resolve SKUs to friendly names. This is the way to go when you have more than ten or so SKUs.
Ok, I just checked and I have about 20 SKU’s we use, so a hash table will be the way to go, I just don’t know how to go about modifying the script to achieve this
I updated https://github.com/12Knocksinna/Office365itpros/blob/master/ReportLicenseAssignmentsToUsers.Ps1 to show how to add a user-friendly license name to the output.
Excellent, thanks so much for the help Tony, keep up the great blog!