Enabling ODBC Logging on an IIS websitewww.tartoos.com

IIS includes a feature whereby you can pipe your logs to an ODBC-compatible datasource instead of the usual flat text file. This can be advantageous for a number of reasons, a major one of which is more flexible reporting - it's much easier to create a custom reporting application if your data is already in a database than to build a parser as well, and of course if you're running a webfarm, you can use the ODBC logging feature to unify together the logfiles from all the separate machines into a single point (preferably a separate machine).

So how do we enable ODBC logging? well it's fairly simple. I'm going to demonstrate by seting up ODBC logging for my local development version of this site, RTFM.www.tartoos.com

First, I fire up SQL Server and add a new database called rtfmlogs. Once I've done this I'll need to set up a System DSN (ODBC data source name) from my control panel.www.tartoos.com

This done, there is still a further step. IIS will not automatically create the database table for you. You need to do that by firing up SQL Query analyzer, clicking open and heading off to %windir%\system32\inetsrv\logtemp.sql. Run this against your new database and we're off to IIS for the last step.www.tartoos.com

Crack open the properties sheet for the chosen site by right clicking the website node and selecting properties. The first tab you see has the loggin options at the foot. Select ODBC logging in the dropdown and hit 'configure'.

You'll be faced with the screen below

Insert the name of your DSN, the name of the table you created earlier, the username and password. Make doubly sure the table name is correct - the defaults for the creation script are different to the defaults IIS will show you!www.tartoos.com

There you go. Now all you need to do is test. Just hit the website in your browser, then do a select from the table in SQL Query Analyzer.

Now. As for whether you should be doing this or not, be aware that there IS a performance hit associated with ODBC logging. A heavily trafficked site won't benefit from this performance-wise, but if you intend to analyze your own logs with SQL, it could be the way to go.