In a previous blog article I've detailed how to analyse IIS Logs using Excel. However, when you get very huge logs or when you want to automate this operation, Excel is not the best way to do. It's where LogParser 2.2 takes over. It allows you to parse any kind of logs (IIS, HTTPErr, Event Logs…) using a programming language similar to SQL. LogParser 2.2 is great but you just get the command-line tool and then… You've got to find the queries yourself.
Of course, many web sites on Internet are sharing LogParser requests like: - http://support.microsoft.com/kb/910447/en-us - http://blogs.msdn.com/b/mmcintyr/archive/2009/07/20/analyzing-iis-log-files-using-log-parser-part-1.aspx - …Once you get all the queries you need, you can automate them by using a batch file or a PowerShell script.However, when you start to use LogParser, it's not really easy to know how to look at what you want in the IIS Logs and sometimes, it's even difficult to know what to look at.LogParser Studio is implementing several pre-built queries which is great to start or to give ideas to go further and develop your own queries.Then you'll be able to copy those requests to use it directly with LogParser 2.2 using command-lines if you're not interested in the UI.
First of all, install LogParser Sutdio which is available here: http://gallery.technet.microsoft.com/Log-Parser-Studio-cd458765Then, launch it. You'll be able to see the Library tab with the pre-built queries:
Every entry that starts with IIS is, of course, to analyze IIS Logs. I'll show you an example using "IIS: Top 25 Slow URLs", however, don't hesitate to play with the other queries.
It's as simple as this. Then, some nice features are included in LogParser Studio:
Just click on the Chart icons to generate a graph:
And more. Just have a look at this tool it's really great.
Once you'll be at ease with the tool and the queries, you'll surely want to develop your own queries.Just press the New Query button and you'll be able to execute what you want:
To give you some ideas:
Sort out the entries between two specific times. Really useful when the log size is huge and you know when the issue occurred.This will allow you to reduce the size of the data to analyze by only viewing data during a specific time:Select TO_TIMESTAMP(TO_DATE(date), TO_TIME(time)) AS Timestamp, sc-status as Status, sc-substatus as Sub-Status, cs-method as Method, cs-uri-stem as URL, time-taken as Time-Taken FROM '[LOGFILEPATH]' where to_time(time) between timestamp('07:43:55','hh:mm:ss') and timestamp('07:44:21','hh:mm:ss') group by Timestamp, sc-status, sc-substatus, cs-method, cs-uri-stem, time-taken order by Timestamp, sc-status, sc-substatus, cs-uri-stem, time-taken asc
Sort out every error messages from the HTTPErr log file except the Timer_ConnectionIdle which is generally not an issue:Select TO_TIMESTAMP(TO_DATE(date), TO_TIME(time)) AS Timestamp, c-ip, c-port, s-ip, s-port, cs-version, cs-method, cs-uri, sc-status, s-siteid, s-reason, s-queuename FROM '[LOGFILEPATH]' where s-reason <> 'Timer_ConnectionIdle'
In addition, LogParser can be used to detect or check if you're facing an attack.The two following articles are very good articles to look at: - DoS Attacks: http://technet.microsoft.com/en-us/magazine/2006.03.insidemscom.aspx - Checking for intrusions: http://www.symantec.com/connect/articles/forensic-log-parsing-microsofts-logparser
I hope this article will be useful.Sylvain Lecerf and the French Microsoft Support Team
Thank you - good summary!
Nice work thanks!
This is really good. Where and how i can run query manually like the one mentioned in your post? any other help full queries ?
When i launch the Parser studio, i see loading library... and it stays like that for a while.
Anything new about getteing fixed parsing logs from the Advanced Log module? Still not working.