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
}