Distribution list housekeeping

PowerShell Help
1

Hi all,

Been scratching my head for a good while on trying to come up with a script that will tell me if a DL has been sent to in the last 30 days so that I can then email out to the DL asking if we can delete it from the system.

I’m querying against Exchange Online.

My logic of the script is:

  • Get all DLs of the system and save to CSV
  • Use Get-MessageTrace for the status of EXPANDED (because we know that this will mean its a DL group) and iterate through each email address and save to a CSV file
  • Compare the 2 CSVs and if the email address is not present in the search results then we know no email has been sent to it and can potentially be deleted.

The problem I have is that I think Exchange Online is throttling me so I can’t get a true list of what groups have been emailed to. I get the following response back when I increase the amount of searches I’m performing:

“We cannot currently process your request, please wait a few minutes and try again”

Eventually the script will move onto the next part.

My thoughts started to think whether its possible in PS to say something along the lines of “once I’ve searched for something@email.com and find 1 message that has been sent to it, move onto the next address to search” purely in the effort to reduce the number of queries I’m throwing at Exchange Online and make the overall script execution time lower.
I just need to know that the group has received at least 1 email to it proving that the DL is being used.

There are a number of blog posts out there on the subject of this but none seem to match what I’m looking to achieve. The script below is a kludge of scripts I’ve tweaked from.

Any guidance is much appreciated!

Simon

code:

Get-DistributionGroup -ResultSize Unlimited | Select-Object PrimarySmtpAddress | Sort-Object PrimarySmtpAddress | Export-Csv -Path C:\Powershell\DL-ALL.csv -NoTypeInformation

$index = 1
while($index -le 20)
{
$Batchfile = Get-MessageTrace -Status Expanded -StartDate $30days -EndDate $current -PageSize 5000 -Page $index | Sort-Object RecipientAddress | Group-Object RecipientAddress | Sort-Object Name | Select-Object @{Label="PrimarySmtpAddress";Expression={$_.Name}}, Count
$index ++
sleep 30
}

$Batchfile | Export-Csv -Path C:\Powershell\DL-Active.csv -NoTypeInformation




# Compare the CSV files and output to an inactive file


$a = Import-Csv C:\Powershell\DL-ALL.csv
$b = Import-Csv C:\Powershell\DL-Active.csv

Write-Host -BackgroundColor DarkBlue -ForegroundColor Green "Comparing DLs and outputting to CSV...."

Compare-Object $a $b -Property PrimarySmtpAddress | Where-Object {$_.SideIndicator -eq "<="} | Sort-Object PrimarySmtpAddress | Select-Object -Property PrimarySmtpAddress | Export-Csv -Path C:\Powershell\DL-Inactive.csv -NoTypeInformation


# Calculate and output on screen how many inactive DL's we've found


$c = $a.Count - $b.Count

Write-Host -BackgroundColor DarkBlue -ForegroundColor Green "In the last 30 days of searching through our message logs, we have $c unused DLs"
2

I don’t have any O365 distribution groups, all mine are on prem but try this replacing get-messagetrackinglog with get-messagetrace. You may want/need to modify to suit your output, but this basically is getting a count of the messages and if you have more than 1 it’s a used distribution group.

import-csv C:\scripts\Exchangescripts\dl.csv | 

foreach {

$msgcount = (Get-MessageTrackingLog -Recipients $_.primarysmtpaddress -Start 1/7/18 -End 1/8/18 |  Measure-Object).Count

 $propList = @{
                      "User"=$_.primarysmtpaddress
                      "Message Count"=$msgCount
                      }

                      New-Object PsCustomObject -Property $propList
    } | 

        Export-Csv C:\scripts\exchangescripts\NumberOfEmailMessagesReceived.csv -NoTypeInformation
3

You can use the -Resultsize Switch to Limit the results to 1. In my opinion you can do the whole Thing in a oneliner(or multiple for a nice output).

Get-DistributionGroup  |%  {if(-not $(Get-MessageTrackingLog -Recipients $_.WindowsEmailAddress -ResultSize 1)) {write-output $_.WindowsEmailAddress}}

You Need to adjust the commands to the O365 ones. And you don’t actually Need the Expand Event and two files. Just pick up the Destination addresses of your DLs and use them with get-messagetrace. Expand Comes after the Receive Event in Exchange.