Recently I received one post from my customer regarding how to convert ISA ClientIP to our familiar IPv4 format.
I did not find any document regarding the rule of ClientIP in ISA log, but I believed that it was a LONG INT value converted from binary expression. Based on this logic, the conversion rule should be as follows:
1. Convert the number into 32 bit binary expression;
2. Separate the binary into four group by 8 bit;
3. Convert each 8 bit into a decimal value and concate them with a dot.
Then I write the following function for parse ClientIP value in ISA log.
=======================================
create function ufn_getIPAddr(@nIP bigint)
returns nvarchar(20)
as
begin
declare @binIP varbinary(4)
declare @h1 varbinary(1)
declare @h2 varbinary(1)
declare @h3 varbinary(1)
declare @h4 varbinary(1)
declare @strIP nvarchar(20)
SELECT @binIP = CONVERT(VARBINARY(4),@nIP )
select @h1 = SUBSTRING(@binIP,1,1)
select @h2 = SUBSTRING(@binIP,2,1)
select @h3 = SUBSTRING(@binIP,3,1)
select @h4 = SUBSTRING(@binIP,4,1)
select @strIP = CONVERT(nvarchar(3),CONVERT(int,@h1))+'.'
+CONVERT(nvarchar(3),CONVERT(int,@h2))+'.'
+CONVERT(nvarchar(3),CONVERT(int,@h3))+'.'
+CONVERT(nvarchar(3),CONVERT(int,@h4))
--print @strIP
return @strIP
end
==========================================
Execute the statement:
select dbo.ufn_getIPAddr(3232240156)
Output:
192.168.18.28
-------------------------------------------------------------------
This article is owned by Wacle.
If you have any questions, please contact wacle.wang@gmail.com
-------------------------------------------------------------------
2007年5月20日星期日
订阅:
博文评论 (Atom)
没有评论:
发表评论