How to Enable ODBC Logging for IIS on Win2k
Ok, I recently managed to do this and it was a real pain in the ass to find instructions, and much of it was guess work, so I figured to write up a short description to help other folks out. Seems Microsoft people aren't too keen on spreading help and how-to's, even on the MS web sites.
Overview
What we are going to do is reconfigure IIS so that log files from one entire site will be written via ODBC to a table in a SQL Server database. The process helps you to keep you log files in a format for easy querying and reporting capabilities. You can also monitor the log table as it is being written and don't have to worry about the file being locked while the server is writing to it (as is the case with the standard W3C format I believe). Also, IIS seems to write more quickly to the ODBC log format than the W3C .log files.
The first step is to set up a new database. Then we will need to create the appropriate table for the logging (it has to be in a standard format so this must be done correctly). Next we will establish a new ODBC connection to this database and test it. Finally we will alter our site properties to reflect ODBC logging rather than W3C logging.
Process
1. Create the Database
The first step is to create a new database in your SQL server. The easiest way to do this is with Start -> Programs -> Microsoft SQL Server -> Enterprise Manager. Click the server you want to connect to, then right click on the server and select 'New Database...'. Name your new database and click 'OK' to create it.
2. Create the Table for ODBC Logging
Open Query Analyzer using Start -> Programs -> Microsoft SQL Server -> Query Analyzer. Next locate the file LogTemp.sql. Use Start -> Find, or check the default location in: \WINNT\System32\inetsrv\. The file should look like this:
create table inetlog ( 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) )
Once you have this file, open it. Next start Query Analyzer and connect to localhost as 'sa' with your password. Select the new database you created in Step 1 from the drop down at the top of Query Analyzer, cut and past the SQL code from LogTemp.sql into Query Analyzer and click 'Run'. This will create the new table for logging.
3. Create ODBC
Create a new ODBC connection using Start -> Programs -> Administrative Tools -> Data Sources (ODBC). Select the 'System DSN' tab and click the 'Add...' button, select SQL as the data source type then click 'Finish'. In the resulting screen specify a name and description for your data source and type '(local)' into the server field. Click 'Next', use the default NT authentication (or SQL authentication if you'd rather), click 'Next', check the box that says 'Change the default database to:' and select the database you created in step 1. Then click 'Next' until you get an option to test the new data source. Go ahead and test the source and make sure it connects successfully.
4. Alter the IIS Logging
Open your Computer Manager using Start -> Programs -> Administrative Tools -> Computer Manager. Select and expand the 'Services and Applications' icon tree, select and expand the 'Internet Information Services' icon and then right click on the website you wish to switch to ODBC logging. Check the Properties. In the Properties pop-up, the first tab ('Web Site') has a check box next to 'Enable Logging', make sure this is checked, then change the 'Active log format:' drop down to 'ODBC logging' anc click the 'Properties...' button. Enter the ODBC name that you set up in Step 2, enter a valid username ('sa' for instance) and password to your database server, and make sure the table name reads 'inetlog' or whatever name was specified in logtemp.sql. Next click 'Apply' and then 'OK'.
5. All Done
At this point your logging will be set up. In Query Analyzer use the database you set up in Step 1, and issue a 'select * from inetlog' (or whatever table name specified in step 2), and make sure the table is there. Navigate to the site you altered in a browser, make some requests, and then issue the select statement again to see if logging is working. If no new entries appear in the table check your 'System' event log in Computer Manager and look for error messages.
Tips
Windows 2000 doesn't log errors right away so you may have to wait a little while for error messages to appear. If you run into ODBC errors, check your ODBC connection properties and make sure it isn't using the 'Named Pipes' option. Make sure the account you use to connect through ODBC and in the ODBC logging properties has permissions to write to the inetlog table. Also make sure that the inetlog table is in the proper format. Remember, IIS will not create this table for you, you must do it manually. Check Micorosft's documentation at:
If you get a chance drop Microsoft a line and tell them what crappy documentation they have. Enjoy.