Checking Exchange Online Distribution List Activity Over 90 Days

A Better Check for Unused Distribution Lists Than a 10-day Lookback

A recent article explaining how to use historical message trace data to create an inbound email report for the last 90 days sparked an idea about how to improve checking for inactive distribution lists and clean up the directory

As explained in this article, using online message trace data limits the check to the last ten days and that’s probably not enough in some circumstances. For example, a distribution list might be used just once or twice a month for important communications. A ten-day lookback will consider the distribution list to be inactive if it isn’t used in that window. Further checks should prevent the deletion of the distribution list but an automated process might remove it.

Going back ninety days to check activity is a different matter. If a distribution list remains unused for three months, it’s probably a good candidate for removal. Let’s discuss how to implement the check.

Retrieving Historical Message Trace Data for Distribution Lists

As a quick refresh, we know that Exchange Online holds message trace data online for only ten days. After this, Exchange Online moves the message trace data to colder long-term Azure storage. Historical message trace searches initiated from the Exchange admin center or using the Start-HistoricalSearch cmdlet launch background search jobs to access the Azure storage and retrieve the requested data, which administrators can then download as CSV files.

In the article to build an inbuild email report for a tenant, I explain how to use multiple search jobs to fetch message trace data before combining the data to generate the report. This technique is necessary to avoid exceeding limits for historical search jobs, like the maximum of 100 email addresses a job can process. This is obviously a problem when dealing with mailboxes because to generate a report for a complete tenant, you must fetch message trace data for every mailbox, and that means splitting up mailboxes in batches of 100 to retrieve the data.

The lower number of distribution lists (usually) means that fewer historical search jobs are needed to fetch message trace data. For instance, if a tenant has 100 distribution lists or fewer, all the data needed can be fetched using a single historical search job, Here’s how to create and submit the job with PowerShell:

[array]$DLs = Get-DistributionGroup -ResultSize Unlimited
[array]$DLRecipientAddresses = $DLs.PrimarySMTPAddress
$StartDate = (Get-Date).AddDays(-90)
$ReportName = ("DL Historical Search from {0} Submitted {1}" -f $StartDate, (Get-Date -format g))

$Status = Start-HistoricalSearch -RecipientAddress $DLRecipientAddresses -StartDate $StartDate -EndDate (Get-Date) -ReportType MessageTrace -ReportTitle $ReportName -Direction Sent -NotifyAddress Jay.Redmond@office365itpros.com

Microsoft 365 runs the historical searches in the background and the results might take some time before the results are available for download. It’s time for a coffee. After the jobs finish, download the files to a folder for processing (I use c:\temp\).

Processing Historical Message Trace Data for Distribution Lists

The downloaded message trace data holds records for messages sent to distribution lists over the last 90 days. Using a PowerShell script, the steps to process the data to figure out if distribution lists are active goes something like this:

• Process the downloaded data to find entries relating to distribution lists and extract that information to an array. A message trace record can be for a message sent to multiple recipients, so it’s necessary to check each recipient to detect when a record relates to a distribution list.
• For each distribution list, check its primary SMTP address against the array of message trace data and select the record with the most recent timestamp.
• Report what’s found for a distribution list. Both conditions are covered – either the code finds a message trace record for a list or it doesn’t.
• Generate the output (a CSV file) and output some statistics:

No messages found for distribution list Users External Email Monitoring
No messages found for distribution list Users Who Don't Use MyAnalytics
No messages found for distribution list Vice Presidents
No messages found for distribution list VIP Users
Found message for Distribution list Yammer Development at 28/10/2023 15:56

Total distribution lists checked:     81
Active distribution lists:            7
Percentage active distribution lists: 8.64%
Inactive distribution lists:          74

Figure 1 shows some of the information collected about distribution lists. The records at the top have timestamps showing when message trace noted the delivery of a message sent to the distribution list as it passed through the Exchange Online transport service. If the timestamp is “N/A,” it means that no message trace record can be found for that distribution list, so we can conclude that no one has sent a message to that distribution list in the last 90 days.

My code is available from GitHub. Feel free to improve the script!

No Magic, Just Data

There’s no rocket science here. It’s a matter of using data captured by Exchange Online that’s available for analysis. The only magic is some PowerShell and a little bit of lateral thinking about how to prove when distribution lists are in active use.

---

Support the work of the Office 365 for IT Pros team by subscribing to the Office 365 for IT Pros eBook. Your support pays for the time we need to track, analyze, and document the changing world of Microsoft 365 and Office 365.