Tuesday, June 01, 2010

Loading IIS logs into a SQL Server Database

Tools required:
Microsoft Log Parser : http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en
SQL Server Instance

Command:

LogParser "SELECT * INTO webLog FROM “S:\ex100515.log" -i:W3C -o:SQL -server:sqlServerName -database:IISLogs -driver:"SQL Server" -createTable:ON -username:yourSQLUsername -password:yourSQLPassword

note: if you wish to use integrated security (your windows account) to access the database, then, simply dont use the username and password fields.

If you need to import multiple log files, then you can specify a wild card instead of a specific file name (eg: s:\*.log).

List of fields that are imported:

image

note 2: Depending on the speed of your machine, size of the log files, etc., this can be a long operation and you wont get any indication on whether it is running or not, just be patient. (it took me about 4 minutes to import a file with 300k lines).

No comments: