Exchange online Powershell clean up distribution list

V Dinh 1 Reputation point
2021-12-04T01:38:28.617+00:00

Hi,
I found the ps script below that helps determine with the distribution list can be cleaned up, but it seems like doesn't have the path where the report will be exported? Please advise, thank you!

$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

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,358 questions
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rich Matheisen 44,776 Reputation points
    2021-12-04T03:28:35.48+00:00

    Change the last line in the script: 

    #All distribution groups not in use
$DistributionGroupReport | Where-Object InUse -eq $false | Export-CSV x:\DIR\FILE.csv -NoTypeInfo

    When you post code please use the "Code Sample" editor. It's the 5th icon from the left on the Format Bar. The icon's graphic is "101 010".

    When you post code using the normal editor it mangles the code. It also makes it hard to separate text from code.

    0 comments
  2. Meghashree Vijaya 1 Reputation point
    2022-10-12T08:52:00.057+00:00

    What is the complete script to run to achive this

    0 comments
  3. Oz Oscroft 31 Reputation points
    2023-01-06T09:52:28.317+00:00

    Thanks for this. The principle is exactly what I'm looking for, but I'm not clear which elements I'm supposed to change for our tenant (if any), and what the complete script is (i.e. why there's some script in the code sample editor and some not). Please could you clarify as it would really help. Thanks.

    0 comments
  4. Rich Matheisen 44,776 Reputation points
    2023-01-06T20:53:29.527+00:00

    Your script would look like this: 

    $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 | Export-CSV x:\DIR\FILE.csv -NoTypeInfo

    To answer your question "why there's some script in the code sample editor and some not", it's because when you posted your question you didn't use the Code Sample editor. The "normal" editor recognized some of what you posted a code. It also mangled many of the "<dollarsign><period><underbar>" character sequences by removing the "<underbar>" character, and it also turned lies that begin with "#" into "headers" using different font characteristics, and italicized some of the text.

    0 comments