Script for determining unused distribution groups in Exchange Online

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!

12 thoughts on “Script for determining unused distribution groups in Exchange Online

    1. 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 πŸ™‚

  1. 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?

    1. 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. πŸ™‚

  2. 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?

  3. 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!

  4. 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”

  5. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s