• W3C Extended Log File Format
• NCSA Log File Format
• Microsoft Log File Format
• Logging to any ODBC data source

The default Log File format is W3C Extended Logging format.

Web logs are delimited text files as specified by RFC 2616, "Hypertext Transfer Protocol -- HTTP/1.1" ( http://www.rfc-editor.org/rfc/rfc2616.txt).

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 any cookie data or persistent data in the request
• [cs(Referer)] is the URL of the previous site visited by the user.

For the W3C Extended Logging format, there are a number of additional fields that can be chosen; for more details see this article:

Each line of the log file is formatted as follows:

Note: The W3C Extended Logging format is the only log file format you can use to specify exactly which fields you want to log.

The header of the log files corresponds to the fields chosen in the Logging Properties dialog of the Web site, on the Web Site tab, and in the case of W3C Extended Logging, the Extended Properties tab.

Below is an example of the headers that are written to a log file in W3C Extended Logging format.

#Software: Microsoft Internet Information Services 5.0
#Version: 1.0
#Date: 2002-08-11 05:25:49
#Fields: date time c-ip cs-username s-ip s-port cs-method cs-uri-stem cs-uri-query sc-status cs(User-Agent)
2002-08-11 05:25:49 192.168.1.1 - 192.168.1.1 80 GET /default.asp View=A314 200

You could use SQL Server Enterprise Manager to create the table for your log file, but to make it easier the following TSQL code can be used.

Open SQL Query Analyzer and paste in the following script 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: Some of these fields are quite large and may not be necessary for reviewing your particular log files. Also you should adjust the script if you have selected different fields in the Logging Properties dialog.

Once the table has been created, you can import the data by using the DTS Import Wizard, mapping from the *.log file to the database and table.

Note: You may also use Bulk Import to import the table as the following command shows:

BULK INSERT [dbo].[tablename] FROM 'c:\weblog.log'
WITH (
    FIELDTERMINATOR = ' ',
    ROWTERMINATOR = '\n'
)

Note: Bulk insert will not work with SQL Server 7.0, but it does work with SQL Server 2000. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

Note: Bulk insert will fail if it encounters lines that start with "#"; these lines are used to describe the Software used to create the log file; the version and the date and the fields we are logging as described above.

For W3C Extended Logging 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:

When the import has completed, you can use Query Analyzer to run queries to organize the data.

Select [cs-uri-stem], [time-taken] 
From tablename 
Where [time-taken] >= 20000 and time between '1899-12-30 16:30:00.000' and '1899-12-30 17:30:00.000' 

This query will display the names of all the pages that took more than 20 seconds to process and that were processed between 4:30 and 5:30 PM, along with the process time (in milliseconds) for each.

Note By default web logs are recorded in Greenwich mean time, so unless changes have been made to record the logs in local time, you must adjust for local time when you review the logs.

Another sample query:

Select distinct [cs-uri-stem], [time-taken] 
From tablename 
Where [time-taken] > (select avg([time-taken]) From tablename) order by [time-taken] desc)

This query finds the average [time-taken] for each entry in the log file, and then selects the [cs-uri-stem] and the [time-taken] and organizes this in descending order by [time-taken]. Adding a "time between" clause could further isolate the results of this query.