Hace algunas semanas mi buen amigo Isvet Laclaustra me invitó a preparar un tema que permita a la gente de infraestructura entender un poquito de lo que es Business Intelligence y de cómo se pueden aplicar sus conceptos a tareas que ellos realizan cada dia. Para ello entonces se me ocurrió el crear nuestra propia versión de Google Analytics para procesar los archivos de log que puede generar un servidor de internet como el Internet Information Services (IIS), para de este modo poder interpretar cómo se está desempeñando nuestro website: quiénes son mis visitantes, de dónde son, qué páginas miran, cuántos son, qué navegador de internet usan, qué sistema operativo usan, etc.
Lo importante del concepto es que además lo puedan aplicar no sólo a IIS, sino también a otras herramientas que generen archivos de log en archivos de texto con la misma lógica que mostraremos a continuación, permitiendo así un mayor y mejor monitoreo proactivo que ayude a tomar mejores decisiones con respecto a la demanda o uso de nuestros servicios.
Entrando entonces al caso que nos toca, lo primero que debemos entender es que existen varias formas a cómo IIS puede guardar los archivos de log de acuerdo a la versión del mismo. Pueden ver más detalles acerca de los tipos de log en MSDN aquí. Es importante recalcar que todos estos formatos dividen las columnas del archivo en base al caracter de espacio o “en blanco”. Particularmente he creado este post basado en la versión IIS Log File File Format de mi IIS versión 7. Uno de los archivos se ve así:
De acuerdo al mismo encabezado del archivo, los campos que contiene este formato son los siguientes: 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; time-taken.
Como se ve, el archivo presenta un encabezado que ocupa las primeras 4 líneas del archivo comenzando con el caracter “#”:
#Software: Microsoft Internet Information Services 7.5
#Version: 1.0
#Date: 2009-07-30 17:14:40
#Fields: 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 time-taken
Lo curioso del caso, es que por algún motivo, IIS incluye estas mismas 4 líneas además de al comienzo, en cualquier parte del cuerpo del archivo, lo cuál hace nuestro ejercicio más interesante. Una de las líneas de datos se ve así:
2009-07-30 17:14:40 ::1 GET /reports - 80 - ::1
Mozilla/4.0+(compatible;+MSIE+8.0;+Windows+NT+6.1;+WOW64;+Trident/4.0;
+SLCC2;+.NET+CLR+2.0.50727;+.NET+CLR+3.5.30729;+.NET+CLR+3.0.30729)
404 0 2 495
IIS crea uno o varios archivos de acuerdo a unas variables del tamaño de cada uno de los archivos. En mi caso tengo los archivos de varios días de datos en un folder en particular:
Para lograr nuestro objetivo he creado un nuevo proyecto de Integration Services con un nuevo paquete, el cual tendrá por objetivo cargar e interpretar cada uno de los archivos de log IIS para luego escribir sus datos en nuestra base de datos de SQL.
Una vez en nuestro paquete primero crearemos una variable “FileName” la cual almacenará el “path” del archivo log que estemos procesando, podemos inicializarla con el “path” de alguno de nuestros archivos:
Ahora agregamos un control “ForEach Loop Container”, el cuál será el responsable de recorrer nuestro folder con los archivos de log y seleccionar uno a la vez para ser procesado por nuestro Data Flow que será agregado posteriormente:
En la configuración del ForEach, debemos asegurarnos que en la propiedad “Enumerator“ esté seleccionado el valor “Foreach File Enumerator”. Como podrán ver, hay muchas otras opciones para los cuales nos puede servir este control:
Ahora nos toca configurar el “path” o ruta del folder donde el control deberá recorrer los archivos. Así mismo aquí tenemos la opción de indicar qué tipo de archivos debe leer el control, en nuestro caso serían los *.log. Finalmente, indicamos también de que queremos el “path” completo del archivo (“Fully qualified”), ya que este valor será usado posteriormente para leer cada uno de los archivos":
En la sección de Variable Mappings, mapearemos la salida de nuestro control ForEach hacia nuestra variable “FileName” creada en el primer paso:
Ahora ya tenemos lista la parte del flujo de control de nuestra solución para recorrer cada uno de los archivos del log de IIS. Ahora agregamos un componente “Data Flow” dentro de nuestro ForEach para trabajar con el contenido de cada log e insertarlo a nuestra tabla en SQL Server:
Como mencioné anteriormente, ya en nuestro Data Flow, lo primero que necesitamos es leer el contenido de nuestros archivos, por ello agregamos un componente “Flat File Source” de la sección de “Data Flow Sources”:
Ahora debemos realizar la configuración para que lea cada uno de los archivos de log. Para ello creamos una nueva conexión de tipo “Flat File” desde nuestro control, presionando el botón “New”:
En la ventana de configuración de la conexión, seleccionamos alguno de nuestro archivo de log a fin de que el control pueda “entender” el formato del mismo:
Adicionalmente en la ventana anterior le estamos indicando al control que no lea las primeros 4 filas del archivo (“Header rows to skip”), con esto logramos evitar las primeras lineas de control que tienen todos los archivos de log, pero no hemos resuelto el problema si luego dentro del contenido encontramos otras líneas de control de manera inesperada.
En la seccion “Columns”, por defecto podremos ver una sola columna con el contenido de cada fila de nuestro archivo de log, lo cual dejaremos de esta misma manera. Es decir, no crearemos en este punto una columna para cada campo del archivo, esto lo haremos más adelante para tener más control del manejo del contenido del mismo:
Lo que sí debemos hacer es aumentar el tamaño por defecto de la columna a 1000 caracteres para que no se nos trunque nada del contenido del archivo:
Ahora está listo nuestro control de lectura de los archivos de log de IIS:
Aquí viene la magia. Para hacer que nuestro componente “Flat File”, lea cada uno de los archivos que nos traiga el “ForEach”, debemos configurar la propiedad “Expressions” de nuestra “Flat File Connection”:
Seleccionamos la propiedad “ConnectionString” y le damos click a los 3 botoncitos e incluimos ahi la variable que alimentará la ruta de cada archivo que procese el loop:
Como comenté al inicio de este artículo, los logs de IIS presentan un formato algo irregular, ya que en cualquier parte del contenido del archivo podemos recibir alguna de las lineas de control del archivo, que incluyen la descripción de los campos del archivo y alguna otra “meta data”. Adicionalmente el formato del archivo incluye un caracter en blanco o espacio como separador de las columnas de datos, lo cual no es soportado por defecto con el control. Por estos motivos, no podemos dejar todo el control del archivo a nuestro componente “Flat File”, ya que si configuramos los campos en el mismo, en cuanto el archivo presente alguna de esas filas inesperadas, el componente arrojará un error de interpretación o “parsing”. Para resolver este pequeño problemita, usaremos un control “Script Component”, el cual nos permite incluir código de .NET y por tanto nos abre las puertas para incluir cualquier código de validación que necesitemos como en este caso:
Lo renombramos:
Configuramos como columna de entrada, la columna saliente de nuestro componente “Flat File”:
Y como salida (“output”), configuramos los campos que vamos a procesar o “parsear” de nuestro log de acuerdo a la descripción de los campos que se incluye dentro del mismo archivo:
Ya dentro de la sección de Script de nuestro control y específicamente dentro del procedimiento “Sub ProcessInputRows” vamos a crear el código que nos permitirá convertir la columna entrante en todas las columnas de salida que necesitamos. ProcessInputRows se ejecuta cada vez que exista una nueva fila en el control Data Flow.
Primero declaramos algunas variables para la lectura de cada una de las filas del archivo (strRow). También incluimos una variable que usaremos para separar cada una de las columnas del archivo usando un espacio en blanco (strColSepartor), recibiendo las columnas resultantes en un array de tipo cadena (rowValues()):
Dim strRow As String
Dim strColSeperator As String = " "
Dim rowValues As String()
Leemos la linea de datos:
strRow = Row.Line.ToString()
Ahora podemos validar de que sólo procesemos si la línea es una de nuestro interés. En nuestro caso, no nos interesan las filas que comienzan con el caracter de control “#”. Si ese fuese el caso, identificaremos a esa fila como inválida invalidando la fecha del evento (Event Date = 9999/12/31):
If strRow.StartsWith("#") = False Then
Else
Row.EventDate = "9999/12/31"
End If
Ahora que tenemos las filas que nos interesan, procedemos a aplicar la función Split de .NET para convertir la fila que recibimos como entrada en columnas:
rowValues = Row.Line.Split(CChar(strColSeperator))Para obtener cada una de los campos de datos, accedemos a la posición específica de acuerdo a las especificaciones del log de IIS usando nuestro array. Por ejemplo para obtener el campo con la fecha en que ocurrió la solicitud al servidor de IIS haremos lo siguiente:
Row.EventDate = rowValues.GetValue(0).ToString()
De igual manera, para leer por ejemplo el “browser” o navegador utilizado por el usuario en la posición 10, usamos:
Row.UserAgent = rowValues.GetValue(9).ToString()
Con esto ya tienen la idea de leer cada uno de los campos del log de IIS. También podemos incluir algunas validaciónes específicas o incluir código para el manejo de errores:
Try
Row.WindowsStatus = rowValues.GetValue(12).ToString()
Catch ex As Exception
Row.WindowsStatus = 0
End Try
Por ejemplo, algo más específico sería tratar de inferir el navegador utilizado por el usuario usando el campo que contiene el “User Agent”:
'Browser
If rowValues.GetValue(9).ToString.Contains("Chrome") = True Then
Row.Browser = "Chrome"
ElseIf rowValues.GetValue(9).ToString.Contains("MSIE+8") = True Then
Row.Browser = "Internet Explorer 8"
ElseIf rowValues.GetValue(9).ToString.Contains("MSIE+7") = True Then
Row.Browser = "Internet Explorer 7"
ElseIf rowValues.GetValue(9).ToString.Contains("MSIE+6") = True Then
Row.Browser = "Internet Explorer 6"
ElseIf rowValues.GetValue(9).ToString.Contains("Firefox") = True Then
Row.Browser = "Firefox"
ElseIf rowValues.GetValue(9).ToString.Contains("Safari") = True Then
Row.Browser = "Safari"
ElseIf rowValues.GetValue(9).ToString.Contains("BlackBerry") = True Then
Row.Browser = "BlackBerry"
Else
Row.Browser = "Other"
End If
De manera similar para inferir el sistema operativo utilizado por el usuario desde el mismo campo del “User Agent”:
'Windows
If rowValues.GetValue(9).ToString.Contains("Windows+6.1") = True _
Or rowValues.GetValue(9).ToString.Contains("Windows+NT+6.1") Then
Row.OS = "Windows 7"
ElseIf rowValues.GetValue(9).ToString.Contains("Windows+NT+6.0") = True _
Or rowValues.GetValue(9).ToString.Contains("Windows+6.0") Then
Row.OS = "Windows Vista"
ElseIf rowValues.GetValue(9).ToString.Contains("Windows+2000") = True _
Or rowValues.GetValue(9).ToString.Contains("Windows+NT+5.0") Then
Row.OS = "Windows 2000"
ElseIf rowValues.GetValue(9).ToString.Contains("Windows+XP") = True _
Or rowValues.GetValue(9).ToString.Contains("Windows+NT+5.1") Then
Row.OS = "Windows XP"
ElseIf rowValues.GetValue(9).ToString.Contains("Windows+NT+5.2") = True Then
Row.OS = "Windows Server 2003"
ElseIf rowValues.GetValue(9).ToString.Contains("Mac") = True Then
Row.OS = "Mac"
ElseIf rowValues.GetValue(9).ToString.Contains("Windows") = True Then
Row.OS = "Other Windows"
Else
Row.OS = "Other OS"
End If
Después de terminar nuestro código .NET, nuestro Data Flow se vería así:
El código completo incluido en el “Script Component” lo pueden ver a continuación:
Dim strRow As String
Dim strColSeperator As String = " "
Dim rowValues As String()
strRow = Row.Line.ToString()
'If strRow.Contains(",") Then
' strColSeperator = (",")
'ElseIf strRow.Contains(";") Then
' strColSeperator = ";"
'End If
If strRow.StartsWith("#") = False Then
'MessageBox.Show(Row.Line.ToString)
rowValues = Row.Line.Split(CChar(strColSeperator))
Row.EventDate = rowValues.GetValue(0).ToString()
Row.EventTime = rowValues.GetValue(1).ToString()
Row.ServerIP = rowValues.GetValue(2).ToString()
Row.CSMethod = rowValues.GetValue(3).ToString()
Row.URIStem = rowValues.GetValue(4).ToString()
Row.URIQuery = rowValues.GetValue(5).ToString()
Row.Port = rowValues.GetValue(6).ToString()
Row.UserName = rowValues.GetValue(7).ToString()
Row.ClientIP = rowValues.GetValue(8).ToString()
Row.UserAgent = rowValues.GetValue(9).ToString()
Row.ServiceStatus = rowValues.GetValue(10).ToString()
Row.ServiceSubStatus = rowValues.GetValue(11).ToString()
Try
Row.WindowsStatus = rowValues.GetValue(12).ToString()
Catch ex As Exception
Row.WindowsStatus = 0
End Try
Row.TimeTaken = rowValues.GetValue(13).ToString()
'Browser
If rowValues.GetValue(9).ToString.Contains("Chrome") = True Then
Row.Browser = "Chrome"
ElseIf rowValues.GetValue(9).ToString.Contains("MSIE+8") = True Then
Row.Browser = "Internet Explorer 8"
ElseIf rowValues.GetValue(9).ToString.Contains("MSIE+7") = True Then
Row.Browser = "Internet Explorer 7"
ElseIf rowValues.GetValue(9).ToString.Contains("MSIE+6") = True Then
Row.Browser = "Internet Explorer 6"
ElseIf rowValues.GetValue(9).ToString.Contains("Firefox") = True Then
Row.Browser = "Firefox"
ElseIf rowValues.GetValue(9).ToString.Contains("Safari") = True Then
Row.Browser = "Safari"
ElseIf rowValues.GetValue(9).ToString.Contains("BlackBerry") = True Then
Row.Browser = "BlackBerry"
Else
Row.Browser = "Other"
End If
'Windows
If rowValues.GetValue(9).ToString.Contains("Windows+6.1") = True Or rowValues.GetValue(9).ToString.Contains("Windows+NT+6.1") Then
Row.OS = "Windows 7"
ElseIf rowValues.GetValue(9).ToString.Contains("Windows+NT+6.0") = True Or rowValues.GetValue(9).ToString.Contains("Windows+6.0") Then
Row.OS = "Windows Vista"
ElseIf rowValues.GetValue(9).ToString.Contains("Windows+2000") = True Or rowValues.GetValue(9).ToString.Contains("Windows+NT+5.0") Then
Row.OS = "Windows 2000"
ElseIf rowValues.GetValue(9).ToString.Contains("Windows+XP") = True Or rowValues.GetValue(9).ToString.Contains("Windows+NT+5.1") Then
Row.OS = "Windows XP"
ElseIf rowValues.GetValue(9).ToString.Contains("Windows+NT+5.2") = True Then
Row.OS = "Windows Server 2003"
ElseIf rowValues.GetValue(9).ToString.Contains("Mac") = True Then
Row.OS = "Mac"
ElseIf rowValues.GetValue(9).ToString.Contains("Windows") = True Then
Row.OS = "Other Windows"
Else
Row.OS = "Other OS"
End If
Else
Row.EventDate = "9999/12/31"
End If
La pregunta que debemos hacer de acuerdo a la lógica de nuestro Script Component sería de que si la fecha del evento es igual a “9999/12/31” de la siguiente manera:
EventDate == (DT_DBTIMESTAMP)"9999/12/31"
Por último agregamos un componente “OLE DB Destination” para que escriba en nuestra base de datos de SQL las filas válidas:
Seleccionamos o creamos la conexión a nuestra base de datos (en mi caso IISLogDataMart):
Y para la tabla, ya que no existe aún, la crearemos usando el botón “New”, el cuál presentará el script de acuerdo a la metadata de las columnas existentes en nuestro paquete:
Presionamos “OK” y la tabla se crea:
Ahora sólo nos queda mapear los campos de nuestro paquete a los de nuestra tabla. En este caso el “mapping” es automático, ya que los nombres de las columnas de la tabla destino son los mismos a los del paquete:
Finalmente, así se ve nuestro “Data Flow” terminado”
Luego de ejecutar nuestro paquete y de “subir” todos los archivos de log en nuestro folder a nuestra tabla, ya estamos listos para darle “vida” a nuestros datos y develar lo que está pasando en términos de visitas con nuestro website. Para ello, podemos usar la herramienta Report Builder 2.0, una herramienta dirigida al usuario final (no al desarrollador) que es parte de SQL Server 2008, pero que se debe descargar por separado. Pueden referirse al post que publiqué anteriormente para saber más sobre el producto:
Ya en Report Builder 2.0 creamos nuestra conexión a la fuente de datos en SQL Server:
Ingresamos los datos de la conexión:
Y ahora de manera fácil, usando nuestras capacidades de escribir consultas en SQL, vamos a crear una primera gráfica para mostrar la candidad de “hits” recibidos a lo largo del tiempo:
SELECT EventDate, COUNT(*)
FROM IISLogInput
GROUP BY EventDate
ORDER BY EventDate
Con estos datos vamos a crear una gráfica sencilla incluendo el contador en la zona de “Data Fields” y la fecha del evento en la zona de las categorías:
De igual manera, ahora como dicen por ahí, el cielo es el límite. Podemos crear un sin número de gráficas o tablas que nos brinden información interesante acerca de las visitas recibidas en nuestro servidor de IIS:
Y por tanto de forma fácil, y no necesariamente usando Analysis Services, puedo crear un dashboard de lo más interesante y automatizado que muestre información mucho más interesante que el mirar los archivos de log aburridos de IIS. Nuestro dashboard quedó así:
Espero que haya servidor el tour de cómo aplicar Inteligencia de Negocios a sus procesos operacionales de una manera relativamente fácil. Lo que espero que hayan podido aprender en esta guia es:
- Utilizar el componente ForEach Loop para leer objetos del sistema de archivos
- Procesar archivos de texto
- Procesar archivos de texto con formato irregular
- Utilizar el “Script Component” para crear lógicas personalizadas que no hagan otros controles o de manera más fácil
- Cargar los datos de un archivo de texto hacia una tabla de SQL Server
- Crear dashboards atractivos usando Report Builder 2.0
Un ejemplo de archivo de log, el paquete de SSIS utilizado y el reporte generado en este demo lo pueden bajar de mi Skydrive:
Hasta la próxima,
Alan
****UPDATE 1/22/2010
Recibí una pregunta mencionando problemas para poder habilitar el ingreso de los datos del File Enumerator en el For Each component.
Hay un problema de la interface, pues cuando uno agrega un nuevo For Each y edita sus propiedades por primera vez, notarán que no aparecen los controles para poder configurar el File Enumerator:
La solución consiste en cambiar el valor del combobox del Enumerator a cualquier otro y luego volverlo al “Foreach File Enumerator” para que por arte de magia aparescan los controles perdidos:
Espero que esto les permita continuar.
Alan.
.jpg)
.png)
