Дешёвое и сердитое средство для просмотра и анализа логов ISA 2006

Доброе время суток, хабралюди.
В этой заметке я опишу один из вариантов просмотра логов ISA 2006. Основное его достоинство в том, что он не требует платного софта.

Логи должны храниться в MSDE формате. Далее, скачиваем SQL Server management studio (распространяется бесплатно), устанавливаем её и подключаемся к БД MSFW,
как описано у CYRILL`a
Выбираем «Создать запрос» и подключаемся к базе данных за желаемую дату и создаём запрос, например:

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

Запрос выполнен, но для полного счастья хотелось бы видеть `ClientIP` в удобном и понятном Ipv4, а также имена хостов в столбце `uri` вместо ip адреса.

Итак, фиксим `ClientIP`, по методике, описанной в статье
How to Parse ISA log's ClientIP with T-SQL, автор — некий Wacle.

Привожу свой вольный перевод:

Как обработать параметр ClientIP в ISA логах с помощью T-SQL

Часто я принимаю письма от моих клиентов с одним вопросом — как перевести ISA ClientIP в знакомый нам IPv4 формат.
Я не нашёл никаких документов касающихся правила ClientIP в ISA логах, но я был уверен что это LONG INT величина сконвертированная из двоичного выражения.
Основываясь на этой логике, правило конверсии должно быть нижеследующим:

1. Конвертируем число в 32-х битное двоичное выражение;
2. Делим его на 4 группы по 8 бит;
3. Конвертируем каждую группу в десятичную величину и разделяем их точкой.

Я написал следующую функцию для обработки значения ClientIP в логе ISA:

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

Пример запуска этой функции：
select dbo.ufn_getIPAddr(3232240156)
Результат：
192.168.18.28

Эта статья — собственность Wacle.
Если у вас есть какие-то вопросы, пишите wacle.wang@gmail.com

Теперь разбираемся с именами хостов. Опять-таки, найдено у CYRILL`a
Краткая инструкция по установке:
1. Копируем HostLogger.dll в каталог к ISA Server.
2. В командной строке выполняем: regsvr32 HostLogger.dll

Теперь повторяем запрос к базе данных:
Выбираем «Создать запрос», подключаемся к базе данных за желаемую дату и выполняем эту функцию:

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

Теперь снова выбираем «Создать запрос» и подключаемся к базе данных за желаемую дату и создаём запрос, например:

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

И получаем вполне удобоваримые данные. У этого способа есть недостаток — он позволяет сделать выборку, ограниченную одними сутками.
Т.е. чтобы получить выборку за месяц, нужно обработать соответствующее кол-во баз данных. Но для того, чтобы оперативно посмотреть статистику отдельного пользователя этот способ вполне подходит.