Earlier today, after a system outage, I was sent a W3C IIS Log to take a look at.
After staring at this log for a few minutes in Notepad2, my eyes started to glaze over. Basically, staring at system or IIS logs with a text editor is useless if there are tons of events or requests.
I knew there are plenty of parsing tools out there for log analysis, and I had used some before. Unfortunately, I had none of these tools installed on my laptop. Googling quick lead me to a Microsoft tool called ‘LogParser’ that I had never heard of before.
LogParser basically supports a ton of different log formats and gives you the capability to run SQL queries against the log and view the results in tabular format. Super useful!
The app is distributed in (2) parts. One is a command line/service executable that provides the querying engine and the other part is a stand-alone GUI.
https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=24659
https://gallery.technet.microsoft.com/Log-Parser-Studio-cd458765
After installing the engine and then unzipping/running the GUI, you simply load the log file and choose its type…. Then query away! There is also a bunch of pre-generated (included) queries that you can use as a guide for some of the special functions in the SQL implementation.
You can see from the screen shot which log types are supported. In my cases, I was querying against a W3CLog. The things I was looking for were 400/500 series errors and/or extremely long response times.
In the query window, I devised a little query that would give me the information I wanted – time in both utc/local time, http status, response time, and the page/uri that was requested.
SELECT top 1000 TO_TIMESTAMP(date, time) AS utc-timestamp, TO_LOCALTIME(utc-timestamp) AS local-timestamp, cs-uri-stem as Uri,
sc-status as HttpStatus,
sc-substatus as SubStatus,
time-taken as ResTime(ms), DIV(MUL(1.0,time-taken), 1000.0) AS ResponseTime(s) FROM '[LOGFILEPATH]'
WHERE (sc-status <> 304 and sc-status <> 500) and time-taken > 40000
AND local-timestamp between timestamp('2015/05/15 16:30:00', 'yyyy/MM/dd hh:mm:ss') and timestamp('2015/05/15 19:00:00', 'yyyy/MM/dd hh:mm:ss')
order by time-taken desc
With this query, I could see the patterns and failures that I was interested in within the log. Another nice thing about the tabular results that are returned is that they can be further sorted with the column headers. The results can also be copied to sent others or exported.
Along with the included Query Library, it’s simple to save any query that you devices to the library for later use.
This tool has found a nice home in my tool box.