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:
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-cd458765
Then, 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.
Add a log file to analyze:
- Click on the fifth icons “Choose log files/folder to query”
- Click on “Add Files” or “Add Folder” and select the IIS log file to analyze or the folder containing IIS logs
- Click on OK
Select the rule to execute by double clicking on “IIS: Top 25 Slow URLs”
- You should be able to see the detail of the LogParser query at the bottom of the window
- Then, click on the red circle with the exclamation mark to execute the query. In few seconds you should see the results:
It’s as simple as this.
Then, some nice features are included in LogParser Studio:
- Automatically generate graphs from the results:
Just click on the Chart icons to generate a graph:
- Export the query as a PowerShell script:
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