27 diciembre 2009

Aplicando BI a mi infraestructura de IT: Monitoreando mi Servidor Web (IIS)

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í:

image

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:


image


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:


image


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:


image


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:


image


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":


image


En la sección de Variable Mappings, mapearemos la salida de nuestro control ForEach hacia nuestra variable “FileName” creada en el primer paso:


image


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:


image


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”:


image


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”:


image


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: 


image


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:


image


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:


image


Ahora está listo nuestro control de lectura de los archivos de log de IIS:


image


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”:


image


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:


image 


image


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:


image 


Lo renombramos:


image


Configuramos como columna de entrada, la columna saliente de nuestro componente “Flat File”:


image


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:


image


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í:


image


El código completo incluido en el “Script Component” lo pueden ver a continuación:









  1. Dim strRow As String

  2. Dim strColSeperator As String = " "

  3. Dim rowValues As String()

  4. strRow = Row.Line.ToString()

  5. 'If strRow.Contains(",") Then

  6. '    strColSeperator = (",")

  7. 'ElseIf strRow.Contains(";") Then

  8. '    strColSeperator = ";"

  9. 'End If

  10. If strRow.StartsWith("#") = False Then


  11.     'MessageBox.Show(Row.Line.ToString)


  12.     rowValues = Row.Line.Split(CChar(strColSeperator))

  13.     Row.EventDate = rowValues.GetValue(0).ToString()

  14.     Row.EventTime = rowValues.GetValue(1).ToString()

  15.     Row.ServerIP = rowValues.GetValue(2).ToString()

  16.     Row.CSMethod = rowValues.GetValue(3).ToString()

  17.     Row.URIStem = rowValues.GetValue(4).ToString()

  18.     Row.URIQuery = rowValues.GetValue(5).ToString()

  19.     Row.Port = rowValues.GetValue(6).ToString()

  20.     Row.UserName = rowValues.GetValue(7).ToString()

  21.     Row.ClientIP = rowValues.GetValue(8).ToString()

  22.     Row.UserAgent = rowValues.GetValue(9).ToString()

  23.     Row.ServiceStatus = rowValues.GetValue(10).ToString()

  24.     Row.ServiceSubStatus = rowValues.GetValue(11).ToString()

  25.     Try

  26.         Row.WindowsStatus = rowValues.GetValue(12).ToString()

  27.     Catch ex As Exception

  28.         Row.WindowsStatus = 0

  29.     End Try

  30.     Row.TimeTaken = rowValues.GetValue(13).ToString()




  31.     'Browser

  32.     If rowValues.GetValue(9).ToString.Contains("Chrome") = True Then

  33.         Row.Browser = "Chrome"

  34.     ElseIf rowValues.GetValue(9).ToString.Contains("MSIE+8") = True Then

  35.         Row.Browser = "Internet Explorer 8"

  36.     ElseIf rowValues.GetValue(9).ToString.Contains("MSIE+7") = True Then

  37.         Row.Browser = "Internet Explorer 7"

  38.     ElseIf rowValues.GetValue(9).ToString.Contains("MSIE+6") = True Then

  39.         Row.Browser = "Internet Explorer 6"

  40.     ElseIf rowValues.GetValue(9).ToString.Contains("Firefox") = True Then

  41.         Row.Browser = "Firefox"

  42.     ElseIf rowValues.GetValue(9).ToString.Contains("Safari") = True Then

  43.         Row.Browser = "Safari"

  44.     ElseIf rowValues.GetValue(9).ToString.Contains("BlackBerry") = True Then

  45.         Row.Browser = "BlackBerry"

  46.     Else

  47.         Row.Browser = "Other"

  48.     End If


  49.     'Windows

  50.     If rowValues.GetValue(9).ToString.Contains("Windows+6.1") = True Or rowValues.GetValue(9).ToString.Contains("Windows+NT+6.1") Then

  51.         Row.OS = "Windows 7"

  52.     ElseIf rowValues.GetValue(9).ToString.Contains("Windows+NT+6.0") = True Or rowValues.GetValue(9).ToString.Contains("Windows+6.0") Then

  53.         Row.OS = "Windows Vista"

  54.     ElseIf rowValues.GetValue(9).ToString.Contains("Windows+2000") = True Or rowValues.GetValue(9).ToString.Contains("Windows+NT+5.0") Then

  55.         Row.OS = "Windows 2000"

  56.     ElseIf rowValues.GetValue(9).ToString.Contains("Windows+XP") = True Or rowValues.GetValue(9).ToString.Contains("Windows+NT+5.1") Then

  57.         Row.OS = "Windows XP"

  58.     ElseIf rowValues.GetValue(9).ToString.Contains("Windows+NT+5.2") = True Then

  59.         Row.OS = "Windows Server 2003"

  60.     ElseIf rowValues.GetValue(9).ToString.Contains("Mac") = True Then

  61.         Row.OS = "Mac"

  62.     ElseIf rowValues.GetValue(9).ToString.Contains("Windows") = True Then

  63.         Row.OS = "Other Windows"

  64.     Else

  65.         Row.OS = "Other OS"

  66.     End If


  67. Else

  68.     Row.EventDate = "9999/12/31"

  69. End If







Ya pasamos la parte más “complicada” de nuestro paquete. Ahora agregamos un componente “Conditional Split” para poder discernir entre las filas correctas o incorrectas de nuestro archivo:


image


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"



image


Por último agregamos un componente “OLE DB Destination” para que escriba en nuestra base de datos de SQL las filas válidas:


image


Seleccionamos o creamos la conexión a nuestra base de datos (en mi caso IISLogDataMart):


image


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:


image


Presionamos “OK” y la tabla se crea:


image


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:


image


Finalmente, así se ve nuestro “Data Flow” terminado”


image


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:


image


Ya en Report Builder 2.0 creamos nuestra conexión a la fuente de datos en SQL Server:


image


Ingresamos los datos de la conexión:


image


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
image

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:

image

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:

image

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í:

image 

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:


image


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:


image


Espero que esto les permita continuar.





Alan.

3 comentarios:

Anónimo dijo...

Hello I'd love to congratulate you for such a great quality forum!
I was sure this would be a perfect way to introduce myself!

Sincerely,
Monte Phil
if you're ever bored check out my site!
[url=http://www.partyopedia.com/articles/airplane-party-supplies.html]airplane Party Supplies[/url].

N2o4Fun dijo...

Ecxelente tutorial. Nunca habia utlizado Report Builder, parece una herramienta bastante util y las graficas no se ven nada mal.

Alan Koo dijo...

Gracias por el comentario N2o4Fun.