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).
Figure 1: License assignment information in the Office 365 Admin Center
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:
# 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:
Figure 2: Reporting the count of assigned licenses in an Office 365 tenant
While Figure 3 shows how the data appears in the CSV file.
Figure 3: Office 365 license assignment information output to a 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!
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
Hi Tony,
Thanks for the script , i have tested on my test tenant and it is working fine and i have a doubt i am getting the output for all the users but users having multiple licenses getting update individually multiple times
I want them to be appeared in the single row instead of getting updates in multiple rows.
Is it Possible ?
{"id":null,"mode":"button","open_style":"in_modal","currency_code":"EUR","currency_symbol":"\u20ac","currency_type":"decimal","blank_flag_url":"https:\/\/office365itpros.com\/wp-content\/plugins\/tip-jar-wp\/\/assets\/images\/flags\/blank.gif","flag_sprite_url":"https:\/\/office365itpros.com\/wp-content\/plugins\/tip-jar-wp\/\/assets\/images\/flags\/flags.png","default_amount":100,"top_media_type":"featured_image","featured_image_url":"https:\/\/office365itpros.com\/wp-content\/uploads\/2022\/11\/cover-141x200.jpg","featured_embed":"","header_media":null,"file_download_attachment_data":null,"recurring_options_enabled":true,"recurring_options":{"never":{"selected":true,"after_output":"One time only"},"weekly":{"selected":false,"after_output":"Every week"},"monthly":{"selected":false,"after_output":"Every month"},"yearly":{"selected":false,"after_output":"Every year"}},"strings":{"current_user_email":"","current_user_name":"","link_text":"Virtual Tip Jar","complete_payment_button_error_text":"Check info and try again","payment_verb":"Pay","payment_request_label":"Office 365 for IT Pros","form_has_an_error":"Please check and fix the errors above","general_server_error":"Something isn't working right at the moment. Please try again.","form_title":"Office 365 for IT Pros","form_subtitle":null,"currency_search_text":"Country or Currency here","other_payment_option":"Other payment option","manage_payments_button_text":"Manage your payments","thank_you_message":"Thank you for supporting the work of Office 365 for IT Pros!","payment_confirmation_title":"Office 365 for IT Pros","receipt_title":"Your Receipt","print_receipt":"Print Receipt","email_receipt":"Email Receipt","email_receipt_sending":"Sending receipt...","email_receipt_success":"Email receipt successfully sent","email_receipt_failed":"Email receipt failed to send. Please try again.","receipt_payee":"Paid to","receipt_statement_descriptor":"This will show up on your statement as","receipt_date":"Date","receipt_transaction_id":"Transaction ID","receipt_transaction_amount":"Amount","refund_payer":"Refund from","login":"Log in to manage your payments","manage_payments":"Manage Payments","transactions_title":"Your Transactions","transaction_title":"Transaction Receipt","transaction_period":"Plan Period","arrangements_title":"Your Plans","arrangement_title":"Manage Plan","arrangement_details":"Plan Details","arrangement_id_title":"Plan ID","arrangement_payment_method_title":"Payment Method","arrangement_amount_title":"Plan Amount","arrangement_renewal_title":"Next renewal date","arrangement_action_cancel":"Cancel Plan","arrangement_action_cant_cancel":"Cancelling is currently not available.","arrangement_action_cancel_double":"Are you sure you'd like to cancel?","arrangement_cancelling":"Cancelling Plan...","arrangement_cancelled":"Plan Cancelled","arrangement_failed_to_cancel":"Failed to cancel plan","back_to_plans":"\u2190 Back to Plans","update_payment_method_verb":"Update","sca_auth_description":"Your have a pending renewal payment which requires authorization.","sca_auth_verb":"Authorize renewal payment","sca_authing_verb":"Authorizing payment","sca_authed_verb":"Payment successfully authorized!","sca_auth_failed":"Unable to authorize! Please try again.","login_button_text":"Log in","login_form_has_an_error":"Please check and fix the errors above","uppercase_search":"Search","lowercase_search":"search","uppercase_page":"Page","lowercase_page":"page","uppercase_items":"Items","lowercase_items":"items","uppercase_per":"Per","lowercase_per":"per","uppercase_of":"Of","lowercase_of":"of","back":"Back to plans","zip_code_placeholder":"Zip\/Postal Code","download_file_button_text":"Download File","input_field_instructions":{"tip_amount":{"placeholder_text":"How much would you like to tip?","initial":{"instruction_type":"normal","instruction_message":"How much would you like to tip? Choose any currency."},"empty":{"instruction_type":"error","instruction_message":"How much would you like to tip? Choose any currency."},"invalid_curency":{"instruction_type":"error","instruction_message":"Please choose a valid currency."}},"recurring":{"placeholder_text":"Recurring","initial":{"instruction_type":"normal","instruction_message":"How often would you like to give this?"},"success":{"instruction_type":"success","instruction_message":"How often would you like to give this?"},"empty":{"instruction_type":"error","instruction_message":"How often would you like to give this?"}},"name":{"placeholder_text":"Name on Credit Card","initial":{"instruction_type":"normal","instruction_message":"Enter the name on your card."},"success":{"instruction_type":"success","instruction_message":"Enter the name on your card."},"empty":{"instruction_type":"error","instruction_message":"Please enter the name on your card."}},"privacy_policy":{"terms_title":"Terms and conditions","terms_body":null,"terms_show_text":"View Terms","terms_hide_text":"Hide Terms","initial":{"instruction_type":"normal","instruction_message":"I agree to the terms."},"unchecked":{"instruction_type":"error","instruction_message":"Please agree to the terms."},"checked":{"instruction_type":"success","instruction_message":"I agree to the terms."}},"email":{"placeholder_text":"Your email address","initial":{"instruction_type":"normal","instruction_message":"Enter your email address"},"success":{"instruction_type":"success","instruction_message":"Enter your email address"},"blank":{"instruction_type":"error","instruction_message":"Enter your email address"},"not_an_email_address":{"instruction_type":"error","instruction_message":"Make sure you have entered a valid email address"}},"note_with_tip":{"placeholder_text":"Your note here...","initial":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"empty":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"not_empty_initial":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"saving":{"instruction_type":"normal","instruction_message":"Saving note..."},"success":{"instruction_type":"success","instruction_message":"Note successfully saved!"},"error":{"instruction_type":"error","instruction_message":"Unable to save note note at this time. Please try again."}},"email_for_login_code":{"placeholder_text":"Your email address","initial":{"instruction_type":"normal","instruction_message":"Enter your email to log in."},"success":{"instruction_type":"success","instruction_message":"Enter your email to log in."},"blank":{"instruction_type":"error","instruction_message":"Enter your email to log in."},"empty":{"instruction_type":"error","instruction_message":"Enter your email to log in."}},"login_code":{"initial":{"instruction_type":"normal","instruction_message":"Check your email and enter the login code."},"success":{"instruction_type":"success","instruction_message":"Check your email and enter the login code."},"blank":{"instruction_type":"error","instruction_message":"Check your email and enter the login code."},"empty":{"instruction_type":"error","instruction_message":"Check your email and enter the login code."}},"stripe_all_in_one":{"initial":{"instruction_type":"normal","instruction_message":"Enter your credit card details here."},"empty":{"instruction_type":"error","instruction_message":"Enter your credit card details here."},"success":{"instruction_type":"normal","instruction_message":"Enter your credit card details here."},"invalid_number":{"instruction_type":"error","instruction_message":"The card number is not a valid credit card number."},"invalid_expiry_month":{"instruction_type":"error","instruction_message":"The card's expiration month is invalid."},"invalid_expiry_year":{"instruction_type":"error","instruction_message":"The card's expiration year is invalid."},"invalid_cvc":{"instruction_type":"error","instruction_message":"The card's security code is invalid."},"incorrect_number":{"instruction_type":"error","instruction_message":"The card number is incorrect."},"incomplete_number":{"instruction_type":"error","instruction_message":"The card number is incomplete."},"incomplete_cvc":{"instruction_type":"error","instruction_message":"The card's security code is incomplete."},"incomplete_expiry":{"instruction_type":"error","instruction_message":"The card's expiration date is incomplete."},"incomplete_zip":{"instruction_type":"error","instruction_message":"The card's zip code is incomplete."},"expired_card":{"instruction_type":"error","instruction_message":"The card has expired."},"incorrect_cvc":{"instruction_type":"error","instruction_message":"The card's security code is incorrect."},"incorrect_zip":{"instruction_type":"error","instruction_message":"The card's zip code failed validation."},"invalid_expiry_year_past":{"instruction_type":"error","instruction_message":"The card's expiration year is in the past"},"card_declined":{"instruction_type":"error","instruction_message":"The card was declined."},"missing":{"instruction_type":"error","instruction_message":"There is no card on a customer that is being charged."},"processing_error":{"instruction_type":"error","instruction_message":"An error occurred while processing the card."},"invalid_request_error":{"instruction_type":"error","instruction_message":"Unable to process this payment, please try again or use alternative method."},"invalid_sofort_country":{"instruction_type":"error","instruction_message":"The billing country is not accepted by SOFORT. Please try another country."}}}},"fetched_oembed_html":false}
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!
Hi Tony,
Thanks for the script , i have tested on my test tenant and it is working fine and i have a doubt i am getting the output for all the users but users having multiple licenses getting update individually multiple times
I want them to be appeared in the single row instead of getting updates in multiple rows.
Is it Possible ?
Sure. It’s PowerShell…
Loop down through the results and collect the records for each user and the licenses they hold and put the data into a list.