• Twitter
  • FaceBook

Welcome to ISAserver.org

Forums | Register | Login | My Profile | Inbox | RSS RSS icon | My Subscription | My Forums | Address Book | Member List | Search | FAQ | Ticket List | Log Out

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   


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
RE: source and destination IP in logs - 8.Feb.2005 2:01:00 AM   


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
create function BigInt2IP (@bi bigint)
returns Varchar(15)
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) )

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)
Post #: 2

Page:   [1] << Older Topic    Newer Topic >>
All Forums >> [ISA Server 2004 Firewall] >> Logging and Reporting >> source and destination IP in logs Page: [1]
Jump to:

New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts