Using SQL for IIS Web Logs, Part 1
December 1, 2006
This article explores the benefits and various methods of using Microsoft SQL Server to analyze IIS Web Server log files.
The web server included with Microsofts Internet Information Services (IIS) can be configured to keep detailed logs of web visits. There are many advantages to keeping web logs, including the ability to detect growth and usage patterns, error conditions, and visitor information such as the clients IP address and web browser used. Although the IIS web log is easily configured, there are no built in Microsoft tools for detailed web log analysis. In addition, while there are many third party tools for log analysis, often the report details you may need are very specific to your site and may not be covered in a strand report. For these situations, Microsoft SQL Server can make a convenient and functional log repository and reporting engine.
Usually, IIS will write the log to a text file. When this happens, an extra step of moving the log data from the text file into SQL is needed. However, this default behavior can be overwritten, and the IIS log can be directly saved inside a SQL table. This method is called ODBC Logging.
Before proceeding though, two disadvantages to this method need to be evaluated for your specific web site. First, IIS requires more overhead and resources to write directly to SQL rather than a text file. If your site has high traffic, this method may not be a good fit. Second, the information captured by the ODBC logging method is fixed and cannot be altered. So if there a field of information needed, such as the clients Referrer, that is not included in ODBC logging, but is included in text file logging, then the ODBC method will not be viable. For a complete list of available fields that can be logged and their definitions, see the following Microsoft page at:
In addition, ODBC logging is not an available option for Windows 2000 Pro or XP. If none of these issues present a problem for your web sites logging, then ODBC is very convenient.
Configuring ODBC Logging
To begin, create a database and table to hold the log file. The following script creates both. The table section can also be found in a script called LogTemp.sql located in Windows\System32\Inetsrv directory.
USE master; GO CREATE DATABASE IIS; GO USE IIS; GO create table InternetLog ( ClientHost varchar(255), username varchar(255), LogTime datetime, [service] varchar( 255), machine varchar( 255), serverip varchar( 50), processingtime int, bytesrecvd int, bytessent int, servicestatus int, win32status int, operation varchar( 255), target varchar(255), parameters varchar(255) );
Now, well create a SQL user with permissions on the InternetLog table. From the SQL Management Studio, expand the Security folder, right click the Logins folder, and then select New Login. On the General tab, create a Login name and password and change the Default database to IIS.
Click the User Mapping tab, then select IIS as a mapped database, and check the db_datawriter role, then OK.
Next, well create a System DSN in Windows. Open the Data Source ODBC icon located in the Administrative Tools group. This example assumes SQL is located on the same machine as IIS.
1. Select the System DSN tab, then Add.
2. At the bottom, select SQL Server, then finish.
3. Enter HTTPLog as the Name, and enter the server name, then Next.
4. Change the default of With Windows NT Authentication to With SQL Server
5. Click the Client Configuration button.
6. Set the Network Library to Named Pipes, and then click OK.
7. Enter the Login ID of IISLog along with its password in the Connect to SQL section, and then click Next.
8. Check the box for Change default database and select IIS, then click Next.
9. Click Finish, then Test Data Source. You should receive the message TESTS COMPLETED SUCCESSFULLY!.
Check the SQL Server to make sure Named Pipes is enabled. In SQL 2005, this can be done from the SQL Server Configuration Manger, under the Protocols section.
The final series of steps is to configure IIS to use ODBC logging. From the Internet Information Services Manager, right click the web site name then select Properties. Check the Enable logging box and choose ODBC as the Active log format. The table name should be InternetLog, the user name is IISLog, and the password is the one created previously. Click OK to save your changes. Next stop and start the Web Site by clicking the Stop Item icon (square black box) on the IIS Manager menu bar. As a test, open a browser and go to the web site. Now in SQL open the InternetLog table. There should be a record of your visit.
Now with the data being logged into SQL, the InternetLog table can be queried for statistics. A common report may be a count of the most hit pages. The following script produces a list of pages viewed, ordered by the most popular first.
SELECT Target, COUNT(Target) as Hits FROM InternetLog WHERE LogTime BETWEEN '01/01/2006' AND '12/31/2006' GROUP BY Target ORDER BY Hits DESC
The example brings up an important point. IIS is logging hits not only to HTML pages, but also any image or cascading style sheet included on that page. There are a couple of different options for weeding these out of the results. One would be to modify the query to only look for HTML pages (WHERE Target LIKE *HTML). Another option, if all the images were located in a common folder, would be to navigate to that folder from the IIS Manger, right click on it, and then uncheck the Log Visits check box.
IIS Web Logs can provide a great deal of insight about web servers. The logs can be tightly coupled to SQL Server to allow for automatic inserts. There are a couple of restrictions to this tightly coupled method though. One is your web site must have low traffic; otherwise the server may slow as recourses are consumed for the ODBC logging. The other issue is the limited number of fields that can be report on. If either of these is problematic for your site, then ODBC is not an option. However, SQL Server still can be. Next month, well examine populating SQL from text based log files that include IIS extended fields and can be used in high volume sites.