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.