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 Quest 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:
Connect-ExchangeOnline -ShowBanner:$false
$EndDate = Get-Date
$StartDate = $EndDate.AddDays(-10)
$Messages = $Null
# Exchange Online 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-Object 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-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.