A Quick Look at Log Parser

This morning I was alerted to the fact that one of our clients was having problems with our application last night. Unfortunately, none of their end users had captured any of the error messages. I checked the SQL and Windows Event logs but found nothing helpful. Then I had a look in into the IIS web service logs and found some clues.

Working with IIS logs is a bummer. They’re big, unwieldy text files and just a pain to work with. I asked on Twitter for suggestions. Jjakubowski replied with a link to Log Parser so I went to have a look. As soon as I saw the page I recalled that I’ve actually used this free Microsoft utility in the past (man, my memory…). I recall that I wasn’t entirely comfortable with it at the time but decided to dig a little deeper.

Long story short: This thing rocks. It can parse and process just about anything:

Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows® operating system such as the Event Log, the Registry, the file system, and Active Directory®. You tell Log Parser what information you need and how you want it processed. The results of your query can be custom-formatted in text based output, or they can be persisted to more specialty targets like SQL, SYSLOG, or a chart.

I rolled up my sleeves, wrote some SQL and after a bit of experimentation had some great queries built that really helped show where the issues were.

I’m not going attempt to build a tutorial on how to use this beast, but I’ll share a few links and tips that some might find useful.

Tip 1: There’s a help file (.chm) included in the download. Don’t ignore it – it is full of useful information and examples. Seriously. Check the help file before hitting your web search engine of choice. Among other things you can quickly find the column headings (and descriptions) for the type of file you’re working on.

Tip 2: You don’t have to formally install Log Parser.exe on all your machines. Once you have it one you can copy the .exe around. For instance, I copied it up to one of my network shares so that I can easily run it from any of the web servers without having to install anything (I keep some useful .sql files in that same share).

Tip 3: IIS dates are in UTC which can make ‘em a bit of a pain when looking for time-based events. My brain gets tired after having to mentally translate all the times I’m reviewing…

Check out this date handling in this query designed to show errors from the ASP pages:

SELECT  LogFilename, LogRow
, TO_DATE(TO_LOCALTIME(TO_TIMESTAMP(date, time))) as date
, TO_TIME(TO_LOCALTIME(TO_TIMESTAMP(date, time))) as time
, cs-method, cs-uri-stem, cs-uri-query, cs(User-Agent)
FROM <1>
WHERE (sc-status = 500) AND (cs-uri-stem LIKE '%.asp')

Cool, huh? (Cheerfully lifted those functions from this handy little presentation)

And speaking of dates: Suppose you’re looking for recent events in your IIS logs. While you could use some date logic in your query’s WHERE clause, Log Parser will still have to chug through all your log files. That could be a lot of unnecessary processing. Speed things up by using minDateMod parameter to specify the minimum file last modified date (local time) to look at.

So let’s tie that all together. Save that example query up there to a file called ASPerrors.sql. Now run something like this to query everything, but only after midnight on March, 8 2010:

LogParser file:ASPerrors.sql –i:IISw3c -minDateMod:"2010-03-08 00:00:00" –o:datagrid

Tip 4: Check out the datagrid. See that last –o parameter on the example above? That sends the output of your query to a nifty little grid window. This is invaluable while tweaking your query or just doing research. Faster and easier than sorting things out at the command prompt or capture output in text files. Once you have the query nailed down you can change the –o parm to a more appropriate format if desired.

Bonus tip: Click “All rows” when the datagrid shows up after running your query.

Tip 5: Take some time and look at examples on the web. There are tons and you’ll get some great ideas!

Log Parser initially looks a little humble and archaic due the command line nature, but dig into it a bit and you’ll be amazed by the power and potential.

Oh, I should mention that I looked into some GUI apps to help ease using Log Parser but didn’t really hit jackpot. First I tried Visual Logparser but couldn’t get it to even start on my Win7x64 machine. Next I tried Log Parser Lizard. This one shows some real potential, but every now and then it would go to 100% CPU while running a query and never come back. I can’t deny that my query may have been flawed, but I’d rather just get an error

Possibly Related posts:

1. Budget Windows Mirroring
2. IIS: No Monitoring Hits in the Logs
3. A Quick Look at Untangle