Wednesday, June 19, 2013

How to clear old unused distribution lists from Exchange 2010/2007 programatically

Source :   http://ivan.dretvic.com/2011/10/how-to-clear-old-unused-distribution-lists-from-exchange-2010-programatically/

 

We have used below CMDs to provide the DL Status report for IMG Client.

 

Get-DistributionGroup -ResultSize unlimited | Select-Object PrimarySMTPAddress | Sort-Object PrimarySMTPAddress | Export-CSV DL-ALL.csv –notype

 

 

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

 

 

 

$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

 

 

 

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

Get-TransportServer -Identity EXCH1 | fl *messagetracking*

2

MessageTrackingLogEnabled               : True

 

3

MessageTrackingLogMaxAge                : 30.00:00:00

4

MessageTrackingLogMaxDirectorySize      : 1000 MB (1,048,576,000 bytes)

 

5

MessageTrackingLogMaxFileSize           : 10 MB (10,485,760 bytes)

6

MessageTrackingLogPath                  : C:\Program Files\Microsoft\Exchange Server\V14\TransportRoles\Logs\MessageTracking

 

7

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

Get-TransportServer -Identity EXCH1 | fl *messagetracking*

2

MessageTrackingLogEnabled               : True

 

3

MessageTrackingLogMaxAge                : 90.00:00:00

4

MessageTrackingLogMaxDirectorySize      : 2.93 GB (3,145,728,000 bytes)

 

5

MessageTrackingLogMaxFileSize           : 10 MB (10,485,760 bytes)

6

MessageTrackingLogPath                  : C:\Program Files\Microsoft\Exchange Server\V14\TransportRoles\Logs\MessageTracking

 

7

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

$file1 = Import-CSV -Path "DL-ALL.csv"

2

$file2 = Import-CSV -Path "DL-Active.csv"

 

3

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 scipt 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.

1

$a = Get-Date

2

$notes = "$a - Hidden from address list due to inactive use."

 

3

$inactiveDL = Import-CSV -Path "DL-Inactive2.csv" | foreach-object

4

{

 

5

Set-Group -identity $_.PrimarySmtpAddress -notes $notes

6

Set-DistributionGroup -identity $_.PrimarySmtpAddress -HiddenFromAddressListsEnabled $true

 

7

}

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:

1

$a = Get-Date

2

$notes = "$a - No longer Mail Enabled due to inactive use."

 

3

$inactiveDL = Import-CSV -Path "DL-Inactive2.csv" | foreach-object

4

{

 

5

Set-Group -identity $_.PrimarySmtpAddress -notes $notes

6

Disable-DistributionGroup -identity $_.PrimarySmtpAddress -Confirm $false

 

7

}

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.


No comments:

Post a Comment