Welcome to ISAserver.org

source and destination IP in logs

Users viewing this topic: none

Logged in as: Guest

Printable Version

All Forums >> [ISA Server 2004 Firewall] >> Logging and Reporting >> source and destination IP in logs

Page: [1]

Message

<< Older Topic Newer Topic >>

source and destination IP in logs - 14.Dec.2004 2:51:00 PM

chris@thomas.edu

Posts: 1
Joined: 2.May2003
From: Waterville
Status: offline

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

Post #: 1

Featured Links*

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

Linwood

Posts: 28
Joined: 2.Dec.2001
From: Louisa, VA, USA
Status: offline

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 ]

(in reply to chris@thomas.edu)