Converting dotted notation to a long integer is easy enough as well. You just break the dot notation octets into their base decimal values multiplied by the decimal value of each octet itself, and add each octet's base * value product together. For example, let's take the next to the last entry from above - 203.83.16.0 through 203.83.23.255. The base for each octet is 256 to the power of the octet column's position, or 2563.2562.2561.2560 or further extrapolated to 16777216.65536.256.0 respectively. So in this case, for the beginning IP, you'll take (16,777,216 * 203) + (65,536 * 83) + (256 * 16) + (0 ) or 3,405,774,848 + 5,439,488 + 4,096 + 0, which equals 3,411,218,432 as indicated above in the BegIPLong field. Using the same process, we see that the EndIPLong field would be 3405774848 + 5439488 + 5888 + 255, or 3400220479 as again indicated above. (Note that for the last octet of 2560 you just use the decimal value of the IP address, not 0 * the value which would always be 0.)

Converting the beginning and ending IP address to a long integer like this within the IPAddresses table allows us to easily retrieve the Country value from the associated IP range record where the long integer IP address in the Firewall log is between the beginning and ending long integer IP referenced in the table. The ISA Firewall Log table structure is well documented, so I won't eat web space here detailing it; however, I will reference some important, basic fields therein: ID (bigint), logtime (datetime), protocol (varchar 32), SourceIP (bigint), SourcePort (int), DestinationIP (bigint), DestinationPort (int) works for now. Let's call this the FirewallLog table.

Consider this single record matching the above columns that might be found in the FirewallLog:

"1044432","2007-11-08 12:26:35.157","TCP","3684096418","45887","3515032734","443"

select FirewallLog.*, IPAddresses.FullCntry from FirewallLog,IPAddresses where FirewallLog.SourceIP between IPAddresses.BegIPLong and IPAddresses.EndIPLong and FirewallLog.ID = 1044432

"1044432","2007-11-08 12:26:35.157","TCP","3684096418","45887","3515032734","443","China"

Functions and Procedures

The following SQL will create a function for you called dbo.ConvertLongIP:

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: Timothy "Thor" Mullen -- Create date: 03/01/08 -- Description: Scalar function to converts long/bigint formatted IP addresses to dot notation -- ============================================= ALTER FUNCTION [dbo].[ConvertLongIP] ( -- Input parameter for long (bigint)IP here @LongIP bigint ) RETURNS varchar(15) AS BEGIN DECLARE @DotIP varchar(15), @bin varbinary(4) select @bin = cast(@LongIP as varbinary(4)) select @DotIP = cast(convert(int,substring(@bin,1,1)) as varchar(3)) + '.' + cast(convert(int,substring(@bin,2,1)) as varchar(3)) + '.' + cast(convert(int,substring(@bin,3,1)) as varchar(3)) + '.' + cast(convert(int,substring(@bin,4,1)) as varchar(3)) RETURN @DotIP END

Once the function is created, one can now easily determine the source IP address by including the following function call in the original SQL statement:

select FirewallLog.*, dbo.ConvertLongIP(SourceIP),IPAddresses.FullCntry from FirewallLog,IPAddresses where FirewallLog.SourceIP between IPAddresses.BegIPLong and IPAddresses.EndIPLong and FirewallLog.ID = 1044432

Reporting Examples

For my environments, I have verified that there is no valid traffic coming out of Russia, China, Turkey, Poland, and Korea. And very, very little valid SMTP traffic comes from Brazil and Mexico to my production networks. While a large portion of the US-based SMTP traffic is indeed spam, I at least now have the opportunity to analyze traffic and make decisions based upon my research.

Now let's look at HTTP: