Earlier this month, I was asked how to find inactive distribution lists in Exchange Online. We’re often asked questions about why such-and-such a topic isn’t covered in the Office 365 for IT Pros eBook all the time. Sometimes, our questioner is mistaken and the topic is covered (perhaps in a chapter that they don’t expect it to be) and sometimes we simply disagree and think that the topic doesn’t fit or isn’t worth covering. But sometimes we sit up and say “yeah, that should be in the book…” and promptly go to work.
I looked at chapter 7, which is where we cover distribution lists, and found that we had punted on the topic by recommending that people run a message trace to find whether anyone was sending messages to a list. That advice was correct, but we gave some practical example of how to approach the problem.
I took a look around the internet to see if anyone had come up with a good way to find inactive distribution lists and couldn’t come up with a good solution. Or at least, one that hadn’t been written years ago and perhaps needed some dusting off and recalibration against today’s Exchange Online. For example, many people assert that Exchange Online message traces can go back 30 days. They can’t. The limit used to be 7 days and it’s now 10. Commercial products like Quadrotech’s Radar Reports offer good answers, but not everyone wants to pay for the power and sophistication of a full-blown Office 365 reporting product (if you do, Radar Reports are the best around).
In any case, the solution described below is imperfect and needs more work to be a production-quality answer, but it lays the foundation for someone else to work out the bells and whistles.
A Prototype Solution
Exchange Online does not include a way to find inactive distribution lists, so we must create a solution to find and report these DLs with PowerShell. The key points to remember are:
A distribution list is active when people use it to address messages.
Evidence of distribution list activity can be found in the message tracking logs by running a message trace to find events noting the expansion of distribution list memberships.
Exchange Online keeps message tracking logs online for up to 10 days, after which the information is moved into Office 365 data repositories and kept there for an extra 80 days. If you want to search back further than 10 days, Office 365 performs the search in the background and returns a CSV file with the results. For the purpose of this exercise, online searches can only look back 10 days to find expansion events.
With these points in mind, we can write a script to collect expansion events from the message tracking logs for the last 10 days and store the results in a table. We can then check the distribution lists in the tenant against the table to discover if we find a match. If we do, we know that the distribution list was used in the last ten days. If not, it’s a candidate to be considered as an inactive DL. Apart from reporting each list as it is checked, the script also outputs the results to a CSV file.
$EndDate = Get-Date
$StartDate = $EndDate.AddDays(-10)
$Messages = $Null
#Office 365 returns pages of message trace data, so we must keep on asking for pages until no more remain
$Page = 1
Write-Host "Collecting message trace data for the last 10 days"
Do
{
$PageOfMessages = (Get-MessageTrace -Status Expanded -PageSize 5000 -Page $Page -StartDate $StartDate -EndDate $EndDate | Select Received, RecipientAddress)
$Page++
$Messages += $PageOfMessages
}
Until ($PageOfMessages -eq $Null)
# Build an array of email addresses found in the message trace data
$MessageTable = @{}
$Messagetable = ($Messages | Sort RecipientAddress -Unique | Select RecipientAddress, Received)
# Now get the DLs and check the email address of each against the table
$DLs = Get-DistributionGroup -ResultSize Unlimited
Write-Host "Processing" $DLs.Count "distribution lists..."
$Results = ForEach ($DL in $DLs) {
If ($MessageTable -Match $DL.PrimarySMTPAddress) {
[pscustomobject]@{Name = $DL.DisplayName ; Active = "Yes"}
Write-Host $DL.DisplayName "is active" -Foregroundcolor Yellow }
Else {
[pscustomobject]@{Name = $DL.DisplayName ; Active = "No"}
Write-Host $DL.DisplayName "inactive" -Foregroundcolor Red }
}
$Results | Export-CSV c:\Temp\ListofDLs.csv -NoTypeInformation
Given that message traces give us a limited ten-day window to find inactive distribution lists, this is not a practical technique for a production-quality solution. Nevertheless, the method gives us the basis to develop the technique further into something that might work. For instance, you could run a script every ten days and merge the results over a period of a few months to give a more precise view of inactive and active lists.
For more information about distribution lists, see Chapter 7 of Office 365 for IT Pros. Chapter 17 is the right place to go for information about how to run a message trace.
6 Replies to “How to Find and Report Inactive Distribution Lists”
I went thru this recently. I love the concept of this and this script looks good. However, I will be surprised if this script will show emails sent bcc. I think many users now send emails to distribution lists (especially big ones) bcc to avoid reply-to-all storms. Since DL’s in bcc’s are expanded at the client level before being sent, heavily used distribution lists may look unutilized. This is what I found. If there is a way to collect the bcc info, I loved to hear it. (I don’t think it can be done.) It says this script collects expansion events. If it picks up bcc, I will be impressed and surprised. I will let you know.
Hi Tony, thank you for your comment.
So, are you saying that there is no way to identify DLs used in BCC?
Loading...
I think my previous assertion was incorrect. I just tested by sending a message to a DL as a BCC recipient and the DL showed up as active. It’s a while since I looked at this code…
Loading...
Topics like this one make me wish for an open source scripting/reporting repo for M365. So people can put all their work together instead of everyone writing their own wheels.
Would be a fun one to contribute to, and borrow from.
The problem with PowerShell (and maybe a joy) is that every organization has its own standards for coding, error handling, etc. That’s why I concentrate on illustrating the principles and leave the implementation to those who need the code.
{"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 went thru this recently. I love the concept of this and this script looks good. However, I will be surprised if this script will show emails sent bcc. I think many users now send emails to distribution lists (especially big ones) bcc to avoid reply-to-all storms. Since DL’s in bcc’s are expanded at the client level before being sent, heavily used distribution lists may look unutilized. This is what I found. If there is a way to collect the bcc info, I loved to hear it. (I don’t think it can be done.) It says this script collects expansion events. If it picks up bcc, I will be impressed and surprised. I will let you know.
I think you’re right. The whole point of BCC is to remain invisible, so they don’t appear in the message data.
Hi Tony, thank you for your comment.
So, are you saying that there is no way to identify DLs used in BCC?
I think my previous assertion was incorrect. I just tested by sending a message to a DL as a BCC recipient and the DL showed up as active. It’s a while since I looked at this code…
Topics like this one make me wish for an open source scripting/reporting repo for M365. So people can put all their work together instead of everyone writing their own wheels.
Would be a fun one to contribute to, and borrow from.
The problem with PowerShell (and maybe a joy) is that every organization has its own standards for coding, error handling, etc. That’s why I concentrate on illustrating the principles and leave the implementation to those who need the code.