How to Find and Report Inactive Distribution Lists

Find inactive distribution lists in Exchange Online

Find Inactive Distribution Lists Using Message Trace Data

Updated 28 October 2023

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 the Groups chapter, 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 Nova offer good answers, but not everyone wants to pay for the power and sophistication of a full-blown reporting product.

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 Microsoft 365 data repositories and kept there for an extra 80 days. If you want to search back further than 10 days, Exchange Online can run historical searches in the background and return CSV files 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. The code below is different to the current version, but it’s close enough to demonstrate the idea:

$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"
   $PageOfMessages = (Get-MessageTrace -Status Expanded -PageSize 5000 -Page $Page -StartDate $StartDate -EndDate $EndDate | Select-Object Received, RecipientAddress)
   $Messages += $PageOfMessages
Until ($PageOfMessages -eq $Null)
# Build an array of email addresses found in the message trace data
$MessageTable = @{}
$Messagetable = ($Messages | Sort-Object RecipientAddress -Unique | Select-Object RecipientAddress, Received)
# Now get the DLs and check the email address of each against the table
[array]$DLs = Get-DistributionGroup -ResultSize Unlimited
Write-Host ("Processing {0} distribution lists..." -f $DLs.count)
$Results = ForEach ($DL in $DLs) {
   If ($MessageTable -Match $DL.PrimarySMTPAddress) {
     [pscustomobject]@{Name = $DL.DisplayName ; Active = "Yes"}
     Write-Host ("{0} is active" -f $DL.DisplayName) -Foregroundcolor Yellow }
   Else {
     [pscustomobject]@{Name = $DL.DisplayName ; Active = "No"}
     Write-Host ("{0} is inactive" -f $DL.DisplayName) -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.

You can download the current version of the script from GitHub. The latest iteration updates the custom attribute 15 of any DL deemed to be active with the date and time of the last message sent to the DL.

For more information about distribution lists, see the Groups chapter in the Office 365 for IT Pros eBook. The Mail Flow chapter is the right place to go for information about how to run a message trace.

11 Replies to “How to Find and Report Inactive Distribution Lists”

  1. 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.

      1. Hi Tony, thank you for your comment.
        So, are you saying that there is no way to identify DLs used in BCC?

      2. 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…

    1. I don’t think this is true Mike. I just did a quick test and the message tracking logs show my email to my DG (in BCC) being expanded ๐Ÿ™‚ Shame I can’t attach a screenshot. If this was the case, it would be a major flaw in my opinion.

  2. 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.

    1. 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.

  3. Thanks as always!

    I had the same issue where we needed to understand if DGs were being used or not in order to do a clean-up. So, I wrote a script (in part similar to yours) to get several stats about DGs, like emails sent/received, owners, members, send-as/on-behalf permissions, etc.

    The script writes these to a SharePoint List (if itโ€™s a new DG, it creates an entry for the DG; if itโ€™s an existing one, it updates the stats.

    For it to be accurate, one of the fields in the list is when the stats were updated for each individual DG. This way, the next time the script runs, it checks the emails sent/received for that DG from that exact date and time.

    Because stats are in a SharePoint list, itโ€™s extremely easy to create a nice Power BI report with these stats ๐Ÿ˜Š

    The downside is that it is quite slow (2-3 seconds per DG without jobs), so I canโ€™t run it in Azure Automation โ˜น But it is able to process every DG every 3-4 days or so.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.