thor at hammerofgod
Jan 16, 2008, 12:20 PM
Post #1 of 1
For those of you logging ISA (or whatever) to SQL, you'll have no doubt
SQL scalar function to convert big int to dot notation
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
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.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
-- 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
DECLARE @DotIP varchar(15),
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))