Cheap and angry means to view and analyze ISA 2006 logs

Good time of day, habra people.
In this post I will describe one of the options for viewing ISA 2006 logs. Its main advantage is that it does not require paid software.

Logs should be stored in MSDE format. Next, download the SQL Server management studio (distributed for free), install it and connect to the MSFW database,
as described by CYRILL`a
Select "Create request" and connect to the database for the desired date and create a request, for example:

SELECT ClientIP, ClientUserName, ClientAgent, bytesrecvd, bytessent, logTime, uri
FROM WebProxyLog
WHERE ClientIP='_ip_adress_' ORDER BY logtime ASC

The request was completed, but for complete happiness I would like to see `ClientIP` in a convenient and understandable Ipv4, as well as host names in the` uri` column instead of the ip address.

So, fix the `ClientIP`, according to the method described in the article
How to Parse ISA log's ClientIP with T-SQL , the author is a Wacle.
')
I give my free translation:

How to process the ClientIP parameter in ISA logs using T-SQL

Often I accept emails from my clients with one question - how to translate ISA ClientIP into a familiar IPv4 format.
I did not find any documents related to the ClientIP rule in the ISA logs, but I was sure that this is a LONG INT value converted from a binary expression.
Based on this logic, the conversion rule should be as follows:

1. Convert a number into a 32-bit binary expression;
2. We divide it into 4 groups of 8 bits;
3. Convert each group into a decimal value and separate them with a period.

I wrote the following function to handle the ClientIP value in the 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


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


Example of launching this function ：
select dbo.ufn_getIPAddr (3232240156)
Result:
192.168.18.28

This article is the property of Wacle.
If you have any questions, write wacle.wang@gmail.com

Now we understand with names of hosts. Again, found in CYRILL`a
Quick Installation Guide:
1. Copy HostLogger.dll to the directory to ISA Server.
2. In the command line we execute: regsvr32 HostLogger.dll

Now we repeat the query to the database:
Select "Create a request", connect to the database for the desired date and perform this function:

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

Now again select “Create request” and connect to the database for the desired date and create a request, for example:

SELECT dbo.ufn_getIPAddr(ClientIP), ClientUserName, ClientAgent, bytesrecvd, bytessent, logTime, uri
FROM WebProxyLog
WHERE dbo.ufn_getIPAddr(ClientIP)='_ip_adress_' ORDER BY logtime ASC

And we get quite digestible data. This method has a drawback - it allows you to make a sample limited to one day.
Those. To get a sample for the month, you need to process the appropriate number of databases. But in order to quickly see the statistics of an individual user, this method is quite suitable.