If you are an Exchange administrator you would most likely have hundreds of distribution lists in your organisation and no idea which ones are being used. Well I had this problem and decided to do something about it. I set out to see who was actually using these DL’s and remove all the ones that have not been used in months. I will be looking at Static DL’s. If you use Dynamic DL’s then the concept will be the same however some of the syntax may differ slightly.
High level overview of steps
Here are the steps I went through on a high level to identify and remove unused Distribution Lists in my Exchange 2010 SP1 environment:
- Increase Exchange MessageTrackingLogs from the default of 30 days to 90 days
- Export list of ALL distribution lists
- Export list of ALL active distribution lists based off Exchange Tracking Logs
- Compare the results and output the inactive DL’s
- Hide all unused DL’s from the GAL
- Delete all unused DL’s from Exchange
1. Configuring Exchange MessageTrackingLogs settings
Here are the settings i used to configure my logging on my server, named EXCH1. I decided to increase my logging from 30 days to 90 days based on my own requirements – you may need to go longer.
Notes:
- Note this is a server specific command and you need to do it to all your transport servers
- My 300 seat environment used 500MB per month of logs.
- Increasing the log will not remove the original logs
- You will have to wait 2 months after setting
- If you C:\ drive is short on space you can relocate the log path to a different local drive
The following command gets the logging information needed from server EXCH1
|
1 2 3 4 5 6 7 |
Get-TransportServer -Identity EXCH1 | fl *messagetracking* MessageTrackingLogEnabled : True MessageTrackingLogMaxAge : 30.00:00:00 MessageTrackingLogMaxDirectorySize : 1000 MB (1,048,576,000 bytes) MessageTrackingLogMaxFileSize : 10 MB (10,485,760 bytes) MessageTrackingLogPath : C:\Program Files\Microsoft\Exchange Server\V14\TransportRoles\Logs\MessageTracking MessageTrackingLogSubjectLoggingEnabled : True |
The following command sets the Log Directory size to 3GB
|
1 |
Set-TransportServer -Identity EXCH1 -MessageTrackingLogMaxDirectorySize 3000MB |
The following command sets the Max Age of logs from 30 days to 90 days
|
1 |
Set-TransportServer -Identity EXCH1 -MessageTrackingLogMaxAge 90.00:00:00 |
The following command gets the updated logging information needed from server EXCH1
|
1 2 3 4 5 6 7 |
Get-TransportServer -Identity EXCH1 | fl *messagetracking* MessageTrackingLogEnabled : True MessageTrackingLogMaxAge : 90.00:00:00 MessageTrackingLogMaxDirectorySize : 2.93 GB (3,145,728,000 bytes) MessageTrackingLogMaxFileSize : 10 MB (10,485,760 bytes) MessageTrackingLogPath : C:\Program Files\Microsoft\Exchange Server\V14\TransportRoles\Logs\MessageTracking MessageTrackingLogSubjectLoggingEnabled : True |
2. Export list of ALL distribution lists
To export ALL DL’s from your environment run the below command. This command will export the primary SMTP address from all DL’s and sort them alphabetically, and put them in a CSV file.
|
1 |
Get-DistributionGroup | Select-Object PrimarySMTPAddress | Sort-Object PrimarySMTPAddress | Export-CSV DL-ALL.csv -notype |
3. Export list of ALL active distribution lists based off Exchange Tracking Logs
To export all active DL’s from your server we need to look into the transport logs. We first fetch all event logs relating to the expansion of DL’s, then we sort them by RelatedRecipietAddress. Now that they are sorted we group them by RelatedRecipientAddress. From here we sort it alphabetically by the Name column, rename the Name column to PrimarySmtpAddress (so that it matches the column name of the DL-ALL.CSV file, then export the renamed Name column and the Count column to a CSV. Below is a command to do this:
|
1 |
Get-MessageTrackingLog -Server EXCH1 -EventId Expand -ResultSize Unlimited | Sort-Object RelatedRecipientAddress | Group-Object RelatedRecipientAddress | Sort-Object Name | Select-Object @{label="PrimarySmtpAddress";expression={$_.Name}}, Count | Export-CSV DL-Active.csv -notype |
Note: The count column simply displays how many emails were found being sent to the DL. You can sort that to tell you the most popular/least popular ones in your environment.
4. Compare the results and output the inactive DL’s
So initially I compared the output using Excel and VLookups (Yuk – I know) and then I remembered we can do soo many things in PowerShell! Well here I import two CSV’s that we generated previously, compare the two files and output the difference to a new file called DL-Inactive.csv.
|
1 2 3 |
$file1 = Import-CSV -Path "DL-ALL.csv" $file2 = Import-CSV -Path "DL-Active.csv" Compare-Object $file1 $file2 -Property PrimarySmtpAddress -SyncWindow 500 | Sort-Object PrimarySmtpAddress | Select-Object -Property PrimarySmtpAddress | Export-Csv DL-Inactive.csv -NoType |
5. Hide all unused DL’s from the Global Address List
So now you have a long list of distribution groups and you have confirmed with the business that all those DL’s are no longer used. Now you simply run the following command and it will mark all those DL’s as hidden. Immediately you have a sense of relief when this is done – you are truly on the path of cleaning up your Exchange environment!
The below script imports your now cleaned and checked DL-Inactive.csv file. From here we get each line, add a note saying it is now hidden (with a date) and hide it from the GAL using Set-DistributionGroup cmdlet.
<
pre class=””>
|
1 2 3 4 5 6 7 |
$a = Get-Date $notes = "$a - Hidden from address list due to inactive use." $inactiveDL = Import-CSV -Path "DL-Inactive.csv" | foreach-object { Set-Group -identity $_.PrimarySmtpAddress -notes $notes Set-DistributionGroup -identity $_.PrimarySmtpAddress -HiddenFromAddressListsEnabled $true } |
6. Actually delete these Distribution Groups
So time has passed and there are no HelpDesk calls asking for some missing DL’s. The cloud has settled and you are prepared to delete the DL’s. Well before you go and delete anything you have to remember that these groups (if they are security groups) could be used elsewhere. So because of this, all we are going to do is disable the mail capabilities from that group, and then add a note in their notes field that this was done and when. I recommend using extreme caution!
Below is the code:
<
pre class=””>
|
1 2 3 4 5 6 7 |
$a = Get-Date $notes = "$a - No longer Mail Enabled due to inactive use." $inactiveDL = Import-CSV -Path "DL-Inactive.csv" | foreach-object { Set-Group -identity $_.PrimarySmtpAddress -notes $notes Disable-DistributionGroup -identity $_.PrimarySmtpAddress -Confirm $false } |
There you have it. You are now left with a clean list of distribution lists that can be run periodically to determine if more cleaning is required. Your users will love it because all you are left with is up-to-date distribution lists that are current and up to date.
Last point – i have not converted this into one PS script yet, and do all the steps individually. When I do merge it all into the one, i will post it up here.
Resources:
- Configure Message Tracking
http://technet.microsoft.com/en-us/library/aa997984.aspx - Are Distribution Groups really being used?
http://exchangepedia.com/blog/2009/01/are-distribution-groups-really-being.html - compare csv on exact columns
http://www.experts-exchange.com/Programming/Languages/Scripting/Powershell/Q_26910135.html - Using the Compare-Object Cmdlet
http://technet.microsoft.com/en-us/library/ee156812.aspx - Compare two different csv files using PowerShell
http://blogs.technet.com/b/stefan_stranger/archive/2011/02/08/compare-two-different-csv-files-using-powershell.aspx - Tyler Gohl – correcting my poor syntax errors 🙂
Tyler Gohl
How about Dynamic Distribution Groups, because with my report it displayed all Dynamic distribution Groups
Might be a silly question, but this this also work when external people send to the internal distribution groups?
The report is based on both internal and external. The message trace will record any incoming emails to that email address.
p.s. no such thing as silly questions. 😉
Where is DL-Inactive2.csv even coming from? There are so many holes in this guide. Not that it matters, apparently all of my DLs are being used. Although some are only being hit 1-10 times in a 4 month period.
Hi Cody,
Im sorry that you feel there are holes in the guide. If you can help identify them for me I would be happy to amend the guide. I did try my best to be very specific to remove any confusion.
The file DL-Inactive2.csv is created under step 4. Its a compare between the two files which then creates the list in a new file.
Im not sure on the size of your environment, but hearing that you have every DL being hit is a good thing. It means that they are all being used by your users.
In our environment this was not the case, we had about 300 distribution groups of which about 30% were not being used.
Cheers,
Ivan
Hi Cody,
On further looking into this, i can see your confusion. DL-Inactive2.csv should actually be DL-Inactive.csv and it must have been renamed during my testing.
Regards,
Ivan
really digging your blog, already found a few key items my company is in the middle of. Thanks!
I get the below error when i try to hide the DLs – am i missing something ?
ForEach-Object : Cannot bind parameter ‘Process’. Cannot convert the “2013/09/20” value of type “System.String” to type
“System.Management.Automation.ScriptBlock”.
At C:\temp\HideDL.ps1:3 char:74
+ $inactiveDL = Import-CSV -Path “c:\temp\DL-Inactive.csv” | foreach-object <<<<
+ CategoryInfo : InvalidArgument: (:) [ForEach-Object], ParameterBindingException
+ FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.PowerShell.Commands.ForEachObjectCommand
Hi Biju,
Are you able to post up the first 3 lines of the 3 CSV files that were exported? (happy for you to rename parts of it to make it anonymous, so long as the structure is the same. It looks like you are exporting something strange. So example of DL-All.csv, DL-Active.csv and DL-Inactive.csv. I think there is an error in the way its being exported.
Cheers,
Ivan
Thanks for this Ivan! It really helped to clean up our address book. Is it possible to do the same thing for Mail Contacts?
Thanks,
Brad
Hi Brad,
Very glad you asked! I was just looking at my contacts yesterday thinking “this is terrible – I need to clear this out!”. There is no reason why you cant rework the commands to work on contacts. I will write up an article in the coming week once i get it to work for me.
Also glad your Address Book is cleaner now!
Ivan
Hi Ivan,
Can’t see how to add a screenshot in this reply box so I’ll try to explain.
I ran the script in Powershell ISE and when executed I receive a pop-up window with a title of:
cmdlet ForEach-Object at command pipeline position 2 – Supply values for the following parameters:
Then in the window there is an input box with a heading of Process[0]. Should these windows pop up and if so what values are they looking for?
The only change I have made to the syntax is to put the full path to DL-Inactive2.csv – “C:\scripts\DL-Inactive2.csv”
I hope this makes sense without a screenshot 🙂
Thanks,
Paul
Hi Ivan,
I’ve managed to work it out.
For some reason this code didn’t work:
$a = Get-Date
$notes = “$a – Hidden from address list due to inactive use.”
$inactiveDL = Import-CSV -Path “C:\scripts\DL-Inactive2.csv” | foreach-object
{
Set-Group -identity $_.PrimarySmtpAddress -notes $notes
Set-DistributionGroup -identity $_.PrimarySmtpAddress -HiddenFromAddressListsEnabled $true
}
But this did:
$a = Get-Date
$notes = “$a – Hidden from address list due to inactive use.”
$inactiveDL = Import-CSV -Path “C:\scripts\DL-Inactive2.csv” | foreach-object{
Set-Group -identity $_.PrimarySmtpAddress -notes $notes
Set-DistributionGroup -identity $_.PrimarySmtpAddress -HiddenFromAddressListsEnabled $true
}
It looks like powershell assumed I had finished with the ForEach-Object command due to the new line. Removing it fixed the problem and the script runs successfully.
Alternatively you can use a backtick escape at the end of the foreach:
$a = Get-Date
$notes = “$a – Hidden from address list due to inactive use.”
$inactiveDL = Import-CSV -Path “C:\scripts\DL-Inactive2.csv” | foreach-object`
{
Set-Group -identity $_.PrimarySmtpAddress -notes $notes
Set-DistributionGroup -identity $_.PrimarySmtpAddress -HiddenFromAddressListsEnabled $true
}
Hi Paul,
Sorry I didnt get a chance to check this until now – glad you resolved the issue though. I did not have the problem on my end when I wrote it.
Good old SYNTAX issue… 😐
Cheers,
Ivan
AT Step 5. should those commands be run from the EMS on the server or can they be run remotely? Also, should the commands be saved to a .ps1 file to run because when I run them from EMS I am presented with:
Process[3]: }
Hi Paul,
I would prefer a screenshot of your error but ill try and answer as best as I can:
No you don’t need to run it on the server, but you do need to have the Exchange module imported – So you can install EMS on your client and run it from there.
You dont need to save each command as a PS1 command, but when you paste the script you need to execute the code that spans multiple lines all at once. I recommend using Powershell ISE (bing it) and paste the code in there, then you can select the section of script you want to run and then press F8. Its a great tool to learn PowerShell too.
Did you update your DL-Inactive.csv with amendments and then rename it to DL-Inactive2.csv? the code has a different file name which could be causing your problem?
Cheers,
Ivan
I am currently performing a clean of inactive email groups in our environment and found this article.
Great article but I have hit a snag running the hide portion of the processes. The foreach-object stops and asks for input (see below from EMS), but I thought the input was already provided by the csv file.
Any ideas why?
Thanks,
Alison
cmdlet ForEach-Object at command pipeline position 2
Supply values for the following parameters:
Process[0]:
I got around this by running the two set- commands separately with their own import-csv and foreach-object command.
Not sure why the full process did not work for me though, so still interested in finding out why. Thanks
Hi Alison,
I have had a look and could not see why it would not work when run as one script (note I did not test it yet) but based on the code I dont think it should have a problem running. Can you email me a direct screenshot of the error you are getting along with the code you are using?
Also I suspect you renamed “DL-Inactive2.csv” to “DL-Inactive.csv”?
Regards,
Ivan (ivan< at >dretvic.com)
I tested it today against my 2 servers successfully. Adding that get-transportservers will work for 1 or 50 servers.
Hi Todd,
Thanks for the suggestion. Glad its easily expandable.
to get message tracking logs for all transport servers:
Get-transportServer | Get-MessageTrackingLog-EventId Expand -ResultSize Unlimited | Sort-Object RelatedRecipientAddress | Group-Object RelatedRecipientAddress | Sort-Object Name | Select-Object @{label=”PrimarySmtpAddress”;expression={$_.Name}}, Count | Export-CSV DL-Active.csv -notype
Hi Todd,
Thanks for supplying the command – I suppose that will be able to plug straight in and work with my script. I cant test it in my environment unfortunately.
Ivan
It is Get-transportServer without ‘s’ , took a time to understand why command not recognized
Thanks – nicely spotted. 🙂
Thanks for the post.Really looking forward to read more. Really Cool.
Hi Maverick, Thank you for your kind comments. More will come as soon as I get more time! I promise!
Ivan
Very Nice info, It saved us to invest few $$ for third party tool :).
Hi Anil,
Glad to hear the info helped you. Just curious what were your overall objectives you wanted to achieve? just clean up distribution lists?
Cheers,
Ivan
Thanks again Ivan.
I had issues with groups I know received messages during the time checked, but showed up as inactive in the tracking logs (no expand events).
When using RECEIVE as the filter for checking activity some of these groups do show up as active.
Additionally some groups seemed to be inactive as all of their members’ mailboxes are on a different mailbox/hub then the one queried, which I had not taken into account.
When starting to weed out the stale groups I thought this would be much more straight forward… your methods proved to be a great help though 🙂
Hi Joe,
Lets try and break this down a bit:
Do you mean they show as inactive?
The logs you are looking at is a RAW log, and if the event is not there is may be caused by some other reason. I am not sure I can help you there without more thorough investigation.
This is as expected. The reason for this is the script you run in Step 3 is explicitly running on a particular server. I have a one server environment personally (I know it disappoints me too) so I only search the one server. To search multiple servers you would need to alter the code to do the following:
My skills are not superior enough to do this quickly and would need to test it – it will be on my hit list when im allowed to create a DAG in my environment. If you, like me are not able to easily write the script then run the command on each server and create seperate CSV files. From there open up excel and consolidate/sort the files. Then you can carry on with the rest of the steps and it should work accordingly.
(I have not tested this and need to think about the results that are produced and how it will work but i am pretty sure it should)
I was hoping it would be straightforward. If you have more issues please let me know as I would love to improve the script (time permitting)
Ivan
P.S. appreciate your response – im glad others are taking advantage of my simple little scripts.
Thanks for a great post Ivan.
I was wondering regarding step 3 – will filtering with “-EventId Expand” cover groups with 1 member? I know, I know… but this is the kind of environment I currently have to deal with.
Hi Joe,
The documentation doesn’t give a great deal of info on the matter but I believe that even with 1 member it will still log the expand event. Exchange will not know that there is only 1 member until it expands the distribution group.
References that may be of interest:
http://technet.microsoft.com/en-us/library/aa997573.aspx
http://technet.microsoft.com/en-us/library/bb124375.aspx
Please let me know if you encounter any problems (or if it works).
Regards,
ivan
Thanks for putting this together. Under step 6 did you mean to run a Disable-DistributionGroup command in the first section? You talk about mail disabling them, but then you just hide them.
Hi Tyler,
Thank you for your comment. After re-reading my post i noiced the mistake and have now rewritten it to make more sense (and be correct). Thanks for the note and hope you enjoy it.
Feedback is definately welcome!
Cheer,
Ivan
If you want to just mail-disable the security groups, wouldn’t Disable-DistributionGroup be the correct cmdlet? Remove-DistributionGroup mail disables AND deletes the group from AD altogether, right?
Sorry for being your personal editor :p
Thank you for your post – oversight that I have updated. Very much appreciated.
Thanks for the information! How would you handle the situation if you have multiple hub transport servers in the environment that mail could potentially go through?
Hi Eric,
Because i am not in a situation with multiple hub transport servers i never considered the situation. You would be able to agregate the data quite easily in excel before you go ahead and hide/disable the DL’s. To do it programatically i would need to think about it a bit more, and would not able to test it in my environment. I would be happy to help if you are interested in modifying the code to allow multiple transport servers.
Regards,
Ivan
Before you do the message tracking use the command get-transportserver then pipe that to the get-messagetrackinglog command, and remove the -server parameter.
Hi Mike,
Yes that correct – just to reiterate what you are suggesting is to change the command from:
Get-MessageTrackingLog -Server EXCH1 -EventId Expand -ResultSize Unlimited | Sort-Object RelatedRecipientAddress | Group-Object RelatedRecipientAddress | Sort-Object Name | Select-Object @{label=”PrimarySmtpAddress”;expression={$_.Name}}, Count | Export-CSV DL-Active.csv -notype
to:
Get-TransportServer | Get-MessageTrackingLog -EventId Expand -ResultSize Unlimited | Sort-Object RelatedRecipientAddress | Group-Object RelatedRecipientAddress | Sort-Object Name | Select-Object @{label=”PrimarySmtpAddress”;expression={$_.Name}}, Count | Export-CSV DL-Active.csv -notype
I do not have multiple transport servers to test with, but this command should get the logs from all transport servers and export to a single CSV file. Mike can you please confirm this is correct?
Cheers,
Ivan
Just run is agaisnt each transport server and merge the csv files.. removing duplicates