Aaron Johnson Now with 50% less caffeine!

Microsoft Log Parser in action

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).

That is hot.

I just started using it, and that is so easy.

Damn.

Thanks for the link!!!

I was trying to find a log analyzer that would give me the number of Mac IE users on a website. Many stats programs will give you OS stats and browser stats but not both combined. It took no time at all with this helpful program
Thanks for the link + tips on using it!

Wow, sounds cool — I have to check that out.

On the disk space requirements issue: you should compress your file system (the parts where you unzip files) if you are on win2k or winxp and using NTFS.

For using zip files with log parser can you not use the pipe command as this is command line option and take input from out put stream of pkupzip another comand line program for unzipping. Definately you will need sapce on harddisk to have temp file created for pipe command. This will be about the size of largest single unzip file rather then all files unzipped. I hope this helps.

Is there any way to deal with zipped log files without unzip them to temp directory for Log Parser to parse them? I have around 1GB log files everyday so that I don’t like to unzip them first.

Thanks,
Mike

Might try the Command Line Extension for winzip, available at winzip.com. I haven’t used it with the MS Log parser, but will give it a go. I’m currently having fun with Webtrends, and looking at other solutions to get the reports I need.

From one Aaron to another, thanks for this sweet tip. The download URL on Microsoft’s site has changed, however:

http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&DisplayLang=en

LogParser 2.2 is now the latest version.

It is listed in the ASP.net sandbox projects as well. So what does it mean? Are they going to create a web interface or windows interface of this logparser? or any other development.
http://www.asp.net/downloads/teamprojects/default.aspx?tabid=62