Working with IIS 5.0 Logs

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, page 61, shows the Data Definition Language (DDL) specifications I wrote to create the Iislogs table Inlogdata.

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.

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" (http://support.microsoft.com/support/kb/articles/q254/7/18.asp) 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.

End of Article

   Previous  1  [2]  Next