Cargar Logs de IIS6 en SQL Server con OPENROWSET BULK y Fichero de Formato

Habitualmente resulta de utilidad la explotación de los ficheros Log del IIS, para sacar estadísticas de acceso a los IIS, resolver incidencias varias, etc., lo cual resulta bastante sencillo, sin necesidad de utilizar herramientas de tercero (muchas de ellas, de pago). Al fin y al cabo, en qué empresa hoy en día no existe algún servidor IIS, ya sea para hospedar aplicaciones ASP o ASP.Net, o bien para hospedar otro tipo de aplicaciones como MOSS 2007, de las cuales nos puede resultar muy interesante poder obtener estadísticas de acceso a través de los Logs de IIS.

Introducción a los Ficheros de Log de IIS

Lo normal, es que cada Sitio Web de IIS esté configurado para dejar un Log de accesos en formato W3C Extendido. En IIS6 tenemos distintas opciones de registro del Log de IIS:

• Microsoft IIS Log File Format.
• NCSA Common Log File Format.
• ODBC Logging.
• W3C Extended Log File Format.

El formato de fichero de Log de IIS más utilizado, es el formato W3C Extended Log File Format, que se trata de un fichero de texto plano, cuyas columnas están separadas por espacios, y las fechas registradas en UTC (es decir, la fecha GMT, la de Greenwich).

Por defecto, se genera un fichero de Log IIS por cada día, aunque esta periodicidad puede cambiarse, teniendo en cuenta que los ficheros de Log de IIS no se autoreciclan, es decir, los ficheros de Log de IIS no se borran por sí solos por lo que puede ser necesario que eliminemos ficheros de Log de IIS antiguos por nosotros mismos para liberar espacio en disco.

Los ficheros de Log de IIS se almacenan por defecto bajo subdirectorios en la siguiente ubicación:

C:\WINDOWS\system32\LogFiles

De este modo, un servidor IIS puede hospedar múltiples Sitios Web, de tal modo que para cada Sitio Web se utilizará un subdirectorio. El nombre de dicho subdirectorio será la concatenación del literal W3SVC con el Identificador del Sitio Web. Por ejemplo, el Sitio Web GuilleSQL en mi servidor IIS tiene como identificador el 731426560, por lo tanto utilizará el subdirectorio W3SVC731426560, que será el siguiente directorio:

C:\WINDOWS\system32\LogFiles\W3SVC731426560

Las columnas que deseamos que se registren en el Log del IIS, son seleccionables. A continuación se indican todas las posibles columnas que pueden utilizarse en un Log de IIS, indicando cuales no están seleccionadas por defecto.

• Date.
• Time.
• Client IP Address (c-ip).
• User Name (cs-username).
• Service Name (s-sitename).
• Server Name (s-computername). No seleccionado por defecto.
• Server IP Address (s-ip).
• Server Port (s-port).
• Method (cs-method).
• URI Stem (cs-uri-stem).
• URI Query (cs-uri-query).
• Protocol Status (sc-status).
• Protocol Substatus (sc-substatus).
• Win32 Status (sc-win32-status).
• Bytes Sent (sc-bytes. No seleccionado por defecto.
• Bytes Received (cs-bytes). No seleccionado por defecto.
• Time Taken (time-taken). No seleccionado por defecto.
• Protocol Version (cs-version). No seleccionado por defecto.
• Host (cs-host). No seleccionado por defecto.
• User Agent (cs(User-Agent)).
• Cookie. No seleccionado por defecto.
• Referer (cs(Referer)). No seleccionado por defecto.

Como se puede comprobar, existen varias columnas que no se utilizan por defecto y que podrían llegar a resultar muy útiles, como es el caso de Server Name (s-computername) para cuando necesitemos cargar datos de varios servidores IIS (ej: tenemos un Cluster NLB y nos gustaría comprobar como se reparten las peticiones entre los Nodos del Cluster NLB), el Referer, Bytes Sent, Bytes Received, etc.

Fichero de Formato para cargar el Log de IIS

Si deseamos cargar un fichero de texto en SQL Server, una de las primeras cosas que podemos hacer es desarrollar el correspondiente fichero de formato. ¿Qué es un fichero de formato en SQL Server? Un fichero de formato en SQL Server es un fichero de texto (con una determinada estructura) que sirve para indicarle a SQL Server el formato del fichero de texto que se desea cargar, es decir, para definir qué columnas tiene, de qué tipo de dato es cada columna, que separadores se utilizan entre columnas y líneas, qué nombre se le desea dar a cada columna, etc.

La creación del fichero de formato es de utilidad para cargar un fichero de texto en SQL Server, ya que podremos utilizar el fichero de formato en:

• Operaciones de carga con OPENROWSET BULK. OPENROWSET BULK, disponible desde SQL Server 2005, permite acceder a un fichero de texto almacenado en el sistema de ficheros, como si se tratase de una tabla en una sentencia SELECT FROM OPENROWSET BULK, lo cual permite consultar directamente dicho fichero de texto, o bien cargarlo en una tabla con una sentencia INSERT INTO SELECT OPENROWSET BULK o SELECT INTO OPENROWSET BULK, realizar una carga condicional aplicando una WHERE (incluso consultando tablas adicionales con LEFT JOIN, etc.), y muchas cosas más. Es importante tener en cuenta que OPENROWSET BULK requiere siempre de un fichero de formato. Otro detalle a tener en cuenta, es que OPENROWSET se ejecuta desde el propio motor de SQL Server (in-process).
• Operaciones de carga con BULK INSERT. La sentencia BULK INSERT permite insertar el contenido de un fichero en una tabla de SQL Server. BULK INSERT se ejecuta desde el propio motor de SQL Server (in-process) y puede actuar como una operación de registro mínimo. Es la forma de carga más rápida en SQL Server (con el permiso de SELECT INTO, pero claro, SELECT INTO no puede añadir filas a una tabla existente).
• Operaciones de carga con BCP. La utilidad bcp.exe es una herramienta que se ejecuta en un proceso separado de SQL Server (out-of-process), ya sea en la propia máquina que ejecuta SQL Server, o en una máquina externa. La utilidad bcp.exe permite descargar y cargar datos en SQL Server (entre SQL Server y ficheros del sistema de archivos), pudiendo utilizar opcionalmente un fichero de formato para especificar el formato del fichero de carga. Puede actuar como una operación de registro mínimo.

Tampoco quiero entrar en más detalles de las diferentes alternativas de carga en SQL Server, ya que no está en el alcance de este artículo, y además ya he hablado de esto en artículos anteriores como es el caso de SELECT INTO vs INSERT INTO, etc.

Así que, sin más, aprovecho para incluir un fichero de formato para cargar un fichero de Log de IIS en formato W3C Extended Log File Format y con las columnas por defecto. Sólo comentar que en las pruebas que he realizado, he tenido algunos problemas con los tipos de datos reales (ej: con la fecha) y con la precisión (ej: con Win32Status), motivo por el cual, se puede observar que este fichero de formato define todas las columnas como de texto, utilizando en algunos casos una precisión bastante mayor del valor realmente almacenado.

9.0
14
1    SQLCHAR   0   50      " "      1     date             ""
2    SQLCHAR   0   50      " "      2     time             ""
3    SQLCHAR   0   50      " "      3     SiteName         ""
4    SQLCHAR   0   50      " "      4     ServerIP         ""
5    SQLCHAR   0   50      " "      5     Method           ""
6    SQLCHAR   0   1024    " "      6     URIstem          ""
7    SQLCHAR   0   2048    " "      7     URIquery         ""
8    SQLCHAR   0   50      " "      8     Port             ""
9    SQLCHAR   0   250     " "      9     ClientUserName   ""
10   SQLCHAR   0   50      " "      10    ClientIP         ""
11   SQLCHAR   0   1024    " "      11    ClientUserAgent  ""
12   SQLCHAR   0   50      " "      12    Status           ""
13   SQLCHAR   0   50      " "      13    SubStatus        ""
14   SQLCHAR   0   1024    "\r\n"   14    Win32Status      ""

De este modo, es posible utilizar este fichero de formato para leer o cargar ficheros de Log de IIS, teniendo en cuenta que podremos realizar las operaciones de Casting necesarias (ej: cambiar la columna date de VARCHAR a DATETIME), crear tablas indexadas para acelerar las consultas SQL, etc.

Cargar los ficheros Log de IIS

Ahora que ya tenemos nuestro fichero de formato para los ficheros de Log del IIS, ya sólo nos queda poder tratarlos desde SQL Server. Para ello, en este artículo voy a utilizar OPENROWSET BULK, principalmente por ser la alternativa que encuentro más sencilla y flexible de utilizar (esto es sólo una opinión).

Con OPENROWSET BULK podemos consultar un fichero al vuelo, es decir, sin necesidad de cargarlo en SQL Server. Evidentemente, para ficheros grandes resulta un poco costoso (y no es quizás una buena práctica), pero para ficheros pequeños es una opción muy cómoda, que duda cabe. A continuación se incluyen varias consultas de ejemplo (para este y los siguientes ejemplos, suponemos que los ficheros de Log de IIS se han copiado previamente a un disco local del servidor SQL Server):

Del mismo modo, con OPENROWSET BULK podemos crear una tabla desde un fichero con una sentencia SELECT INTO. De este modo, seguidamente podemos consultar directamente la tabla (en vez del fichero) y además, no es necesario que la tabla exista previamente. A continuación se muestra un ejemplo:

El caso anterior es interesante, pero quizás nos pueda interesar más cargar varios ficheros de Log de IIS en la misma tabla de SQL Server con INSERT SELECT OPENROWSET BULK, ya sean varios ficheros del mismo servidor IIS o de diferentes servidores IIS, pudiendo consultar seguidamente esta información consolidada directamente desde SQL Server. A continuación se muestra un ejemplo, en el que se supone que la tabla de destino ya existe previamente:

Crear un DataMart con los ficheros Log de IIS

Visto lo visto, lo último que nos queda es crear un DataMart en SQL Server, donde cargar los ficheros de Log de IIS. En el siguiente ejemplo, vamos a contruir un pequeño DataMart relacional, incluyendo las tablas de dimensión, la tabla de hechos y el proceso de carga. En este caso, vamos a utilizar Claves Subrogadas (enteros autonuméricos, es decir, IDENTITY) en las tablas de dimensión para minimizar el almacenamiento de la tabla de hechos y maximizar el rendimiento.

A continuación se incluyen las DDL de creación de la tabla de carga de los ficheros de Log de IIS (stagging). Sobre esta tabla se cargará el fichero o ficheros Log de IIS que deseemos cargar en nuestro DataMart. Se trata de un área temporal, es decir, esta tabla se truncará y cargará las veces que se necesite (su contenido no será persistente a lo largo del tiempo).

CREATE TABLE [dbo].[STG_IISLog](
	[ServerName] [varchar](250) NULL,
	[date] [varchar](50) NULL,
	[time] [varchar](50) NULL,
	[SiteName] [varchar](50) NULL,
	[ServerIP] [varchar](50) NULL,
	[Method] [varchar](50) NULL,
	[URIstem] [varchar](1024) NULL,
	[URIquery] [varchar](2048) NULL,
	[Port] [varchar](50) NULL,
	[ClientUserName] [varchar](250) NULL,
	[ClientIP] [varchar](50) NULL,
	[ClientUserAgent] [varchar](1024) NULL,
	[Status] [varchar](50) NULL,
	[SubStatus] [varchar](50) NULL,
	[Win32Status] [varchar](1024) NULL
)

A continuación se incluyen las DDL de creación de las tablas de dimensión de nuestro DataMart.

CREATE TABLE [dbo].[DW_DIM_URIstem](
	[URIstem_ID] [int] IDENTITY(1,1) NOT NULL,
	[URIstem] [varchar](1024) NOT NULL,
   CONSTRAINT [PK_DW_DIM_URIstem] PRIMARY KEY CLUSTERED
   ([URIstem_ID] ASC)
)
GO
CREATE TABLE [dbo].[DW_DIM_URIquery](
	[URIquery_ID] [int] IDENTITY(1,1) NOT NULL,
	[URIquery] [varchar](2048) NOT NULL,
   CONSTRAINT [PK_DW_DIM_URIquery] PRIMARY KEY CLUSTERED
   ([URIquery_ID] ASC)
)
GO
CREATE TABLE [dbo].[DW_DIM_SiteName](
	[SiteName_ID] [tinyint] IDENTITY(1,1) NOT NULL,
	[SiteName] [varchar](50) NOT NULL,
   CONSTRAINT [PK_DW_DIM_SiteName] PRIMARY KEY CLUSTERED
   ([SiteName_ID] ASC)
)
GO
CREATE TABLE [dbo].[DW_DIM_ServerName](
	[ServerName_ID] [tinyint] IDENTITY(1,1) NOT NULL,
	[ServerName] [varchar](250) NOT NULL,
   CONSTRAINT [PK_DW_DIM_ServerName] PRIMARY KEY CLUSTERED
   ([ServerName_ID] ASC)
)
GO
CREATE TABLE [dbo].[DW_DIM_ServerIP](
	[ServerIP_ID] [smallint] IDENTITY(1,1) NOT NULL,
	[ServerIP] [varchar](50) NOT NULL,
   CONSTRAINT [PK_DW_DIM_ServerIP] PRIMARY KEY CLUSTERED
   ([ServerIP_ID] ASC)
)
GO
CREATE TABLE [dbo].[DW_DIM_Method](
	[Method_ID] [tinyint] IDENTITY(1,1) NOT NULL,
	[Method] [varchar](50) NOT NULL,
   CONSTRAINT [PK_DW_DIM_Method] PRIMARY KEY CLUSTERED
  ([Method_ID] ASC)
)
GO
CREATE TABLE [dbo].[DW_DIM_ClientUSerName](
	[ClientUserName_ID] [int] IDENTITY(1,1) NOT NULL,
	[ClientUserName] [varchar](250) NOT NULL,
   CONSTRAINT [PK_DW_DIM_ClientUSerName] PRIMARY KEY CLUSTERED
   ([ClientUserName_ID] ASC)
)
GO
CREATE TABLE [dbo].[DW_DIM_ClientIP](
	[ClientIP_ID] [int] IDENTITY(1,1) NOT NULL,
	[ClientIP] [varchar](50) NOT NULL,
   CONSTRAINT [PK_DW_DIM_ClientIP] PRIMARY KEY CLUSTERED
   ([ClientIP_ID] ASC)
)
GO

A continuación se incluye la DDL de creación de la tabla de hechos, que almacenará la información histórica de nuestros ficheros de Log de IIS. Esta tabla podría llegar a almacenar grandes volúmenes de datos.

CREATE TABLE [dbo].[DW_TH_IISLog](
	[ServerName_ID] [tinyint] NOT NULL,
	[DateTimeUTC] [smalldatetime] NOT NULL,
	[SiteName_ID] [tinyint] NOT NULL,
	[ServerIP_ID] [smallint] NOT NULL,
	[Method_ID] [tinyint] NOT NULL,
	[URIstem_ID] [int] NOT NULL,
	[URIquery_ID] [int] NOT NULL,
	[Port] [smallint] NOT NULL,
	[ClientUserName_ID] [int] NOT NULL,
	[ClientIP_ID] [int] NOT NULL,
	[ClientUserAgent_ID] [int] NOT NULL,
	[Status] [smallint] NOT NULL,
	[SubStatus] [smallint] NOT NULL,
	[Win32Status] [bigint] NOT NULL
)
GO
CREATE CLUSTERED INDEX [IX_DW_TH_IISLog_DateTimeUTC] ON [dbo].[DW_TH_IISLog]
([DateTimeUTC] ASC)
GO

Seguidamente, se incluye el proceso de carga de la tabla de ficheros de Log de IIS (stagging), para un fichero de Log en particular. Es importante tener en cuenta, que si se desean cargar múltiples ficheros, debe ejecutarse este código para cada fichero (excepto la línea TRUNCATE TABLE, que borraría la tabla).

TRUNCATE TABLE dbo.STG_IISLog

INSERT INTO dbo.STG_IISLog
SELECT 'ServerName', LogIIS.*
FROM OPENROWSET
(
   BULK 'c:\temp\logiis.log',
   FORMATFILE='c:\temp\LogIIS.fmt',
   FIRSTROW=5
) AS LogIIS

Una vez cargada la tabla de ficheros (ya sea con uno o varios ficheros), estaremos en condiciones de cargar las tablas de Dimensiones. A continuación se incluye el proceso de carga incremental de las tablas de dimensión, las cuales se cargan desde el stagging.

INSERT INTO dbo.DW_DIM_ClientIP (ClientIP)
SELECT DISTINCT A.ClientIP
FROM dbo.STG_IISLog AS A
LEFT JOIN dbo.DW_DIM_ClientIP AS B
ON A.ClientIP = B.ClientIP
WHERE B.ClientIP IS NULL

INSERT INTO dbo.DW_DIM_ClientUserName(ClientUserName)
SELECT DISTINCT A.ClientUserName
FROM dbo.STG_IISLog AS A
LEFT JOIN dbo.DW_DIM_ClientUSerName AS B
ON A.ClientUserName = B.ClientUserName
WHERE B.ClientUserName IS NULL

INSERT INTO dbo.DW_DIM_Method (Method)
SELECT DISTINCT A.Method
FROM dbo.STG_IISLog AS A
LEFT JOIN dbo.DW_DIM_Method AS B
ON A.Method = B.Method
WHERE B.Method IS NULL

INSERT INTO dbo.DW_DIM_ServerIP (ServerIP)
SELECT DISTINCT A.ServerIP
FROM dbo.STG_IISLog AS A
LEFT JOIN dbo.DW_DIM_ServerIP AS B
ON A.ServerIP = B.ServerIP
WHERE B.ServerIP IS NULL

INSERT INTO dbo.DW_DIM_ServerName (ServerName)
SELECT DISTINCT A.ServerName
FROM dbo.STG_IISLog AS A
LEFT JOIN dbo.DW_DIM_ServerName AS B
ON A.ServerName = B.ServerName
WHERE B.ServerName IS NULL

INSERT INTO dbo.DW_DIM_SiteName (SiteName)
SELECT DISTINCT A.SiteName
FROM dbo.STG_IISLog AS A
LEFT JOIN dbo.DW_DIM_SiteName AS B
ON A.SiteName = B.SiteName
WHERE B.SiteName IS NULL

INSERT INTO dbo.DW_DIM_URIquery (URIquery)
SELECT DISTINCT A.URIquery
FROM dbo.STG_IISLog AS A
LEFT JOIN dbo.DW_DIM_URIquery AS B
ON A.URIquery = B.URIquery
WHERE B.URIquery IS NULL

INSERT INTO dbo.DW_DIM_URIstem (URIstem)
SELECT DISTINCT A.URIstem
FROM dbo.STG_IISLog AS A
LEFT JOIN dbo.DW_DIM_URIstem AS B
ON A.URIstem = B.URIstem
WHERE B.URIstem IS NULL

Y para finalizar, una vez cargadas nuestras tablas de Dimensión, estaremos en condición de cargar nuestra tabla de hechos. Para ello deberemos ejecutar el siguiente bloque de código Transact-SQL, el cual realiza una carga incremental de la tabla de hechos, la cual se carga desde el stagging y desde las tablas de dimensión (recogiendo de las tablas de dimensión las claves subrogadas, motivo de los LEFT JOIN).

INSERT INTO dbo.DW_TH_IISLog
(
	ServerName_ID
	,DateTimeUTC
	,SiteName_ID
	,ServerIP_ID
	,Method_ID
	,URIstem_ID
	,URIquery_ID
	,Port
	,ClientUSerName_ID
	,ClientIP_ID
	,Status
	,SubStatus
	,Win32Status
)
SELECT

	ServerName.ServerName_ID
	,CAST(REPLACE([date], '-','') + ' ' + [time] AS DATETIME) AS DateTimeUTC
	,SiteName.SiteName_ID
	,ServerIP.ServerIP_ID
	,Method.Method_ID
	,URIstem.URIstem_ID
	,URIquery.URIquery_ID
	,STG.Port
	,ClientUSerName.ClientUSerName_ID
	,ClientIP.ClientIP_ID
	,STG.Status
	,STG.SubStatus
	,STG.Win32Status

FROM dbo.STG_IISLog AS STG
	LEFT JOIN dbo.DW_DIM_ClientIP AS ClientIP
		ON STG.ClientIP = ClientIP.ClientIP
	LEFT JOIN dbo.DW_DIM_ClientUSerName AS ClientUSerName
		ON STG.ClientUserName = ClientUSerName.ClientUserName
	LEFT JOIN dbo.DW_DIM_Method AS Method
		ON STG.Method = Method.Method
	LEFT JOIN dbo.DW_DIM_ServerIP AS ServerIP
		ON STG.ServerIP = ServerIP.ServerIP
	LEFT JOIN dbo.DW_DIM_ServerName AS ServerName
		ON STG.ServerName = ServerName.ServerName
	LEFT JOIN dbo.DW_DIM_SiteName AS SiteName
		ON STG.SiteName = SiteName.SiteName
	LEFT JOIN dbo.DW_DIM_URIquery AS URIquery
		ON STG.URIquery = URIquery.URIquery
	LEFT JOIN dbo.DW_DIM_URIstem AS URIstem
		ON STG.URIstem = URIstem.URIstem
WHERE [Date] NOT Like '#%'	-- Excluir encabezados

Con esto, ya tenemos nuestro pequeño DataMart para poder consultar de una forma más profesional, el contenido de nuestros ficheros de Log de IIS. El siguiente paso, por supuesto, sería la creacion de un Cubo de Analysis Services, lo cual implicaría una enorme mejora del rendimiento, al tratarse de información agregada y fuertemente indexada, que podría consultarse mediante el Lenguaje MDX o a través de Tablas Dinámicas (Pivot Table) de Excel (lo más cómodo, sin duda).

Como dato curioso de este DataMart, durante las pruebas realizadas, el almacenamiento de 21.000.000 de filas en el Stagging costaba 6GB mientras que en la tabla de hechos sólo 1,2GB, minimizándose seriamente el tiempo de respuesta de las consultas (evidentemente, al minimizar las operaciones de acceso a disco).

Y poco más, aprovecho para colgaros unos ficheritos con el código necesario para que podáis reproducirlo fácilmente, en vuestro trabajo o en vuestra casa.

	Descargar Fichero de Formato (LogIIS.fmt)
	Descargar sentencias DDL de creación de las tablas en SQL Server (LogIIS_crear_tablas.sql)
	Descargar sentencias DML de cargar las tablas en SQL Server (LogIIS_cargar_tablas.sql)

Como siempre, espero que os guste !