Bad Smells

Sorry about that smell – it is my stale blog. Fixing now...

Log Parser

Like my compiler, Log Parser is an essential tool for this ADC. It helps me breath.

Picture yourself looking for a needle in a haystack of logs – Windows Event, IIS, Netmon, “custom” logs in various formats, Perfmon (more on this later), etc. Multiple megabytes of all this stuff. How would you proceed?

The first time I was presented with this, I scratched my head too. Findstr and a trusty text editor can only get you so far.

Then I found Log Parser...from the docs:

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.
The world is your database with Log Parser.

Most software is designed to accomplish a limited number of specific tasks. Log Parser is different... the number of ways it can be used is limited only by the needs and imagination of the user.
If you find a creative way to use it, let us know at www.logparser.com!

Log Parser in Action

My problem? Look for a bunch of IIS requests that met a specific criteria for a specific time window. Respect the fact that the IIS logs were in UTC time (report them in local time) and there are a bunch of them AND each file is *huge*.

My solution? Log Parser delivers on its promise that the world is my database. I was able to quickly create a reusable script file and "select *" my way to glory. You need to download Log Parser (I tend to add the default install path to my PATH) and away you go - the accompanying help is great but the book is a must !

The command line (note the web query like syntax to pass in input parameters to the script):

logparser -i:IISW3C file:iiswindow.sql?INFILE=IIS\*.log+OUTFILE=IISwindow.csv+WINDOWDATE='20060404'+STARTTIME='14:00:00'+ENDTIME='17:00:00'

This lets Log Parser know that my input script is called IISWindow.sql and it is going to receive an IIS log of type IISW3C (there are many input formats that Log Parser natively understands). It then goes on to say that this particular script file has a bunch of input parameters that will be used in the script - INFILE, OUTFILE, WINDOWDATE, etc.

The actual script file (-- is the syntax for comments):

SELECT TO_LOCALTIME(TO_TIMESTAMP(date, time)) AS LocalDateTime,c-ip,cs-method,cs-uri-stem,cs-uri-query,sc-status,sc-win32-status,sc-bytes,cs-bytes,time-taken,cs(User-Agent)
INTO %OUTFILE%
FROM %INFILE%
WHERE
-- only focus on ASP and SOAP requests
--(cs-uri-stem LIKE '%.asp%' OR cs-uri-stem LIKE '%.wsdl%')
--AND
TO_STRING( LocalDateTime, 'yyyyMMdd' ) LIKE %WINDOWDATE%
AND
(TO_STRING( LocalDateTime, 'hh:mm:ss') >= %STARTTIME% AND  TO_STRING( LocalDateTime, 'hh:mm:ss') <= %ENDTIME% )

DONE! I was able to get a nice little CSV file that I could have had Log Parser turn into a chart on my behalf or (the path I took) bring it into Microsoft Excel and then use simple Excel tricks - sorting, filtering, pivot tables to get a better view of the data.

Too much FileMon data

I too need Sysinternals.com tools to get by. Ever have too much FileMon data to review? My fix:

-- Command Line is:
-- logparser -i:TSV -iSeparator:tab -iHeaderFile:filemon_header.txt file:test1.sql -o:CSV -headerRow:OFF
SELECT *
INTO filemon_test1.csv
FROM huge_filemon.LOG
WHERE Path = 'D:\boo.dll'

The header being a simple file called filemon_header.txt - this is how you tell Log Parser about custom formats.

# Time Process Request Path Result Other

I then have a simple CSV file for Microsoft Excel.

A note about Perfmon data

Log Parser can do TSV formatted Perfmon files just fine. However, sometimes, all you get is the raw BLG Perfmon files. These are unreadable with today's version of Log Parser. That's where Relog can help. When presented with BLG files, I usually run this relog magic to get an input file for Log Parser:

1. Get all the counters in the blg file and write them to counters.txt

relog file.blg -q -o counters.txt

2. Modify the list of counters in counters.txt, rename to mycounters.txt and then relog only data we want contained in mycounters.txt

relog file.blg -cf mycounters.txt -f csv -o perftest.csv

 

Good Smells

I don’t remember how I got hooked on Log Parser, but it has been a godsend. Hopefully you will learn to love it. From the unofficial Log Parser Site: Log Parser is essentially a skunkworks project from one of Microsoft's developers.

Skunkworks indeed…but it smells so good!