ISA Logs

[masterzenfoo]

Morning\Afternoon all,

I have a couple of ISA servers and a copy of SQL Express 2005 installed on my machine.

I am trying to play with the ISA logs to glean a bit more info from then rather than using the built in report writer with ISA.

Problem I am having is the ClientIP field is not showing the IP address correctly. The data type of the field is "bigint" and the IP address is displayed as an example: 2886732040

Does anyone know how I can put this number back to an IP address or something a bit more legible????

Thanks in advance!

 

[ptheriault]

The problem here is we don't know where the '.''s are needed.

The IP address could be any of these.

288.673.20.40

288.673.204.0




- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[masterzenfoo]

no the IP address should be starting 172.

just the way it has converted the ip address field. I can not find away to convert back.

PLEASE HELP! Its doing my head in!

 

[ptheriault]

Based on the information you have provided us there is no way to put back the '.'

Does the first three groups always have 3 digits?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[masterzenfoo]

All the ip addresses will be 172.16.9.***

Not bothered about the decimal notation if I can glean out the last three numbers from the four octet then I'll be happy!

Cheers fro your help

 

[ptheriault]

This will give you the last 3.

DECLARE @ip bigint
select @ip = 172169001
select len(@ip)-3

SELECT substring(convert(varchar(9),@ip),1,len(convert(varchar(9),@ip))-3)+'.'+RIGHT(convert(varchar(9),@ip),3)

- Paul

- If at first you don't succeed, find out if the loser gets anything.