Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Dec 1, 2006

Using SQL for IIS Web Logs, Part 1

By Don Schlichting

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:

http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/676400bc-8969-4aa7-851a-9319490a9bbb.mspx?mfr=true

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, we’ll 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, we’ll 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.

QUERIES

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.

Conclusion

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, we’ll examine populating SQL from text based log files that include IIS extended fields and can be used in high volume sites.

» See All Articles by Columnist Don Schlichting



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM