2007年5月20日星期日

How to Parse ISA log's ClientIP with T-SQL

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
-------------------------------------------------------------------

没有评论: