Procesar IIS Log con sentencias SQL
Hace un tiempo me llamaron de una empresa pues estaba teniendo problemas con el servidor IIS pues tenía tiempos de respuestas malos. Se necesitaba un diagnóstico rápido para poder ver que era lo que estaba sucediendo.
La aplicacion estaba generada con GeneXus 8.0 y .NET, y corria sobre un IIS 6.
Si bien en otros casos utilizamos herramientas mas sofisticadas (por ejemplo el awstats, que es gratis y bueno) para el procesamiento de logs, en este caso como se necesitaban realizar algunas consultas puntuales ad-hoc, decidí utilizar el viejo y querido SQL server para procesar los logs.
El paso a paso de lo que hice fue:
1) Copiar los archivos de los IIS para mi disco local. Eran de unos 130Mb x dia x Servidor del NLB y tenian el formato exAAMMDD.log que es el default del IIS 6. Para procesar varios archivos, en una sola pasada, lo mejor es concatenarlos todos de la forma
copy ex*.log logiis.txt
2) Agregarle una linea de títulos... (para que quede mas fácil de cargar con DTS)
Es la tercera linea dentro del archivo, que hay que sacarle el texto #Fields: ..
La lista de campos es configurable, pero en este caso tenia los campos:
date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) sc-status sc-substatus sc-win32-status
3) Con DTS Import importar el archivo de texto (elegir el Text File como origen) poniendo que tome la primer linea como los nombres de columnas y con separador de blancos.
Se va a crear una tabla que tendra el nombre logiis.txt (el nombre del archivo)
4) Algunas consultas a realizar....
-- Borrar la lineas de titulos (sobran)
delete from logiis where date like '#%'
-- programas que mas se utilizan
select count(*),[cs-uri-stem] from logiis where [cs-uri-stem] like '%.aspx%'
group by [cs-uri-stem]
order by count(*) desc
-- Errores mas comunes
select count(*),[sc-status] from logiis where [sc-status] <> '200'
group by [sc-status]
order by count(*) desc
-- Objetos que faltan o dan errores (pueden ser imagens que faltan copiar, programas que no se encuentran, timeouts, etc)
select count(*),[cs-uri-stem],[sc-status] from logiis where [sc-status] <> '200'
group by [cs-uri-stem],[sc-status]
order by count(*) desc
-- Llamadas por IP
select count(*),[c-ip] from logiis where [c-ip] like '%.aspx%'
group by [c-ip]
order by count(*) desc
Si se tuvieran los campos bytes recibidos/enviados y segundos que demora la consulta, se pueden sacar también las consultas que mas consumen en bytes o las que demoran mas, de forma fácil.
Rápidamente pudimos ver que la mayoría de las consultas se estaban realizando desde una direccón ip única. Conversando con el usuario externo que estaba realizando esas consultas para realización de "screen scraping", capturando información del HTML de las pantallas a traves de progrmas. Con un sencillo web service que daba lo que solicitaba se pudo solucionar el problema.
La aplicacion estaba generada con GeneXus 8.0 y .NET, y corria sobre un IIS 6.
Si bien en otros casos utilizamos herramientas mas sofisticadas (por ejemplo el awstats, que es gratis y bueno) para el procesamiento de logs, en este caso como se necesitaban realizar algunas consultas puntuales ad-hoc, decidí utilizar el viejo y querido SQL server para procesar los logs.
El paso a paso de lo que hice fue:
1) Copiar los archivos de los IIS para mi disco local. Eran de unos 130Mb x dia x Servidor del NLB y tenian el formato exAAMMDD.log que es el default del IIS 6. Para procesar varios archivos, en una sola pasada, lo mejor es concatenarlos todos de la forma
copy ex*.log logiis.txt
2) Agregarle una linea de títulos... (para que quede mas fácil de cargar con DTS)
Es la tercera linea dentro del archivo, que hay que sacarle el texto #Fields: ..
La lista de campos es configurable, pero en este caso tenia los campos:
date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) sc-status sc-substatus sc-win32-status
3) Con DTS Import importar el archivo de texto (elegir el Text File como origen) poniendo que tome la primer linea como los nombres de columnas y con separador de blancos.
Se va a crear una tabla que tendra el nombre logiis.txt (el nombre del archivo)
4) Algunas consultas a realizar....
-- Borrar la lineas de titulos (sobran)
delete from logiis where date like '#%'
-- programas que mas se utilizan
select count(*),[cs-uri-stem] from logiis where [cs-uri-stem] like '%.aspx%'
group by [cs-uri-stem]
order by count(*) desc
-- Errores mas comunes
select count(*),[sc-status] from logiis where [sc-status] <> '200'
group by [sc-status]
order by count(*) desc
-- Objetos que faltan o dan errores (pueden ser imagens que faltan copiar, programas que no se encuentran, timeouts, etc)
select count(*),[cs-uri-stem],[sc-status] from logiis where [sc-status] <> '200'
group by [cs-uri-stem],[sc-status]
order by count(*) desc
-- Llamadas por IP
select count(*),[c-ip] from logiis where [c-ip] like '%.aspx%'
group by [c-ip]
order by count(*) desc
Si se tuvieran los campos bytes recibidos/enviados y segundos que demora la consulta, se pueden sacar también las consultas que mas consumen en bytes o las que demoran mas, de forma fácil.
Rápidamente pudimos ver que la mayoría de las consultas se estaban realizando desde una direccón ip única. Conversando con el usuario externo que estaba realizando esas consultas para realización de "screen scraping", capturando información del HTML de las pantallas a traves de progrmas. Con un sencillo web service que daba lo que solicitaba se pudo solucionar el problema.
Excelente la idea de importarlo mediante DTS a SQL.
ResponderBorrarTiene mucho poder tener los log's en una base de datos SQL, ya que te permite implementar aplicaciones que utilicen la misma.
En cambio si lo que necesitas es realizar "consultas en vivo" sobre los logs te recomiendo para análisis la utilización de LogParser (Microsoft)www.logparser.com.
"Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows® operating system such as the Event Log, the Registry, the file system, and Active Directory®."
Nativamente posee el soporte de IIS, y las consultas sobre el Log se efectúan con lenguaje SQL, por lo que las consultas SQL implementadas en SQLServer podrías utilizarla directamente sobre LogParser y correrlas de forma automática en línea de comandos.
David:
ResponderBorrarEl logparser es una buena opcion, para armar reportes y procesar los logs para realizacion de consultas. A mi me gusta para centralizar todos los reportes de varios servidores en una base de datos centralizada o en un panel de control desde donde se pueda visualizar el estado de varios indicadores de mis servidores. Sin embargo, el cargar los datos a una base de datos, tiene la ventaja que permite despues hacer unas cuantas consultas (que en principio no se cuales son).
Si bien conocia el logparser, y lo habia probado hace un tiempo, no habia visto que podia usarse para cargar los datos en una base de datos.
Gracias por el comentario!.
Saludos,
Enrique