��

Select the product you need help with

�� SQL Server �� Web

��. ��: 296085 - �� .

��: ��

��

�� | ��

��

Internet Information Server/Services �� web. �� , �� . �� web.

�� IIS �� World Wide Web Consortium (W3C) �� Microsoft SQL Server �� IIS. �� .

�� | ��

��

�� Web �� RFC 2616, "Hypertext Transfer Protocol--HTTP/1.1")http://www.RFC-Editor.org/RFC/rfc2616.txt

(http://www.rfc-editor.org/rfc/rfc2616.txt)

).

�� W3C �� : �� , [c-ip] �� IP �� -��, [cs-��] �� HTTP �� ; [cs-uri-��] �� ; [cs-uri-��] �� , [sc-status] �� ; [sc-byte ��] �� byte �� : [�� ] �� ), [cs(Cookie)] �� cookie � �� ;) �� [cs(Referer)] �� URL �� . For the W3C Extended Logging format, there are a number of additional fields that can be chosen, which are described in IIS Help and at the following Microsoft Web site:

http://msdn.microsoft.com/en-us/library/ms525152.aspx

(http://msdn.microsoft.com/en-us/library/ms525152.aspx)

The logs are formatted as follows:

��

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 theWeb Sitetab, and in the case of W3C Extended Logging, theExtended PropertiesTAB. 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:

296093

(http://support.microsoft.com/kb/296093/EN-US/ )

FILE: PrepWebLog Utility Prepares IIS Logs for SQL Bulk Insert

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 that by default web logs are recorded in Greenwich mean time, so unless the 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.

�� | ��