How To Use SQL Server to Analyze Web Logs

Internet Information Server/Services provides a number of formats to gather data in the form of web logs. For busy sites, these text-based flat files sometime become too much of burden to review and are ignored. A better way to review the data would make these logs a better resource for administrators and web masters.
This article describes a method to import IIS logs in World Wide Web Consortium (W3C) Extended Logging format into Microsoft SQL Server to facilitate the review of the IIS log files. The techniques provided can also be altered for other log file formats.
In W3C Extended Logging format the fields are somewhat self explanatory: data and time are just what they seem; [c-ip] is the IP address of the client; [cs-method] is the HTTP method for the request that was met; [cs-uri-stem] is the document that has been requested; [cs-uri-query] is the query string that was sent as part of the request being logged; [sc-status] is the status code returned by the server; [sc-bytes] is the number of bytes that have been returned to the user; [time-taken] is the time in milliseconds that it took for the server to complete the processing of the request; [cs(Cookie)] is the cookie, or persistent data in the request; and [cs(Referer)] is the URL of the previous site visited by the user

The logs are formatted as follows:

date time c-ip cs-method cs-uri-stem cs-uri-query sc-status sc-bytes time-taken cs(User-Agent) cs(Cookie) cs(Referrer)

The header of the log files corresponds to the fields chosen in the Properties of the Web site, on the Web Site tab, and in the case of W3C Extended Logging, the Extended Properties tab. If your web logs are already in a table in Microsoft SQL Server, it is likely because of ODBC logging. However, when you are using ODBC logging the fields are not configurable. IIS Help has instructions on setting up ODBC logging, which includes using Logtemp.sql to create the table in the expected structure.
You could use Enterprise Manager to create the table, but to make it faster and to aid in the automation of the process, instead use the following script in Query Analyzer to create the table:

CREATE TABLE [dbo].[tablename] (
[date] [datetime] NULL,
[time] [datetime] NULL ,
[c-ip] [varchar] (50) NULL ,
[cs-method] [varchar] (50) NULL ,
[cs-uri-stem] [varchar] (255) NULL ,
[cs-uri-query] [varchar] (2048) NULL ,
[sc-status] [int] NULL ,
[sc-bytes] [int] NULL ,
[time-taken] [int] NULL ,
[cs(User-Agent)] [varchar] (255) NULL ,
[cs(Cookie)] [varchar] (2048) NULL ,
[cs(Referer)] [varchar] (2048) NULL
)
Note that some of these fields are quite large and may not be necessary for reviewing your particular log files.
Once the table has been created, you can import the data by using the Import Wizard, mapping from the *.log file to the database and table.
Using the Wizard can be tedious, so the following can be used to expedite importing the web logs:

BULK INSERT [dbo].[tablename] FROM ‘c:\weblog.log’
WITH (
    FIELDTERMINATOR = ‘ ‘,
    ROWTERMINATOR = ‘\n’
)
Note that the bulk insert will fail when it encounters lines that start with “#”. For web logs, this includes the first four lines, as well as any other instances when the server is stopped and started, since the header lines are written when the service is restarted. The following Microsoft Knowledge Base article provides a utility and source code to remove these lines and prepare the log for the bulk insert to SQL Server: http://support.microsoft.com/kb/296093/EN-US/ FILE: PrepWebLog Utility Prepares IIS Logs for SQL Bulk Insert

Advertisements

One thought on “How To Use SQL Server to Analyze Web Logs

  1. off bai web developarule.. iti ies log-urile afara din pagina :))))
    cs(User-Agent) cs(Cookie) cs(Referrer) n-a mai vrut sa incapa (a se observa ochiul ochiul inca format de tester)
    maine bere ? o//

    btw. cum ti-ai schimbat fav ico ? 😀 😀 share me that secret please.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s