For those of you logging ISA (or whatever) to SQL, you'll have no doubt noted that the source and destination IP's are logged as long integers, and not dot notation. While this is great for anyone using geo-ip data for reporting (as in using "between" logic in your queries) it's not human readable. For convenience, I've coded up a little scalar function for SQL in order for you to easily convert the long int IP to dot notation for anyone interested. Once the function is created, you can simply do a "Select blah, blah, blah, dbo.ConvertLongIP(IntSourceIPHere), dbo.ConvertLongIP(IntDestinationIPHere) from logs where whatever = whatever" to return the dot notation IP. The SQL to create the function is included below. Thanks. t set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: Thor -- Create date: 11/28/07 -- Description: Scalar function to converts long/bigint formatted IP addresses to dot notation -- ============================================= ALTER FUNCTION [dbo].[ConvertLongIP] ( -- Add the parameters for the function 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
