To begin this PowerShell script I wrote only works with Exchange 2007 as it uses its commandlets which won't work with the basic PowerShell command window that you get in Vista, 7, 2008, or that you install for XP.  I can't confirm if Exchange 2010 uses the same command shell as 2007 or not so I can't comment with that environment.

Anyway, let's beginning ... Like any Exchange organization distribution lists (or groups) can easily grow out of control.  The number of distribution lists can easily hit the hundreds even for your small businesses.  There really isn't an easy way to determine whether that DL is being used or not.  Sure you can easily talk to the DL's owner (if configured), but if you are like my company you have 300+ distribution lists some with owners configured others not.  That can be a real pain in the butt, so I figured there had to be an easier way to achieve my goal to finally clean up stale/old/unused Exchange distribution lists.

After doing some research I thought the best way for my situation was to write a PowerShell script that will pull all distribution groups in my organization and then use that information to then query Exchange's Message Tracking Log and find when the last email received timestamp was.  If I see a DL with a last email received timestamp of five years ago I'm pretty safe to say it can be removed ... naturally if the group has an owner I would validate with that user before removing it.

Here's where we begin!  First we need to get the SMTP addresses for all the distribution groups in your Exchange 2007 organization.  To accomplish this we simply used the Exchange 2007 commandlet 'Get-DistributionGroup' and then use the attribute 'PrimarySmtpAddress' to get the correct email address that will be used later in a different script.  The script would look something like this:

Get-DistributionGroup | select PrimarySmtpAddress | Export-csv "C:\Projects\ExchangeDLCleanup\smtp.csv" -notype

With the above script the 'Get-DistributionGroup' queries all distribution groups, pretty simple with one command.  But if you just run that command you'll a ton of uses information; aliases, type of group, smtp, owner, etc...  This is why we pipe (|) that data set into a 'select' parameter.  In this script we're selecting the 'PrimarySmtpAddress' attribute of all distribution groups.  We finally pipe that filter data set for export using the 'Export-csv' command.

So we finally have a good list of all the SMTP address for each distribution group.  My next script will handle getting the last email received information.

$servers = "server01", "server02"
$outputfile = "C:\Projects\ExchangeDLCleanup\results.txt"

In my environment we happen to have two Hub Transport servers (for redundancy), so two servers have the ability to transport incoming email (internal or external) to our backend mailbox cluster.  So our script will have to query both Message Tracking Logs for each server.  I so declare my two front-end servers as '$servers'.  I also declare the path and what my output file will be with '$outputfile', this file will contain the results of our tracking log query.

$(foreach ($server in $servers) {Get-Content "H:\Projects\ExchangeDLCleanup\smtp.csv" | %{Get-MessageTrackingLog -ResultSize Unlimited -Server $server -Recipients $_ | select "ServerHostName", "Recipients", timestamp -Last 1}}) | Out-File $outputfile
 (should be all one line if your favorite code editor.)

Since we have two servers we want to query we'll need to do a 'foreach' command or statement, so that each SMTP in our SMTP.csv we created above it will search the last email received on both servers.  For us to get those SMTP address we'll have to use the 'Get-Content' command and point to the file my first script generated.

The 'Get-MessageTrackingLog' is the command we use to search Exchange 2007's Message Tracking Log, with that command we set a few parameters to make sure we filter the correct information.  In this script I use the '-ResultSize Unlimited' to make sure everything is available, the '-Server $server' simply tells the command what servers to use for the log search (which we declared at the beginning of the script.)  Then the '-Recipients $_' is telling to look at all the email addresses contained in our 'smtp.csv' file.

After that, we pipe all that information again and select attributes we're only interested in ... in this case we want to know the 'ServerHostName'; which is the name of the server transporting the message, the 'Recipients'; which is the email address the message is being sent too (which should be the DL in question), and finally look at the 'timestamp -last 1'; which grabs the date and time of the last email received.

To end things we pipe that information to our output file using the 'Out-File' commandlet.  If you open the file you should see the following as your result:

ServerHostname                  Recipients                               Timestamp
--------------                           ----------                                  ---------
server01                             {Everyone@company.com}     12/16/2009 1:54:21 PM

Of course this script is available for you to use in anyway shape of form ... but I do not take responsibility if something goes wrong.  As any administrator will tell you, test first and test again.  Any script examples I post is 'use at your own risk'.  Look at what the script is doing rather than copy, paste, and execute.  Don't know what a certain parameter or commandlet does - Google search :)  Thanks.