Using SQL for IIS Web Logs, Part 1

Introduction


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 Microsoft’s 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.


ODBC Logging


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:

Don Schlichting
Don Schlichting
Don Schlichting is a Microsoft MSCE, MCDBA and an Oracle Certified Associate. Working at Professional Control Corporation as the IT Director,.

Latest Articles