How to find inactive distribution groups?

Introduction:

You might be in a scenario in which you have 2-300 Distribution Lists in your Exchange Online organization that have been created in the past 5-7-years or so, and you think that most of them are unused. Well, you might be right. Housekeeping?

Context:

Distribution Lists (DLs, or formerly known as Distribution Groups) get created often with every other project or team that forms, but whenever time comes for offboarding it seems no one remembers to remove them. This is how you get cluttered with DLs. Now.. is that a problem?
Well DLs don't consume licenses, don't cost to keep around, but.. they might be a trap for users trying to send an email to someone and instead they send it to some old DL, or maybe one day you decide to finally move to M365 Groups (Modern Groups) and thus you will have to recreate and restructure all DLs then. Better have fewer when that time comes. Also, imagine you don't do nothing and assuming DLs will still be around in the next 10 years, you'll end up having thousands instead of hundreds and then it's bad.

What would be our options:

Well, first thing that pops into head is O365 Usage Reports, but that's no bueno as it only shows you user (individual mailbox) reports:

Then you might say aaah, but what about Exchange Mail Trace? Good point, but the GUI seems very limited. It requires a sender, or recipient, or MessageID. And the options are the following:

How would you input 300 DLs here? Spoiler alert, you can't.

So then, we must turn to PowerShell? Yes! Now, on PowerShell, we have the option to go for a Historical Search, but looping through all DLs and starting Historical Searches for each and waiting for them to complete means we need to keep this thing running for 20-24hrs which is not ideal.. Network can hiccup, your PC can reboot. Instead what I propose is a simpler method, but also with some caveats.

The Solution:

Now here'a a simple script I wrote a while ago that loops through all DLs and starts a messagetrace. Remember, message traces can only go 10 days back in time, so you can't exceed that time range.

• The advantage of message traces is that it's an instant result.
• The disadvantage of message traces is that it's limited to 10 days, so ideally you would run this experiment 3 times (aka - each 10 days), and in ~1 month you should have a pretty good idea of what DLs are still active.

I would assume that if a DL has no communication sent to it in 1 month it's pretty much unused. Of course ideally the DLs should have an owner configured on them, and you could then consult with the owners for a final confirmation before actually deleting those unused DLs.

#Connect twice to the needed services - Exchange and MSOL
connect-exchangeonline
Connect-msolservice

#Fetch all Distribution Groups into a variable
$DLs=Get-DistributionGroup -ResultSize Unlimited | select -ExpandProperty primarysmtpaddress

#Create a new Object then loop through all DLs
$all=@()
Foreach ($DL in $DLs)
{
    $DLObject = new-object PSObject

    #Don't forget to change the dates format is US  MM/DD/YYYY, maximum 10 DAYS AGO!!!
    $LastMessageReceived = Get-MessageTrace -StartDate 05/02/2023 -EndDate 05/11/2023 -RecipientAddress $DL | sort Received | select -Last 1
    $DLObject | Add-Member -MemberType NoteProperty -Name 'DLEmail' -Value $Dl
  If($LastMessageReceived -ne $null )
  {
  #$dlobject | add-member  -membertype NoteProperty -name "LastMessageReceived" -Value $LastMessageReceived.Timestamp
  Write-Output yes
  $DLObject | add-member  -membertype NoteProperty -name "LastMessageReceived" -Value $LastMessageReceived.Received
  }
  Else
  {
  Write-Output no
  $dlobject | add-member  -membertype NoteProperty -name "LastMessageReceived" -Value 'No message reiceved in the last 10 days'
  }
  #DL owner and Members
  $owner = (Get-DistributionGroup $DL).managedby
  $members = Get-DistributionGroupmember $DL
  
     if($members -ne $null){
    $concatmembers= [system.String]::Join(";",$members.Name)
    }
    else
    {
    $concatmembers=""
    }
    

  $DisplayName = (Get-DistributionGroup $DL).Name
  $ID=(Get-DistributionGroup $DL).ExternalDirectoryObjectId
  $LastDirSyncTime=(Get-msolgroup -objectid $ID).LastDirSyncTime

  $DLObject | add-member  -membertype NoteProperty -name "Owner" -Value $owner
  $DLObject | add-member  -membertype NoteProperty -name "Members" -Value $concatmembers
  $DLObject | add-member  -membertype NoteProperty -name "SyncedOrNot" -Value $LastDirSyncTime
  $DLObject | add-member  -membertype NoteProperty -name "ObjID" -Value $ID
  $DLObject | add-member  -membertype NoteProperty -name "DisplayName" -Value $Displayname

  #Build the output with all of the above properties into this all object
  $all += $dlobject
    
}

#Export in CSV, make sure the path C:\temp exists
$all | Export-Csv -Encoding UTF8 -NoTypeInformation -Delimiter ";" -path "C:\temp\result 11-05-2023.csv"

What to expect from the script?

Well, it's not perfect, it will say a bunch of 'yes' and 'no' in the console based on if the DL received or not a message.
It will error without stopping for the DLs that don't have an object ID (ex. Room Lists)

Then the result will look like this:

And if we do some Data -> Text to column, delimited by ; and some nice formating we get to:

Which is a very usable output. I notice the DisplayName is crooked, don't know why. (and I frankly don't care since I have the email address)

Can this script be bettered? Of course! Will I do it? Don't know, probably not :D, since it's good enough as it is, but you're free to use it at your own risk, modify it, add parameters in the output or fix the errors.

Conclusion:

Use the above script to have a better understanding of which of your DLs received or didn't receive an email in the past 10 days. Repeat the process a couple of times and you'll have data over 1 month, then you can use that information to decide which DLs you keep and which you can bin.