Are Distribution Groups really being used?

by Bharat Suneja on February 19, 2009

Over the years, you end up creating a large number of Distribution Groups based on user demands. The regular departmental Distribution Groups such as Sales, Marketing, Engineering, and HR. The geographical ones such as AllUS, All-California, All-BayArea, and so on. The ones by employment status such as All-FTE for full-time employees, All-Contractors, and so on. And ones to facilitate the working habits of executives and senior managers, who want to address their team with a distro (geekspeak for Distribution Group) like JoeSchmoe-DirectReports. Then there are the more interesting ones, such as All-MountainClimbers, All-GrungeFans.

Why are so many of these Distribution Groups prefixed with an All-? Can Distribution Groups ever be All-Whatever? Is it possible to include all grunge fans in the All-GrungeFans group? Or only the ones who confess? Can you guarantee everyone in the Sales dept will be included in the All-Sales group by default— even if you used Dynamic Distribution Groups? There will be times when someone does not populate the department attribute for the newly hired Manager of Inside Sales for Timbuktu, and surrounding areas. After two weeks in his exciting new inside sales position, the poor bloke finds out he hasn’t received the number of sales leads freely flying around on the distro, and unfortunately won’t be able to meet his targets for selling surfboards in Timbuktu that quarter.

Over the lifetime of Exchange deployments, there will be groups that get used more frequently, such as Send-Your-Jokes-Here-If-You-Have-Nothing-Better-To-Do-At-Work (the alias conveniently shortened to ExecTalk… ), or the ones that never get used, such as All-ExEmployees (hard as it is to believe, at least one of these two have been spotted in real-world deployments!).

One fine day, your friendly manager/auditor/HR person shows up at your desk wanting to know which distribution groups are in use.

That’s where message tracking logs come to the rescue— assuming these are enabled. If you’ve been mucking around with these logs in Exchange 2007, you probably know a fair bit of PowerShell, and chances are you’re absolutely loving it! If not, head over to previous post Exchange Server 2007: Message Tracking from the command line, and get to know the wonderful cmdlet Get-MessageTrackingLog.

Tracking messages sent to Distribution Groups

How do we get a list of messages sent to Distribution Groups? By getting a list of all Distribution Group expansion events, noted in message tracking logs with the EventID EXPAND. The RelatedRecipientAddress field in the EXPAND entry contains the PrimarySmtpAddress of the Distribution Group expanded. Use the following command to grab a list. You can restrain Get-MessageTrackingLog cmdlet in a number of ways. Since these have been covered in the previous post, I won’t go into details here.

Get-MessageTrackingLog -Start 2/1/2009 -EventID Expand | ft Timestamp,RelatedRecipientAddress -Autosize

You get back a table that looks something like this:

Timestamp RelatedRecipientAddress
——— ———————–
2/18/2009 4:36:27 PM [email protected]
2/18/2009 4:41:18 PM [email protected]

Next, how do we determine how many messages each Distribution Group received? This is easily done by piping the results to the Group-Object cmdlet:

Get-MessageTrackingLog -Start 2/1/2009 -EventId Expand | group-object RelatedRecipientAddress | ft Name,Count -Autosize

This returns a count for each group of messages:

Name Count
—- —–
[email protected] 123
[email protected] 145

To list messages sent to a particular Distribution Group:

Get-MessageTrackingLog -EventID Expand | ? {$_.RelatedRecipientAddress -like “[email protected]”} | ft Timestamp,Sender,MessageSubject -Autosize

Of course, you could use the message tracking GUI in EMC (or EAC in Exchange 2013) — but would it rate anywhere close on your geek satisfaction index?

{ 7 comments… read them below or add one }

Michael Dragone February 19, 2009 at 11:49 am

My favorite is when someone asks for a distro that has already been created. It goes like this:

Them: “Hey, can we set up an Outlook list for the Northwest Sales guys?”
Us: “That was created 11 months ago. X, Y and Z are members of it.”
Them: “Oh.”

Reply

Amadain February 24, 2009 at 11:36 am

This is incredible information, I’ve been trying to do this for at least 2 weeks now. Unfortunately, if I try to export these results to a csv, I just get the ClassID for the objects, and not the displayed results. I’ve seen postings on translating the SystemString values, but when I change the verbage in this solution to select-object and parse the values, the CSV just contains every email in my date range.

Reply

Amadain February 24, 2009 at 1:14 pm

Found my answer, but now I’m running into a limit of 500 records when I export, even when using the -resultsize unlimited tag. Is this built in?

Reply

Anonymous October 23, 2009 at 1:26 pm

good info!
However,…… it only displays *utilized* DLs without displaying the DLs with "zero" utilization. The "orphan" DLs are a big problem.

Did anyone get the script to show the "zero" utilization groups as well?

DM

Reply

Bharat Suneja October 23, 2009 at 1:38 pm

@Michael Dragone: Exchange 2010's self-management features should help with that. :)

Reply

Bharat Suneja October 23, 2009 at 1:41 pm

@Anonymous from Oct. 23, 2009: Yes, and you can dump all distribution lists using get-distributiongroup, then do a compare.

Alternatively, you can script the process to dump all distribution groups, and then remove the uses DL's from it, leaving you with a list of unused DLs. Will write something up if/when I get time.

Reply

Balasaheb Sherkar January 16, 2014 at 12:06 am

i have question about this EXPAND event id
EXPAND – A distribution group was expanded.
What exactly “A distribution group was expanded” it means ?
We get a list of messages sent to Distribution Groups and What about sent by Distribution Group ?

Reply

Leave a Comment

Previous post:

Next post: