LogParser to Query IIS logs using SQL
LogParser is a great way to query IIS logs (any text log, actually, that is delimited).
Once you have it installed (default install is to C:\Program Files\Log Parser 2.2), let’s try to query log file ex090915.log from directory C:\WINDOWS\system32\LogFiles\W3SVC1942853941 . The way you would do this, is this:
LogParser "select date, s-ip, cs-method from C:\WINDOWS\system32\LogFiles\W3SVC1942853941\ex090915.log" -rtp:-1
As you can probably imagine, “date”, “s-ip”, and “cs-method” are the column headers from the log file. The select statement goes in quotes. Also, rather naming a table, you give the path to the log file. What’s the argument -rtp:-1 ? If you don’t include this argument, every 10 results, it will prompt you to “press a key…,” then will show you the next batch of results. In any case, the select state we just ran will spit out the following in the console:
date s-ip cs-method ---------- --------------- --------- 2009-09-15 192.168.157.128 GET 2009-09-15 192.168.157.128 GET 2009-09-15 192.168.157.128 GET 2009-09-15 192.168.157.128 GET 2009-09-15 192.168.157.128 GET 2009-09-15 192.168.157.128 GET 2009-09-15 192.168.157.128 POST 2009-09-15 192.168.157.128 POST Statistics: ----------- Elements processed: 27 Elements output: 27 Execution time: 0.02 seconds
LogParser will even generate graphs (.gif format) of your results.
If you want to use a GUI for your queries, I suggest you try Log Parser Lizard.
Categories