Powershell Needful Things put that in your pipeline

2Mar/102

Determine the source site of Outlook clients on Exchange server

Posted by Jean Louw

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 root\MicrosoftExchangeV2 -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 root\MicrosoftExchangeV2 -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: