As an Exchange Administrator, have you ever wondered if all those Distribution Groups are actually being used? Organizations running Microsoft Exchange Server are likely to have been running Exchange for a at least a few years and also likely to continue to do so for a while. After all those years, more and more distribution groups get created, some of them stop being used, some are simply forgotten, etc.
Maybe you got asked by the Audit department or by your manager for a list of the 20 most utilized groups, or maybe you are just curious. The good news is that as long as you have Message Tracking Logs enabled, you can easily get this information!
The easiest way to track messages sent to distribution groups is to list all the expansion events. When a user sends an email to a group, Exchange needs to expand that group in order to know who to send the email to. This gets registered with an EventID of EXPAND. Additionally, the RelatedRecipientAddress field in the EXPAND entry contains the PrimarySmtpAddress of the expanded group. And this is pretty much all the information we need.
Using the following cmdlet, we can get a list of all the emails sent to distribution groups on the 1st of January 2016:
Get-MessageTrackingLog -Start 01/01/2016 –End 01/02/2016 -EventID Expand | Select Timestamp, RelatedRecipientAddress, MessageSubject
But what we really want is to know how many emails were sent to which groups during a particular time. Not a problem! All we need to do is tweak the cmdlet above and add Group-Object:
Get-MessageTrackingLog -Start 01/01/2016 –End 01/02/2016 -EventID Expand | Group RelatedRecipientAddress | Sort Count –Descending | Select Count, Name
Do not forget to add Get-TransportService (or Get-TransportServer in Exchange 2007 and 2010) to get the logs across all transport servers if you have more than one!
If you are interested in getting the Top 20 most used groups, for example, this is also very easy:
Get-TransportService | Get-MessageTrackingLog -ResultSize Unlimited -Start 01/01/2016 -EventID Expand | Group RelatedRecipientAddress | Sort Count -Descending | Select -First 20 | FT Count, Name -AutoSize