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.

Report showing details of activity for distribution lists extracted from message trace data.
Figure 1: Details of activity for distribution lists from message trace data

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.

10 Replies to “Checking Exchange Online Distribution List Activity Over 90 Days”

  1. Hi Redmon,

    I love this script! Great job.
    I do have a question to it – as there might be too many inactive DLs or some DLs can be inactively e-mail wise but still used in production.
    So my question would be if and how we can add this to a specific “place” – So is it possible to either browse the distributions list on a specific OU or maybe with a custom attribute?

    I tried both without it went through:
    Adding:
    $OU = *
    -Searchbase “OU”
    Get-Mailbox | ? {$_.PSObject.Properties | ? {$_.Name -like “ExtensionCustomAttribute*” -and $_.Value} }

    I hope you can help me out.
    Thanks in advance,

    Martin

    1. You can use a custom attribute.

      Set-DistributionGroup -Identity O365.Book.Authors -CustomAttribute1 ‘VIP’

      Get-DistributionGroup -Filter {CustomAttribute1 -eq ‘VIP’} | Format-Table DisplayName

      DisplayName
      ———–
      Office 365 Book Authors

  2. Hi Tony,

    This is a great script. I was able to change the criteria to target a specific “group” of DL’s since we have thousands in our environment. Using the following: Get-DistributionGroup -ResultSize Unlimited | where-object { $_.ExtensionCustomAttribute1 -eq “Sales” } . I was wondering if there is a way to add a count of how many messages were sent to each DL in the results. I think that is a much better indicator of whether a given DL is being utilized.

    1. Of course. Change:

      [array]$DLFound = $Report | Where-Object {$_.DLSMTP -eq $DL.PrimarySMTPAddress} | Sort-Object -Descending {$_.TimeStamp -as [datetime]} | `
      Select-Object -First 1

      to:

      [array]$DLFound = $Report | Where-Object {$_.DLSMTP -eq $DL.PrimarySMTPAddress} | Sort-Object -Descending {$_.TimeStamp -as [datetime]}

      and

      $DateLastMessage = (Get-Date $DLFound.TimeStamp -format g)

      to

      $DateLastMessage = (Get-Date $DLFound[0].TimeStamp -format g)

      and add the count to the report

      $ReportLine = [PSCustomObject]@{
      Timestamp = $DLFound.Timestamp
      Sender = $DLFound.Sender
      DLName = $DLFound.DLName
      DLSMTP = $DLFound.DLSMTP
      Subject = $DLFound.Subject
      NumMsg = $DLFound.count
      }

      1. Hi Tony,

        Thank you, updating the code now includes the count. One more request on the output. I noticed that the HTML report displays the output correctly. However, the .csv file that is generated is displaying “System.Object[]” in many of the fields. This seems to only happen on the rows where there are more than one DL returned as the results. Is there a way to correct that?

      2. It’s because you are returning multiple vaalues in an array. Change the references so that you report the first element in the array. For instnce, change from $DLFound.Timestamp to $DLFound[0].Timestamp.toString() and all should be fine.

  3. iamtmib@gmail.com
    — Comment or Message —
    Your article
    https://office365itpros.com/2023/12/05/distribution-list-check-90-days/
    in the section
    Retrieving Historical Message Trace Data for Distribution Lists
    When i attempt to run the power shell script to submit the search, it errors out quite quickly saying
    Write-ErrorMessage : |Microsoft.Exchange.Hygiene.Data.TransientDALException|The Hygiene DAL retried a transient condition the maximum number of times.
    At C:\Users\redacted\AppData\Local\Temp\tmpEXO_2hq0y1ia.tna\tmpEXO_2hq0y1ia.tna.psm1:1191 char:13
    + Write-ErrorMessage $ErrorObject
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [Start-HistoricalSearch], TransientDALException
    + FullyQualifiedErrorId : [Server=REDACTED,RequestId=REDACTED,TimeStamp=Wed, 27 Mar 2024 00:51:07 GMT],Write-ErrorMessage

    >>> You’ve run too many historical search jobs. Wait for a couple of hours and then retry.

  4. This script looks like exactly what I need. However, I’m receiving an error. “No historical message tracking logs to analyze – exiting”.. I see that in the script, but not sure what it means. Not sure what I might be doing wrong?

    [array]$DataFiles = Get-ChildItem -Path $DataFolder | Select-Object -ExpandProperty Name
    if (!($DataFiles)) {
    Write-Host “No historical message tracking logs to analyze – exiting”
    Break
    }

Leave a Reply

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