Determine the source site of Outlook clients on Exchange server

We have been toying with the idea of centralising our Exchange environment for a while now, and as part of this project, we needed to audit our Outlook clients, to determine which source site they were connecting from.

I was tasked with this, and was able to quickly gather this information, from both the Exchange 2003 and the 2007 environment, without too much hassle.

For Exchange 2007 simply use get-logonstatistics and select the information that you need. I added some additional spice, which exports each server to a separate CSV file.

foreach ($server in get-mailboxserver){
write-host "Current server: " $server
$filename = "." + $server + ".csv"
Get-LogonStatistics -server $server | select UserName, ClientIPAddress | sort UserName -Unique | Export-Csv $filename
}

Exchange 2003 is very similar, but as you can probably guess by now, you need to use WMI.

foreach ($server in (Get-ExchangeServer | Where {$_.IsExchange2007OrLater -eq $false})){
write-host "Current server: " $server
$filename = "." + $server + ".csv"
Get-Wmiobject -namespace rootMicrosoftExchangeV2 -class Exchange_Logon -Computer $server | select MailboxDisplayName, ClientIP | sort MailboxDisplayName -Unique | Export-Csv $filename
}

My job done, I sent the CSV files of the project managers, only to find out that they thought it would be nice, to see exactly which site each IP address belonged to.

This proved to be a little more tricky, but after a few minutes of probing the Interwebs, I found a post where Shay uses nltest to get the site information for a computer.

I assimilated this into my script with a little DNS lookup to find the host name and came up with a function which will retrieve the site information for each IP address on the fly and add that to the CSV file.

function Get-ComputerSite ($ip){
Write-Host "Current IP:" $ip
$site = $null
$computer = [System.Net.Dns]::gethostentry($ip) 
$site = nltest /server:$($computer.hostname) /dsgetsite
Return $site[0]
}

$ADSiteWMI = @{Name="ADSite";expression={Get-ComputerSite $($_.ClientIP)}}
$ADSite = @{Name="ADSite";expression={Get-ComputerSite $($_.ClientIPAddress)}}

foreach ($server in get-mailboxserver){
write-host "Current server: " $server
$filename = "." + $server + ".csv"
$LogonStats = Get-LogonStatistics -server $server | sort UserName -Unique 
$LogonStats | select UserName, ClientIPAddress, $ADSite | Export-Csv $filename 
}

foreach ($server in (Get-ExchangeServer | Where {$_.IsExchange2007OrLater -eq $false})){
write-host "Current server: " $server
$filename = "." + $server + ".csv"
$LogonStats = Get-Wmiobject -namespace rootMicrosoftExchangeV2 -class Exchange_Logon -Computer $server | sort MailboxDisplayName -Unique
$LogonStats | select MailboxDisplayName, ClientIP, $ADSiteWMI | Export-Csv $filename

This does take some time to complete on servers with many connections, but it gets the results required. I have already noticed a few issues, and the script can do with a little more refinement.

I will post these updates as soon as I get round to adding them. For now, I hope this script can help someone else with a similar problem.

The complete script can be downloaded from here:

https://github.com/technologicza/get-clientipaddress.git

Using SCL to prevent messages from going to Junk Mail

In our environment, we have a number of email addresses which are managed by automated programs and systems and even some home grown applications.

Most of these systems use POP3 to connect to the mailboxes and download incoming email. Obviously POP3 does not give you access to subfolders like “Junk Mail”. It has come to our attention recently, that the Junk email rule has been flagging valid client messages as Junk Mail, and sending these messages to the Junk Mail folder. The result is that these instructions / client information never make it to the back office workflow systems.

To prevent this from happening, you first need to understand SCL or Spam Confidence Level.

The SCL, in a nutshell is basically a score based on a number of criteria, which determine how likely a message is to contain spam. The higher the score (maximum 9) the more confident Outlook is that the message is spam.

An awesome way to view the SCL for individual messages is to install a custom form, which displays an additional column with this information. More information about that here: http://msexchangeteam.com/archive/2004/05/26/142607.aspx

After installing the form, I needed to start sending some spam to myself. This would establish the same message is either blocked or cleared by the Transport Rule. I grabbed an obvious spam message from my Gmail account and turned it into a Powershell spambot:

$messageParameters = @{

Subject = "Vicodin ES (Hydrocodone) 650mg x 30 pills $209 -VISA- tbrkl rqg" Body = " -== The Best Painkillers available ==- Buy Hydrocodone, Vicodin ES, Codeine, Phentermin, Norco, Valiuml, Xanaxl Online You pay & we ship, Absolute NO question asked No PrescriptionNeeded (No doctor approval needed!) 100% deliver your order to your house We have been in business since 1998 This is a rare bargain online to obtain these UNIQUE products. No prior order needed. Limited supply of these hard to get pills, so hurry! "

From = "[email protected]"
To = "[email protected]" 
Bcc = "[email protected]" 
SmtpServer = "1.1.1.1" 
}
Send-MailMessage @messageParameters –BodyAsHtml

Confirmed! My spam message was being trapped by the Junk Mail rule with SCL 9 and moved to the Junk Mail folder.

OK, next we needed to create the Transport Rule. Now, if you are new to Powershell / Exchange I would suggest creating the rule in the GUI, as the interface / wizard used in that process is similar to the Outlook rules wizard.

Once you have the rule created it is very easy to add additional addresses using Powershell. More about that later. For the purposes of this post, I will however create the rule using the shell.

$condition = Get-TransportRulePredicate SentTo 
$condition.Addresses = @((Get-Mailbox "*jean.louw*")) 
$action = Get-TransportRuleAction SetSCl $action.SclValue = "-1" 
$warning = "WARNING: Adding mailboxes to this rule will prevent the Junk Mail rule from detecting possible spam." 

New-TransportRule -name "Set SCL level to -1" -Conditions @($condition) -Action @($action) -Comments $warning

This script will create the rule to set the SCL for all messages to matching addresses to -1. You can replace “(Get-Mailbox “*jean.louw*”)” with any expression or command, which will give you the mailboxes you need to add to the rule.

Now that we have the rule in place, we need to confirm that it is working. Yet again, I sent a control “spam” message ala spambot9000.

This time the message SCL was -1, as we predicted, and the message was not moved to Junk Mail as before.

In future, should you need to add additional email addresses to your rule, you can use the following:

$condition = Get-TransportRulePredicate SentTo 
$condition.Addresses = @((Get-Mailbox "*system*")) 
$condition.Addresses += @((Get-Mailbox "*louw, jean*")) 
Set-TransportRule "Set SCL level to -1" -Conditions @($condition)

Remember that you have to add all of your address searches, each time, as the conditions are overwritten by set-transportrule. This is a really easy way to get around the problem of false positives in mailboxes where humans don’t manage mailboxes, and are unable to notice that valid emails are being sent to Junk Mail.