Skip to main content

Geolocationing Exchange Message Tracking with Powershell (Exchange 2000/2003) (Seeing what countries your messages/Spam are coming from)

Geolocation as wikipedia aptly puts is the real-world geographic location of a computer based on its IP address. Many people use this for web site statistics for displaying where users are coming from. This information can also be very useful to actually tell you where your mail (and SPAM) is coming from. There are a lot of people out there selling and open sourcing geoip services, for instance awstats uses one of the open source variants to get the country information it uses in its report. Following some of these trails I found references to the IpToCountry.csv file which is a list of IP ranges per country that a few people maintain and provide (in slightly differing formats) for free as a download. The most up to date version in the one provided by Webnet77 which is the one I choose to use and is what this script is based on. Using this CSV file in its native format while possible with a little manipulation using the Microsoft text driver I found was very slow when trying to resolve a large number of entries from the message tracking. So for this script I decided to go with creating a mdb database using the ADO and ADO.NET and importing the csv file into to the database.

The script itself is a cut down version of my BYO message tracker the main section of script remains the same. It produces a .NET form that allows you to enter a server-name and a time range for the message tracking logs you want to query. (There was a lot of more functionality in the BYO message tracker that I didn’t port over into this script).It still uses WMI behind the scenes to query the Exchange Message tracking logs. To manage the Geolocation process a number of functions and routines have been added. This script itself is self maintaining meaning that as long as you’re using it on a machine that has ADO on it the script will create the database it’s going to use itself using ADOX. And it also includes a maintenance routine that checks the import file to see if its been updated based on the file modified time of the iptocountry.csv file. If you have downloaded a new csv file when the script is run if will detect the new version and delete the old database and create a new one based on the new file. To insert the records into the database itself the System.Data.ole class is used as well as some powershell cmdlets to get the content of the IptoCountry file one line at a time and then parse it into the database. After the records are inserted into the database because this process tends to make the file size bloat a bit I used the ADO JRO objects to compress the mdb file back to smallest possible size which helps on the performance side of thing as well. To help out further with query performances two indexes are added to both the IPFrom and IPTo columns in the database.

Once the script had created and populated the databases you’ll have a database of numerical ranges which are representations of the IP Blocks that have been allocated to particular countries/IP’s . To find out where an IP address belongs to it’s a matter of first converting it into its numeric representation and then doing a SQL query to find the row in the database that this numeric representation falls within. Because you’ll find mail servers in your message tracking logs tend to repeat a lot to save querying the database for every occurrence of the same server I used a hash table the tracks the C class of a resolved IP and if the script detects it querying the same c class it will use the hashtable value instead of making a another database call. This speeds things up considerably when you making future queries because the database doesn’t need to be queried.

The WMI side of this script does differ from the BYO message tracker in that the message ID that is uses is different. Because the messageID I was using in the other script doesn’t have access to the sending servers IP address I had to use another Message Tracking ID. The one that I found worked the best was 1019 which is the first event ID in the tracking log see this kb. To cater for the possibility of multiple instances of a 1019 events for the same message a hash-table is used to prevent the same message ID being counted twice. The Email Type box allows you to select between what type of email this script will report on. Normal mail is as it indicates normal messages that are sent and received via the server. The Spam email type is messages that the IMF has taken gateway action on (either delete or achieve). When you select the Spam email type the type of tracking ID the WMI side of the script queries for changes from 1019 to 1040.

The extras side of the script allows the aggregation of the query results by country so you can see by country how much email is being sent and received. The side by side aggregation creates the same aggregation but it changes the WMI query to include both the 1019 and 1040 entry types and then creates a side by side aggregation so you can see by country how much normal email is sent vs. how much IMF detected Spam per country (I think this is very cool).

To use the script you need to first download the Iptocountry.csv file from Webnet77 . At the moment the script is set to read the file from the c:\temp directory and also create the database in the c:\temp directory this is controlled by the following lines.

$dbfilepath = "c:\temp\iptocountry.mdb"
$tmpdbfilepath = "c:\temp\iptocountrycomp.mdb"
$importfilepath = "c:\temp\iptocountry.csv"

The $tmpdbfilepath is used only during the compression section of the script.

I’ve put a downloadable copy of the script here. This script itself is a bit long to post verbatim heres what some of the mdb creation and maintenance routines.


function createdb{
$aoADOXDb = new-object -com ADOX.Catalog
$aoADOXDb.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + $dbfilepath)
$atADOXTable = new-object -com ADOX.Table
$atADOXTable.Name = "IPTOCOUNTRY"
$atADOXTable.Columns.Append("IPFrom", $adDouble)
$atADOXTable.Columns.Append("IPTo", $adDouble)
$atADOXTable.Columns.Append("Registry", $adVarWChar, 25)
$atADOXTable.Columns.Append("ASSIGNED", $adDouble)
$atADOXTable.Columns.Append("CTRY", $adVarWChar, 2)
$atADOXTable.Columns.Append("CNTRY", $adVarWChar, 3)
$atADOXTable.Columns.Append("COUNTRY", $adVarWChar, 100)
$atindex = new-object -com ADOX.index
$atindex.Name = "idxIPFrom"
$atindex.Columns.Append("IPFrom")
$atindex.Unique = $True
$atADOXTable.Indexes.Append($atindex)

$atindex1 = new-object -com ADOX.index
$atindex1.Name = "idxIPTo"
$atindex1.Columns.Append("IPTo")
$atindex1.Unique = $True
$atADOXTable.Indexes.Append($atindex1)

$aoADOXDb.Tables.Append($atADOXTable)
$atADOXTablever = new-object -com ADOX.Table
$atADOXTablever.Name = "Version"
$atADOXTablever.Columns.Append("FileModifiedDate", $adVarWChar, 255)
$aoADOXDb.Tables.Append($atADOXTablever)
# Cleanup
$aoADOXDb.ActiveConnection.close()

[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$atindex)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$atindex1)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$atADOXTable)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$atADOXTablever)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$aoADOXDb)
[system.gc]::Collect()
$atindex = $null
$atindex1 = $null
$atADOXTable = $null
$atADOXTablever = $null
$aoADOXDb = $null

}

function populatedb{

$file = get-item $importfilepath
$ocOdbcConnection.Open()
$dcOdBcommand = new-object System.Data.OleDb.OleDbCommand
$dcOdBcommand.connection = $ocOdbcConnection
$dcOdBcommand.commandtext = "Insert into Version values('" + $file.lastwritetime + "')"
$dcOdBcommand.ExecuteNonQuery()
$rcRowCount = 0
"Filling Database this may take a few minutes"
get-content $importfilepath | %{
$linarr = $_.replace("'","``").split(",")
if ($linarr[0].indexofany("#") -band $linarr.length -gt 1){
$stSQLStatement = "Insert into IPTOCOUNTRY values('" + $linarr[0].replace("`"","") + "','" `
+ $linarr[1].replace("`"","") + "','"+ $linarr[2].replace("`"","") + "','"+ `
$linarr[3].replace("`"","") + "','" + $linarr[4].replace("`"","") + "','" + `
$linarr[5].replace("`"","") + "','" + $linarr[6].replace("`"","") + "')"
$dcOdBcommand.commandtext = $stSQLStatement
$inResult = $dcOdBcommand.ExecuteNonQuery()
if ($inResult -ne 1){$inResult}
}
$rcRowCount = $rcRowCount + 1
if ($rcRowCount -eq 10000){
$rcRowCount = 0
"10000 Rows Inserted"
}
}
"Fill completed"
$dcOdBcommand = $null
$ocOdbcConnection.Close()
$ocOdbcConnection = $null
$jrJroobj = new-object -com JRO.JetEngine
$dbSourceDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + $dbfilepath
$dbDestinationDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + $tmpdbfilepath
$jrJroobj.CompactDatabase($dbSourceDB, $dbDestinationDB)
remove-item $dbfilepath
copy-item $tmpdbfilepath $dbfilepath
remove-item $tmpdbfilepath
}

function checkfileversion{
$ocOdbcConnection.Open()
$dcOdBcommand = new-object System.Data.OleDb.OleDbCommand
$dcOdBcommand.connection = $ocOdbcConnection
$dcOdBcommand.commandtext = "select FileModifiedDate from Version"
$drDBreader = $dcOdBcommand.ExecuteReader()
$retval = 0
while ($drDBreader.read()){
if ($drDBreader[0] -ne $file.lastwritetime){$retval = 1}
else{"DB up to date"}
}
$dcOdBcommand = $null
$ocOdbcConnection.Close()
return $retval
}

Popular posts from this blog

Testing and Sending email via SMTP using Opportunistic TLS and oAuth in Office365 with PowerShell

As well as EWS and Remote PowerShell (RPS) other mail protocols POP3, IMAP and SMTP have had OAuth authentication enabled in Exchange Online (Official announcement here ). A while ago I created  this script that used Opportunistic TLS to perform a Telnet style test against a SMTP server using SMTP AUTH. Now that oAuth authentication has been enabled in office365 I've updated this script to be able to use oAuth instead of SMTP Auth to test against Office365. I've also included a function to actually send a Message. Token Acquisition  To Send a Mail using oAuth you first need to get an Access token from Azure AD there are plenty of ways of doing this in PowerShell. You could use a library like MSAL or ADAL (just google your favoured method) or use a library less approach which I've included with this script . Whatever way you do this you need to make sure that your application registration  https://docs.microsoft.com/en-us/azure/active-directory/develop/quickstart-register-

How to test SMTP using Opportunistic TLS with Powershell and grab the public certificate a SMTP server is using

Most email services these day employ Opportunistic TLS when trying to send Messages which means that wherever possible the Messages will be encrypted rather then the plain text legacy of SMTP.  This method was defined in RFC 3207 "SMTP Service Extension for Secure SMTP over Transport Layer Security" and  there's a quite a good explanation of Opportunistic TLS on Wikipedia  https://en.wikipedia.org/wiki/Opportunistic_TLS .  This is used for both Server to Server (eg MTA to MTA) and Client to server (Eg a Message client like Outlook which acts as a MSA) the later being generally Authenticated. Basically it allows you to have a normal plain text SMTP conversation that is then upgraded to TLS using the STARTTLS verb. Not all servers will support this verb so if its not supported then a message is just sent as Plain text. TLS relies on PKI certificates and the administrative issue s that come around certificate management like expired certificates which is why I wrote th

The MailboxConcurrency limit and using Batching in the Microsoft Graph API

If your getting an error such as Application is over its MailboxConcurrency limit while using the Microsoft Graph API this post may help you understand why. Background   The Mailbox  concurrency limit when your using the Graph API is 4 as per https://docs.microsoft.com/en-us/graph/throttling#outlook-service-limits . This is evaluated for each app ID and mailbox combination so this means you can have different apps running under the same credentials and the poor behavior of one won't cause the other to be throttled. If you compared that to EWS you could have up to 27 concurrent connections but they are shared across all apps on a first come first served basis. Batching Batching in the Graph API is a way of combining multiple requests into a single HTTP request. Batching in the Exchange Mail API's EWS and MAPI has been around for a long time and its common, for email Apps to process large numbers of smaller items for a variety of reasons.  Batching in the Graph is limited to a m
All sample scripts and source code is provided by for illustrative purposes only. All examples are untested in different environments and therefore, I cannot guarantee or imply reliability, serviceability, or function of these programs.

All code contained herein is provided to you "AS IS" without any warranties of any kind. The implied warranties of non-infringement, merchantability and fitness for a particular purpose are expressly disclaimed.