A common problem when creating just about any resource in Active Directory, Exchange Online, Azure AD, SharePoint, etc. is life cycle. I see so many customers that have no control over their distribution groups. This is a script that you can use to determine which if your distribution groups are in use, and which are not, by looking at the message trace for the last 90 days.
The script uses the Start-HistoricalSearch available in Exchange Online only, to look for messages sent to your distribution groups. It then waits for the historical search to finish (can take hours…), before it checks the result and creates a new list of distribution groups with the “inuse” attribute set to true or false depending on the historical search result.
install-module ExchangeOnlineManagement
import-module ExchangeOnlineManagement
Connect-ExchangeOnline
$VerbosePreference = "Continue"
# Get all DGs and historical searches
$DistributionGroups = Get-DistributionGroup -ResultSize Unlimited
$HistoricalSearches = Get-HistoricalSearch -ResultSize Unlimited | Sort-Object SubmitDate -Descending
# For each DG, ensure a historical search is started
$SearchesToProcess = $DistributionGroups |
ForEach-Object {
Write-Verbose "Processing $($_.PrimarySMTPAddress) ($($_.Guid))"
$EmailAddresses = $_.EmailAddresses | Where-Object {$_ -like "smtp:*"} | ForEach-Object {$_ -replace "smtp:",""}
$ReportTitle = "Distribution group mapping - $($_.Guid)"
$HistoricalSearch = $HistoricalSearches | Where-Object ReportTitle -eq $ReportTitle | Select-Object -First 1
if($HistoricalSearch) {
Write-Verbose "Found existing historical search '$ReportTitle' with submit date $($HistoricalSearch.SubmitDate)"
if($HistoricalSearch.SubmitDate -lt (Get-Date).AddDays(-7)) {
Write-Verbose "Existing historical search '$ReportTitle' found, but it is more than 7 days old - starting again"
Start-HistoricalSearch -RecipientAddress $EmailAddresses -StartDate (Get-Date).AddDays(-90) -EndDate (Get-Date) -ReportType MessageTrace -ReportTitle $ReportTitle
} else {
$HistoricalSearch
}
} else {
Write-Verbose "No existing historical search '$ReportTitle' found, creating a new one"
Start-HistoricalSearch -RecipientAddress $EmailAddresses -StartDate (Get-Date).AddDays(-90) -EndDate (Get-Date) -ReportType MessageTrace -ReportTitle $ReportTitle
}
}
# Wait for all searches to complete
$Percent = 0
while($SearchesToProcess.ReportStatusDescription -contains "Pending") {
Write-Verbose "Waiting for historical searches to complete... $Percent %"
Start-Sleep 60
# Refresh SearchesToProcess variable
$SearchesToProcess = $SearchesToProcess | Get-HistoricalSearch
$Percent = $SearchesToProcess |
ForEach-Object {
if($_.ReportStatusDescription -eq "Pending") {
0
} else {
100
}
} |
Measure-Object -Average |
Select-Object -ExpandProperty Average
$SearchesToProcess |
Select-Object ReportTitle, Status, ReportStatusDescription, JobProgress, @{Label="EstimatedCompletionTime";Expression={$_.EstimatedCompletionTime.ToLocalTime()}} |
Format-Table
}
# Find any result that has more than 0 rows - these are for DGs that has received emails!
$DistributionGroupReport = $SearchesToProcess |
ForEach-Object {
if($_.Status -ne "Done") {
Write-Host "Job '$($_.JobId)' has status $($_.Status)"
} else {
$Guid = $_.ReportTitle -split " - " | Select-Object -Last 1
$DG = $DistributionGroups | Where-Object Guid -eq $Guid
[PSCustomObject] @{
DisplayName = $DG.DisplayName
GroupType = $DG.GroupType
PrimarySmtpAddress = $DG.PrimarySMTPAddress
Name = $DG.Name
Guid = $Guid
InUse = $_.Rows -gt 0
}
}
}
# All distribution groups not in use
$DistributionGroupReport | Where-Object InUse -eq $false | Format-Table
Best of luck!
Good Workaround! 
Nice script, just an FYI that for large environments the script will hit the limit for Start-HistoricalSearch and then start erroring out.
Looking at MS doco looks like there is a limit of 250 searches per 24 hours
https://docs.microsoft.com/en-us/powershell/module/exchange/start-historicalsearch?view=exchange-ps#:~:text=There%20is%20a%20limit%20of,of%2050000%20results%20or%20lines.
Hi, thanks, yes you are correct. I had this at a customer actually, and the script is made so that you should be able to simply run it the nnext day and it will create historical searches for 250 more 🙂
This script export the results to some path on the storage?
It is not very clear to me what it does with the results and how does the limit mentioned in a previous comment work … after the limit is reached I need to wait 24 hours and run the script again?
Hi, yes, correct. Microsoft has limits serverside on how many queries you can send, requesting historical email traffic. If you reach the limit, wait 24 hours and run the script again. 🙂
Hi,
Where’s the report located? I don’t see the path in the script?
The example outputs to screen using Format-Table, but change it to Export-CSV -Path ~\desktop\report.csv 🙂
I made the change on line 82 to: $DistributionGroupReport | Where-Object InUse -eq $false | Export-CSV -Path ~\desktop\report.csv
This is what I get for screen report (example)
ReportTitle Status ReportStatusDescription JobProgress EstimatedCompletionTime
———– —— ———————– ———– ———————–
Distribution group mapping – 4109wf5c-7e73-49cd-b980-83db0d44b357 Done Complete – Ready for download
Distribution group mapping – aa3ae082-62bd-4658-88e2-8a7f91035968 Done Complete – No results found
This is what I get in the report.csv file:
DisplayName GroupType PrimarySmtpAddress Name Guid InUse
Group Name1 Universal groupname1@domain.com GroupName1 4109wf5c-7e73-49cd-b980-83db0d44b357 FALSE
Group Name2 Universal groupname2@domain.com GroupName2 aa3ae082-62bd-4658-88e2-8a7f91035968 FALSE
Why not matching up?
Also, after the script fails due to 250 hard limit, how does it know where to pickup if I run it 24 hours later?
What happened to my comment?
Genius!
I edited the script slightly to report in-use groups, and to include a count of the number of rows:
Count = $_.Rows
Works like a charm!
When I run the script 24 hours later, I return the same results as the previous day. Is it possible to amend the script to import a csv specifying 250 DLs for each run. I have tried the following in place of Line 10
$DistributionGroups = Import-Csv C:\PS\Exchange\DistributionGroupsListTest.csv.
However, the script errors out with the following
“You must specify at least a message ID, a sender, or a recipient.
+ CategoryInfo : InvalidOperation: (:) [Start-HistoricalSearch], ArgumentException
+ FullyQualifiedErrorId : [Server=BL3PR04MB8076,RequestId=4a4e3753-1ea8-42d2-8933-fc5fffcea7c1,TimeStamp=1/30/2022 7:09:29 PM] [FailureCategory=Cmdlet-ArgumentException] 4EF696B5,Microsoft.Exchange.Management.FfoReporting.StartHistoricalSearch
+ PSComputerName : outlook.office365.com”
I am having the same problem, the historical searches are gone 24 hours later, so it runs new ones and only does the first 250 DLs again unfortunately.
Hi, I have the same problem with historical searches repeating after only initially completing the 250 DLs
The author does not seem to be reading these comments since I asked a similar question in Dec 2021. Here are some other script ideas I found:
https://community.spiceworks.com/topic/2252563-trace-in-active-distribution-list-in-o365-hybrid-environment
http://sigkillit.com/2016/03/03/determine-if-distribution-group-is-being-used-in-365-exchange/#admired-top
https://github.com/ascivv/DLActivityScripts
Don’t know if anyone is still looking at this thread, but I put together a little script to get around this issue:
https://github.com/orchardl/DLUsage
It’s not the cleanest, and I had to basically compile the separate commands that I originally did in the shell to make this script (in other words, I’ve only tested this once haha), so please let me know if it needs an edit:)
thanks for sharing, I am testing it right now. I have more than 3400 DLs so it might take a couple weeks
I have over 300 DLs…. made some modifications to the script to use the primary SMTP address instead of the secondaries as well.. I was getting errors.
“Invalid notification address or notification address not in accepted domains”
sorry i have over 3000 not 300 :-0