Working with IIS 5.0 Logs

By Ken Spencer

This article is from the Spring 2001 issue of Windows 2000 Magazine.

Move Web site log data to a SQL Server database for easier analysis

Microsoft Internet Information Services (IIS) 5.0 logs Web-server events by default. You can choose to turn logging off or ignore log data, but more likely, you want to collect and analyze the data for insight into how users use your Web site. Like Internet Information Server (IIS) 4.0, IIS 5.0 gives you a choice of log-file formats and lets you log to a file or directly to a database. My preferred method of collecting data is to log to World Wide Web Consortium (W3C) Extended Log File Format files, then import the files' data into a Microsoft SQL Server database for analysis. In the paragraphs that follow, I show you how to set up such a logging process and point out some minor differences between IIS 5.0 and IIS 4.0.

On This Page

Logging to the W3C Format
Importing Logs into SQL Server
Analyzing Log Data

Logging to the W3C Format

To configure logging (which is enabled by default), you use Internet Services Manager (ISM) 5.0, which functions similarly to Internet Service Manager (ISM) 4.0. IIS 5.0 logging operations are also quite similar to IIS 4.0, and IIS 5.0 supports the same log formats as IIS 4.0:

• W3C Extended Log File Format (exyymmdd.log)

• Microsoft IIS Log File Format (inyymmdd.log)

• National Center for Supercomputing

• Applications (NCSA) Common Log

• File Format (ncyymmdd.log)

• ODBC Logging format

The filename format for logs closed on a daily basis is in parentheses after the log format. According to the Microsoft article "W3SVC and IIS Log File Names Are Listed with NCSA Format in HTMLA" (https://support.microsoft.com/default.aspx?scid=kb;en-us;240027&sd=tech), the IIS 5.0 documentation shows an incorrect prefix for the W3C and Microsoft IIS filename formats. However, the names are correct in my system's documentation.

One difference between IIS 5.0 and IIS 4.0 is that IIS 5.0 supports the ODBC logging option only in Windows 2000 Server products, not in Win2K Professional. For more information about IIS 4.0 logging and the four logging formats, see "IIS 4.0 Event Logging," March 2001.

The W3C Extended Log File Format is probably the most-used log format because it lets you log more information than the other formats and because it's flexible (i.e., you can specify which information to log). By default, IIS 5.0 W3C logging uses Universal Time Coordinate (UTC) time, which is a new name but is the same time as IIS 4.0's default Greenwich Mean Time (GMT). Thus, IIS 5.0 uses UTC rather than local time to determine when to create a new log file, and all the times in the log file are based on UTC. You might want to use UTC if you have many servers spread across several states or countries and you want to synchronize them on one time.

Figure 1: Setting the time for W3C log-file naming and rollover

However, if all your servers are in one location, you probably prefer to use local time. IIS 5.0 gives you an easy way to specify that you want to use local system time for naming and rolling over log files. Open ISM, click the appropriate server, right-click the appropriate Web site, and select Properties. Click the Web Site tab, ensure that the W3C log format is selected, and click Properties. On the General Properties tab, which Figure 1 shows, select the Use local time for file naming and rollover check box. You can use local system time in IIS 4.0 Service Pack 4 (SP4) or later, but to do so, you must edit the registry.

Figure 2: Selecting the W3C properties to log

Figure 2 shows the window that you use to choose which of the W3C format's extended properties to log. This IIS 5.0 window displays the properties in a tree format instead of the fixed list that IIS 4.0 uses. The IIS 5.0 format also displays in parentheses the property names as they appear in log files. This cross-reference is really handy when you need to compare log entries with the properties you chose to log.

IIS 5.0's extended properties are almost the same as IIS 4.0's, with a couple of exceptions. IIS 4.0's HTTP Status property is Protocol Status in IIS 5.0, although the IIS 5.0 documentation shows the property as HTTP Status. The IIS 5.0 Host property is new and contains the server name. If you turn on Process Accounting for an IIS 5.0 Web site, the Web server displays an additional set of W3C log entries.

Table 1 W3C Property Name Prefixes

Prefix	Action
s-	Server actions
c-	Client actions
cs-	Client-to-server actions
sc-	Server-to-client actions

Adapted from the IIS 5.0 online documentation's "Logging Properties Reference" section.

Table 2 W3C Properties

Field	Appearance in Log	Description
Date	date	The date on which the activity occurred.
Time	time	The time the activity occurred.
Client IP Address	c-ip	The IP address of the client (browser or HTTP initiator) that accessed the server.
User Name	c-username	The name of the authenticated user who accessed the server. A hyphen represents anonymous users.
Service Name	s-sitename	The Internet service running on the client computer and the computer's instance number.
Server Name	s-computername	The name of the server on which IIS 5.0 generated the log entry.
Server IP Address	s-ip	The IP address of the server on which IIS 5.0 generated the log entry.
Server Port	s-port	The port number the client is connected to.
Method	cs-method	The action the client was trying to perform (e.g., a GET method).
URI Stem	cs-uri-stem	The resource accessed (e.g., Default.htm).
URI Query	cs-uri-query	The query, if any, the client was trying to perform.
Protocol Status	sc-status	The status of the action in HTTP terms.
Win32 Status	sc-win32-status	The status of the action in Win2K terms.
Bytes Sent	sc-bytes	The number of bytes the server sent.
Bytes Received	cs-bytes	The number of bytes the server received.
Time Taken	time-taken	The length of time the action took.
Protocol Version	cs-version	The protocol (HTTP, FTP) version the client used. For HTTP, this value will be either HTTP 1.1 or HTTP 1.0.
Host	cs-host	The name of the IIS 5.0 server.
User Agent	cs(User-Agent)	The browser the client used.
Cookie	cs(Cookie)	The content of any cookie sent or received.
Referer	cs(Referer)	The previous site that linked the user to the current site.

Adapted from the IIS 5.0 online documentation's "Logging Properties Reference" section.

The W3C "Logging Properties Reference" section of the IIS 5.0 online documentation is cryptic and somewhat confusing. I've reproduced its tables here, fixing a few errors, modifying some text, and changing the column headings to make the tables more readable. Table 1 defines the prefixes for the W3C property names as they appear in the logs. Each prefix identifies the type of action the property relates to. Table 2 describes the W3C properties and corrects the Protocol Status property information. Table 3 shows the new W3C Process Accounting properties, and Table 4 describes the values for Process Accounting's Process Event property.

Table 3 W3C Process Accounting Properties

Field	Appearance in Log	Description
Process Event	s-event	The event that triggered the log entry. See Table 4 for event names and descriptions.
Process Type	s-proc-type	The type of process (i.e., Common Gateway Interface--CGI, Application, or All) that triggered the event.
Total User Time	s-user-time	The total accumulated User Mode processor time, in seconds, that the site used during the current interval.
Total Kernel Time	s-kernel-time	The total accumulated Kernel Mode processor time, in seconds, that the site used during the current interval.
Total Page Faults	s-page-faults	The total number of memory references that resulted in memory page faults.
Total Processes	s-total-procs	The total number of CGI and out-of-process applications created during the current interval.
Active Processes	s-active-procs	The total number of CGI and out-of-process applications running when IIS 5.0 recorded the log.
Total Terminated Processes	s-stopped-procs	The total number of CGI and out-of-process applications stopped because of process throttling during the current interval.

Adapted from the IIS 5.0 online documentation's "Logging Properties Reference" section.

Table 4 W3C Process Accounting Events

Event Name	Description
Site-Stop	The Web site was stopped.
Site-Start	The Web site was started or restarted.
Site-Pause	The Web site was paused.
Periodic-Log	The current log entry is a regularly defined log entry whose interval the administrator specified.
Interval-Start	The Reset Interval has begun.
Interval-End	The Reset Interval has been reached and reset.
Interval-Change	The administrator changed the value for the Reset Interval.
Log-Change-Int/Start/Stop	The log interval was changed, an interval event took place, or the site stopped, started, or paused.
Eventlog-Limit	An event log was closed because a CGI or out-of-process application reached the event-log limit that the administrator set.
Priority-Limit	A CGI or out-of-process application was set to low priority because it reached the low-priority limit that the administrator set.
Process-Stop-Limit	A CGI or out-of-process application stopped because it reached the process-stopping limit that the administrator set.
Site-Pause-Limit	The Web site was paused because a CGI or out-of-process application reached the site pause limit that the administrator set.
Eventlog-Limit-Reset	The Reset Interval was reached, or the Eventlog-Limit was manually reset.
Priority-Limit-Reset	The Reset Interval was reached, or the Priority-Limit was manually reset.
Process-Stop-Limit-Reset	The Reset Interval was reached, or the Process-Stop-Limit was manually reset.
Site-Pause-Limit-Reset	The Reset Interval was reached, or the Site-Pause-Limit was manually reset.

Adapted from the IIS 5.0 online documentation's "Logging Properties Reference" section.

When IIS 5.0 generates a W3C log file, it stamps at the start of the file a header like the one that Figure 3 shows. The header's first line identifies the server type, the second line identifies the log-file version, the third line is the date and time IIS 5.0 created the log file, and the last line is a space-delimited header line containing all the fields in a file row. Be aware that if you change the fields that are logged, IIS 5.0 stamps a new header in the log file, then continues adding rows consisting of the new fields to the file. If you're importing logs into a database, you'll need to make sure the database accommodates all the new log fields.

Importing Logs into SQL Server

If you want to be able to analyze log data, you need to import the data into a database where you can perform queries against it. If you choose to work directly with the log data that IIS 5.0 generates, several options are available to you. You could use the ODBC log format (instead of the W3C format) to log Web site data directly to SQL Server or another ODBC-compliant database. However, this method writes log entries to the database as events happen, so it can be resource-intensive. I prefer to set up a Data Transformation Services (DTS) package in SQL Server to load W3C log files. The package reads from a text file that contains the log data that IIS 5.0 generates and imports the data into SQL Server. Because the package runs only after IIS 5.0 has closed a log, the update can occur in batch mode and not bog down SQL Server or IIS 5.0 with updates each time IIS 5.0 logs an entry.

To routinely import W3C logs into a SQL Server database by using a DTS package, you need to set up a task within SQL Server that performs several jobs. You can schedule IIS 5.0 to close the log file at a certain time each day and schedule the task to run just after the log file is closed. The task must first copy the closed log file to a set input file or rename the log file to the input file.

Next, the task should strip the header information from the input file, deleting completely the first three header lines and deleting "#Fields:" from the fourth header line. The file now has only one header row, which contains the name of each field. Figure 4 shows the first two lines—the header line and one record line—of an input file with six fields. Finally, the task should run the DTS package to load the input file data into the database.

Before your task can run a DTS package to load data into a SQL Server database, you need to create the database and the DTS package. Actually, you could let the DTS package create the database, but I recommend that you create the table yourself. That way, you can create the columns with the correct data types, making it easier to query on numeric or date fields. You can also name the columns with your own names and, using DTS, map the names from the incoming file to the correct columns. I created a new SQL Server 2000 database named Iislogs for log data. Listing 1 shows the Data Definition Language (DDL) specifications I wrote to create the Iislogs table Inlogdata.

LISTING 1: DDL Specifications for Inlogdata Table 
CREATE TABLE [dbo].[InLogdata]
	 [time] [varchar] (255) ,
	 [c-ip] [varchar] (255) ,
	 [cs-username] [varchar] (255) ,
	 [s-sitename] [varchar] (255) ,
	 [s-computername] [varchar] (255) ,
	 [s-ip] [varchar] (255) ,
	 [s-port] [varchar] (255) ,
	 [cs-method] [varchar] (255) ,
	 [cs-uri-stem] [varchar] (255) ,
	 [cs-uri-query] [varchar] (255) ,
	 [sc-status] [varchar] (255) ,
	 [sc-win32-status] [varchar] (255) ,
	 [sc-bytes] [varchar] (255) ,
	 [cs-bytes] [varchar] (255) ,
	 [time-taken] [varchar] (255) ,
	 [cs-version] [varchar] (255) ,
	 [cs-host] [varchar] (255) ,
	 [cs(User-Agent)] [varchar] (255) ,
	 [cs(Cookie)] [varchar] (255) ,
	 [cs(Referer)] [varchar] (255) ,
	 [Col021] [varchar] (255)
) ON [PRIMARY]
GO

To create the DTS package, I started the SQL Server 2000 Enterprise Manager utility, expanded Data Transformation Services, right-clicked Local Packages, and selected New Package. These steps opened the DTS Designer. Next, I clicked Connection on the top toolbar, selected the Text File connection type, and selected the input file from the displayed directory structure. Then, I clicked the Properties button to open the connection's properties. On the first property page, I clicked the Delimited button and selected the First row has column names check box, leaving the default values for the other options.

I then clicked Next, and on the second property page I clicked the Other option and entered a space character in the box to the right of Other to specify that DTS should use a space as the column delimiter. At this point, I could see the columns correctly defined at the bottom of the property page. I clicked Finish, then clicked OK to close the connection's properties.

Next, I added a second connection. I selected Microsoft OLE DB Provider for SQL Server as the Data Source, then selected the server from the Server list. I entered a username and password for the database. I selected the Iislogs database and clicked OK.

Finally, I dragged a Transform Data task from the toolbar (on the left side of the interface) and selected the first connection I created as the source and the second connection as the destination. Then, I saved the package. To execute the package immediately, I clicked the Execute button (a right-pointing arrow) on the top toolbar. Figure 5 shows the log data in Enterprise Manager.

Figure 5: Viewing the imported log data

Analyzing Log Data

After you've loaded a log into SQL Server, you can use SQL to access and analyze the data. For example, the following SQL statement selects all of the data in the log table:

select * from inlogdata

You can order the log information by server with this SQL statement:

select * from inlogdata

order by 's-computername'

You don't need to be a database or SQL expert to work with SQL Server data. You can use programs such as Microsoft Excel, Microsoft Access, and Seagate Software's Seagate Crystal Reports to perform queries and analyses of your data.

When using the W3C log-file format, the Win32 Status field is useful for identifying the errors that an application has reported. For example, to determine what a Win32 Status value of 5 means, issue the following command at the command prompt:

net helpmsg 5

The command will return the error message Access is denied.

The Microsoft article "IIS 'Bytes Sent' (Sc-bytes) Logging Property Is 0 for ASP Files" (https://support.microsoft.com/default.aspx?scid=kb;en-us;254718&sd=tech) notes that W3C's Bytes Sent field might return 0 bytes when Active Server Pages (ASP) applications have buffering turned on. The article suggests turning buffering off. Doing so might cause the Bytes Sent field to report the correct data, but it almost certainly will significantly slow down your application.

As with other operations, managing and analyzing logs is a chore if you try to do it manually. Combining IIS 5.0 logging with SQL Server's DTS lets you automate the process of loading log data into SQL Server for easier analysis.

You can also use other tools such as Commerce Server 2000 Business Analytics to analyze log data. Another approach is to purchase a tool to automate analysis of your log data. For example, WebTrends Log Analyzer has some great reports that you can use to review what's happening with a site.

Yet another approach to gathering Web site usage information is to turn off logging completely and write Internet Server API (ISAPI) filters to capture from the HTTP header the data that interests you. As you can see, you have many choices in the area of collecting and analyzing Web site information.

About the Author

Ken Spencer is a contributing editor for Windows 2000 Magazine and SQL Server Magazine. He is vice president of 32X Tech, does consulting and custom training for developing and hosting applications, and has written several books. You can reach him at https://www.32x.com.

The above article is courtesy of Windows 2000 Magazine. Click here to subscribe to Windows 2000 Magazine.

We at Microsoft Corporation hope that the information in this work is valuable to you. Your use of the information contained in this work, however, is at your sole risk. All information in this work is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Microsoft Corporation. Microsoft Corporation shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages. All prices for products mentioned in this document are subject to change without notice.

Click to Order