Often, especially in a Web Farm, we have more than a Web Server (with IIS) and we need to analyze its logs in a quicker and simpler way than
importing them manually in an Excel Sheet.
The solution comes from Microsoft (as usual) with a tool made to manipulate IIS Logs: Log Parser (version 2.2 at the time I’m writing).
Log Parser is a command-line tool with multi-source / multi-target log parsers; the flexibility of the tool has to be paid with a complex syntax.
In my daily job I had the need of importing IIS Logs into a SQLServer Db for analyzing it: here’s the command line I used to accomplish the task.
"C:\Program Files\Log Parser 2.2\LogParser.exe" "SELECT * INTO IISLogs FROM ex20100726.log -i:W3C -o:SQL -server:DB01 -database:Logs -driver:"SQL Server" -username:DbUser -password:DbPassword -createTable:ON
Naturally, the command HAS TO BE customized with:
- File Name of the Log File from the IIS Server [ex20100726.log in my example]
- Database coordinates (Server Name, Db Name,Login, Password) [DB01, Logs, DbUser, DbPassword in my example]
- Table [IISLogs in my case]
Be aware of the parameter createTable:ON that will create a table with the schema needed to import the Log.
In case you need to import Logs with different columns (e.g. you needed to have the Time Taken field later than
production day), as my personal suggestion, do it in different tables.
In the next part of the micro-series, I’ll explain how to do a simple script to import (and eventually delete) multiple files thru
Log Parser.
Posts
i am also using query specified by you above, but i am getting null values in my sql table.
ReplyDeletePlease help me.
Just be aware that the -i flag might need to be changed - I was continually getting errors importing files where occassionally it was intepreting the username as an integer where all the usernames were numbers and trying to write it into a string field based on other log files and therefore failing. The reason turned out to be because I used the -i:W3C as above which tries to guess the field type, rather then -i:IISW3C which was more appropriate and knew the appropriate field types.
ReplyDelete