source and destination IP in logs (Full Version)

All Forums >> [ISA Server 2004 Firewall] >> Logging and Reporting



Message


chris@thomas.edu -> source and destination IP in logs (14.Dec.2004 2:51:00 PM)

I'm connecting to MSDE logs via SQL Server Enterprise Manage, but source and destination IP address fields show an integer. Does anyone know how to covert it to the IP address (or better yet, log the IP address instead)?




Linwood -> RE: source and destination IP in logs (8.Feb.2005 2:01:00 AM)

For IP address A.B.C.D the big integer stored is (((A*256+B)*256+C)*256+D

The expression in SQL to convert it is ugly, but you could write it as a UDF.

use master
go
create function BigInt2IP (@bi bigint)
returns Varchar(15)
as
begin
declare @rt Varchar(15)
set @rt= Convert(varchar(3),Convert(Bigint,@bi) / Convert(Bigint,256*256*256)) + '.' +
Convert(varchar(3),Convert(Bigint,@bi) / Convert(Bigint,256*256) % 256) + '.' +
convert(varchar(3),Convert(Bigint,@bi) / Convert(Bigint,256) % 256) + '.' +
convert(varchar(3),Convert(Bigint,@bi) % Convert(Bigint,256) )
return(@rt)
end
go

Then you can use it like this:

select top 5 master.dbo.Bigint2IP(clientIP), *
from ISALOG_20050207_WEB_000.dbo.WebProxyLog

This will show a real IP address from the ClientIP, you can use it most anywhere the numeric version is.

[ February 08, 2005, 02:31 AM: Message edited by: Linwood ]




Page: [1]