How to Find and Report Inactive Distribution Lists

Looking for Underused Distribution Lists

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.