IIS ODBC SQL Logging

23 Jan

I was basically after a way to make better use of the IIS log files. We use Google Analytics on most of our sites but with all the information being stored in the IIS logs, it made sense to work out a way to extract this data and use it, somehow.

My initial investigation saw me play with the IIS Log Analyzer but this seemed to be far too manual a process for my liking, so I then set about looking to script the import of the log files into SQL, but this seemed over the top, I was sure there was a better solution than this ‘hack’.

I next came across this article which allowed logging to SQL straight from any IIS site by making a few simple changes. Basically you add the sqlLogginModule.cs file to the App_Code folder in your site and then modify the web.config to enable this module. All in all that worked pretty well, but I did run into a few issues when dealing with pre-compiled applications. Whilst searching for an answer to that issue, I then came across the solution I am sill using now.

The article here explains how to setup IIS SQL Logging using an ODBC connection. The article is very straight forward and you can’t really go too far wrong. Make sure that you have Custom Logging and ODBC Logging installed.

Once ODBC connection has been created and the applicationHost.config has been modified with the ODBC connection details, I can then easily start tracking a site by running:

appcmd set sites “My Website Name” -logFile.logFormat:Custom -logFile.customLogPluginClsid:{FF16065B-DE82-11CF-BC0A-00AA006111E0}

(appcmd can be found in ‘C:\Windows\System32\inetsrv’ for reference)

Obviously the down side to this is the overhead that the SQL logging will put on the server. I’m yet to notice any performance decrease, but will obviously be keeping an eye on this and the space used by this database. Down the line I might look at scripts to clean out or archive old data, we’ll see how it goes.

Now that everything is being logged to a SQL database, all that left to do is write the front end (I’ll most probably use .net for this as it makes quite light work of this sort of thing) so that I can make some use of all this data. Watch this space.