I mentioned Microsoft Log Parser a couple months back but never had a chance to actually use it until last Friday when my boss needed to know exactly how many times a certain type of file had been acccessed on our cluster of web servers since the beginning of the year. We have Webtrends, but from what I’ve seen of it, it’s made for presenting a 30,000 foot view of a website, not for getting granular information about a specific URL or subset of a URL. In addition, WebTrends typically breaks down reports into weekly or monthly views, which again was not what I needed in this case.

To make a long story short, after downloading and installing Log Parser, the command line argument to get what I needed into a CSV file (called result.txt in {installation dir}\result.txt) was this:

> logparser "select distinct cs-uri-stem, count(*) FROM D:\logfiles\*.log TO result.txt WHERE cs-uri-stem LIKE '/images/mydir/%.swf' GROUP BY cs-uri-stem" -i:IISW3C -o:CSV

I’ll unzip that a bit. ‘logparser’ is executable you’re running from the command line; make sure that you CD to the directory where LogParser.exe lives (for me it was C:\program files\log parser\LogParser.exe). The second part is the SQL query:

• cs-uri-stem is one of the approximately 33 fields available in the IISW3C log file format,
• distinct and count() are just a couple of the many SQL functions that Log Parser supports
• D:\logfiles\*.log indicates the path to the log files that I want to query (and acts much like a database table as far as SQL goes
• TO result.txt is the endpoint to which I want to pipe the results, you can omit this and have the results printed directly to the command line, I needed the data piped to a file
• WHERE .. notice that Log Parser supports the LIKE keyword and also the GROUP BY keyword
• and finally the -i switch indicates the format of the log files I’m analzying and -o is the format that I’d like the results printed too.

There were a couple of things that initially stumped me. First, it doesn’t appear (again from trial and error) that Log Parser can handle zipped log files, so I had to unzip all the log files, which could have caused a problem since a zipped log file in our environment is usually about 3MB and unzipped can get up to 200MB (this is per day… and I needed the last 6 months). Luckily in this case I had enough disk space but next time I might not have enough room. Second, it seemed that Log Parser attempted to guess at the format of the log file the first time I ran it, but on the second go around, it required that I specify the log file format using the -i command line switch.

All said and done, I’d highly recommend that you add Log Parser to your tool belt. I didn’t even mention that it can export to a variety of formats (W3C formatted text files, IIS log format text files, directly to a database, XML, CSV, or even your own custom log file format) or that it can be scripted using the included LogParser.dll. If you’re curious, download it now and then dive into the included documentation (LogParser.doc).