jostein.kjønigsen.net

Database logging: A tiny experiment with IIS7-extensibility

Having recently upgraded to Windows Server 2008 to be able to fool around with IIS7, my main argument was the improved extensibility and ability to inject .NET-code into the IIS-pipeline. Today I gave it a try.

Objective? Get IIS7 to log all requests to a database of my choice. While the regular log-files can provide all the info I need, searching them manually for specific information is just hopeless. Setting up a SSIS-job to import the logs would probably also work, but it would involve external agents polling information, not IIS pushing it.

Solution? Build a .NET HttpModule, inject it into the IIS7 pipeline at server-level, and we have a near no-cost, realtime solution. In IIS6 this would probably have been a lot dirtier to implement, especially if you don't fancy COM, Win32-APIs and C++. IIS7 and the .NET-extensible pipeline makes this job done in a minutes. And this is from scratch. Well. Almost ;)

I started off with googling for similar results, and found something on the IIS-blog. An ASP.NET HTTP-Module for DB-Logging with IIS7. This was pretty close to what I wanted, but not quite there.

What I did, was follow that exact example with a few exceptions:

For the logging table, I added a clustered identity column for future indexing needs, not to mention a referer-column, expanding some of the columns and removed unneeded nullability. My table looks like this:

CREATE TABLE dbo.Log(
	ID int IDENTITY(1,1) NOT NULL,
	Date datetime NOT NULL,
	Method varchar(50) NOT NULL,
	IPAddress varchar(50) NOT NULL,
	Url varchar(400) NOT NULL,
	UserName varchar(255) NOT NULL,
	UserAgent varchar(255) NOT NULL,
	ResponseCode varchar(50) NOT NULL,
	SiteName varchar(50) NOT NULL,
	ApplicationName varchar(50) NOT NULL,
	Referer varchar(400) NOT NULL,
 CONSTRAINT PK_Log PRIMARY KEY CLUSTERED 
(
	ID ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON PRIMARY
)

If you are using Windows Authentication, remember to grant the application-pools used on IIS access to this database and table or you might find yourself with some rather empty log-tables.

Since this is supposed to be a server-wide module and not application-specific, I also moved the connectionString configuration-settings from web.config to applicationHost.config. This makes sure the setting is present for all sites unless overridden. Make sure to take a backup of this file before fooling around.

Making the actual HttpModule, I created a Class-library project and put my file in there instead of making it website-level Application-code. This required manually adding references to System.Web and Microsoft.Web.Administration. Having Windows XP on my development machine, I just copied Microsoft.Web.Administration.dll from the machine running IIS7 to my project-folder and added a reference. This works and builds just fine.

The actual module-code wasn't too bad, but for server-wide deployment it lacked at least one fundamental thing: Error handling. I simply added some error-eating code to prevent errors in my logging-module to stop any request to the entire server. You might want to do this too.

try
{
    // logging code here
}
catch (Exception) {}

I also changed the commandtext and parameters to match my modified schema:

cmd.CommandText = "insert into Log (Date, Method, IPAddress, Url, UserName, UserAgent, "
+ "ResponseCode, SiteName, ApplicationName, Referer) values (@Date, @Method, "
+ "@IPAddress, @Url, @UserName, @UserAgent, @ResponseCode, @SiteName, "
+ "@ApplicationName, @Referer)";

// snip

// slight typo on the parens here to avoid breaking my formating code :)
string referer = httpContext.Request.Headers("Referer");
if (referer == null)
{
    referer = "";
}
cmd.Parameters.AddWithValue("@Referer", referer);

Having done this I was almost done. For IIS to be able to use our module, we need it to be in the Global Assembly Cache. For this the assembly needs to have a strong name and thus needs to be signed.

Having trouble getting this added to the GAC by a simple drag'n'drop in Windows Explorer and not having the .NET SDK (and hence not gacutil.exe) installed on my webserver, I built a Setup project where the only actions to be taken is to deploy the project primary output to the assembly cache. I also excluded the Microsoft.Web.Administration.Dll-dependency which Visual Studio detected, since this should already be present on any machine running IIS7.

The setup built and deployed fine on the webserver. Only one thing remained: Enabling it. To enable it, all you need to do is to click on the machine-node in IIS Manager, select Modules, and from there add your strongly named assembly.

Voila! You now have server-wide SQL backed logging for all our IIS7 traffic! This might seem like much, but honestly: This piece took me much more time writing than the actual work itself did.

I'm sure there are improvements which can be done to this code, but I'm using this right now and it works.