Update the Microsoft 365 Licensing Report Script to Find Underused Accounts
In October 2021, I wrote about how to use the Microsoft Graph PowerShell SDK to create a licensing report for a Microsoft 365 tenant. That report lists the licenses assigned to each Azure AD account together with any disabled service plans for those licenses. It’s a valuable piece of information to help tenants manage license costs.
But we can do better. At least, that’s what some readers think. They’d like to know if people use their assigned licenses so that they can remove expensive licenses from accounts that aren’t active. One way to approach the problem is to use the Microsoft 365 User Activity Report script to identify people who haven’t been active in Exchange Online. SharePoint Online, Teams, OneDrive for Business, and Yammer over the last 180 days. The report already includes an assessment of whether an account is in use, so all you need to do is find those who aren’t active and consider removing their licenses.
Another solution to the problem is to update the licensing report script. To do this, I made several changes to the script (the updated version is available from GitHub).
Filtering for Licensed Accounts
The first change is to the filter used with the Get-MgUser cmdlet. The new filter selects only member accounts that have licenses. Previously, I selected all member accounts, but now we’re interested in chasing down underused licensed accounts. Here’s the command I used:
[Array]$Users = Get-MgUser -Filter "assignedLicenses/`$count ne 0 and userType eq 'Member'" -ConsistencyLevel eventual -CountVariable Records -All -Property signInActivity | Sort-Object DisplayName
The filter applied to Get-MgUser finds Azure AD member accounts with at least one license. The command also retrieves the values of the signInActivity property for each account. This property holds the date and time for an account’s last interactive and non-interactive sign-ins. Here’s what the data for an account looks like:
The last non-interactive sign-in might be something like a synchronization operation performed by the OneDrive sync client. I’m not too interested in these sign-in activities as I want to know about licensed accounts that aren’t taking full advantage of their expensive licenses. Hence, we focus on the timestamp for the last interactive sign-in.
Calculating How Long Since an Account Sign-in
To detect an underused account, we need to define how to recognize such an account. To keep things simple, I define an underused account as being more that hasn’t signed in interactively for over 60 days. An account in this category costs $23/month if it holds an Office 365 E3 license while one assigned an E5 license costs $38/month. And that’s not taking any add-on licenses into account. At $30/month, we’ve already paid $60 for an underused account when it matches our criterion.
The script checks to see if any Azure AD sign-in information is available for the account (i.e., the account has signed in at least once). If it does, we extract the timestamp for the last interactive sign-in and compute how many days it is since that time. If not, we mark the account appropriately.
# Calculate how long it's been since someone signed in
If ([string]::IsNullOrWhiteSpace($User.SignInActivity.LastSignInDateTime) -eq $False) {
[datetime]$LastSignInDate = $User.SignInActivity.LastSignInDateTime
$DaysSinceLastSignIn = ($CreationDate - $LastSignInDate).Days
$LastAccess = Get-Date($User.SignInActivity.LastSignInDateTime) -format g
If ($DaysSinceLastSignIn -gt 60) { $UnusedAccountWarning = ("Account unused for {0} days - check!" -f $DaysSinceLastSignIn) }
}
Else {
$DaysSinceLastSignIn = "Unknown"
$UnusedAccountWarning = ("Unknown last sign-in for account")
$LastAccess = "Unknown"
}
Note that it can take a couple of minutes before Azure AD updates the last interactive timestamp for an account. This is likely due to caching and the need to preserve service resources.
Reporting Underused Accounts
The last change is to the output routine where the script now reports the percentage of underused accounts that it finds. Obviously, it’s not ideal if this number is more than a few percent.
I usually pipe the output of reports to the Out-GridView cmdlet to check the data. Figure 1 shows the output from my tenant. Several underused accounts are identified, which is what I expect given the testing and non-production usage pattern within the tenant. Another advantage of Out-GridView is that it’s easy to sort the information to focus in on problem items as seen here.
Figure 1: Highlighting underused accounts with licenses
Customizing the Output
Seeing that the script is PowerShell, it’s easy to adjust the code to meet the requirements of an organization. Some, for instance, might have a higher tolerance level before they consider an account underutilized and some might be more restrictive. Some might like to split the report up into departments and send the underused accounts found for each department to its manager for review. It’s PowerShell, so go crazy and make the data work for you.
Insight like this doesn’t come easily. You’ve got to know the technology and understand how to look behind the scenes. Benefit from the knowledge and experience of the Office 365 for IT Pros team by subscribing to the best eBook covering Office 365 and the wider Microsoft 365 ecosystem.
I got this error: The term ‘Get-MgSubscribedSku’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify
that the path is correct and try again.
Hello, i test the Script an it looks very good. Problem that the Report is show in ROW “Licence” only Comma and no Licence Names Info. On Disabled Plans i see entrys like “VIVA_LEARNING”.
{"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}
I got this error: The term ‘Get-MgSubscribedSku’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify
that the path is correct and try again.
Did you connect to the Graph SDK by running:
Connect-MgGraph
Select-MgProfile Beta
Those lines are in the script…
Hello, i test the Script an it looks very good. Problem that the Report is show in ROW “Licence” only Comma and no Licence Names Info. On Disabled Plans i see entrys like “VIVA_LEARNING”.
Did you create the CSV files with the expanded license information as per the comments in the script?