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!

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

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 )

Google photo

You are commenting using your Google 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