Read IIS log with LOG Parser in SQL-Like language

Read IIS log with LOG Parser in SQL-Like language

Before we start discussion regarding reading of IIS log, I would like to request you to please read my previous article which provides introduction of Log Parser along with some sample query. Basic knowledge of log parser required for this article, if you don’t have knowledge of Log Parser, kindly click here.

After having understanding of Log Parser, you have to find IIS log file location for your server. It may be:

C:\inetpub\logs\LogFiles\W3SVC1

or

C:\WINDOWS\system32\LogFiles\W3SVC1

Administrator can change the location of log file from IIS so it is better to confirm the location of log file first before executing the commands given below.

Once you are ready with Log Parse in your server along with path of IIS log file, you can execute following commands to get various information.

Command 1: This command will give you total number of execution of .ASPX page in your IIS based on given file path and date. I wanted to read only one log file “ex121025.log”, you can give “*” there to read all log files available under “W3SVC1” folder.

[sourcecode]
logparser.exe -i:W3C -o:DATAGRID "SELECT COUNT(*) FROM C:\WINDOWS\system32\LogFiles\W3SVC1\ex121025.log where date>’2012-10-19′ AND date<‘2012-10-21’ and cs-uri-stem LIKE ‘%.aspx’"
 [/sourcecode]

Command 2: Following command will return total number of process/pages which takes more than 1 second to load, if we know long running pages, we can troubleshoot it and optimize it as well. Please not that “Time-Taken” field gives value in millisecond

[sourcecode]logparser.exe -i:W3C -o:DATAGRID "SELECT COUNT(*) FROM C:\WINDOWS\system32\LogFiles\W3SVC1\ex121025.log where time-taken>1000" [/sourcecode]

Command 3: following command will return total number of pages whose size is greater than 300KB. We might want to make page little lower in size so that it can be loaded in browser fast.

[sourcecode] logparser.exe -i:W3C -o:DATAGRID "SELECT COUNT(*) FROM C:\WINDOWS\system32\LogFiles\W3SVC1\ex121025.log where cs-bytes>307200"
[/sourcecode]

Command 4: Following command will give you top 10 heaviest pages which has taken long time to load in browser.

[sourcecode]logparser.exe -i:W3C -o:DATAGRID "SELECT TOP 10 cs-uri-stem as URL, MAX(time-taken) As Max, MIN(time-taken) As Min, Avg(time-taken) As Average FROM C:\WINDOWS\system32\LogFiles\W3SVC1\ex121025.log GROUP BY URL ORDER By MAX DESC" [/sourcecode]

Command 5: following command will list out IP with reverse DNS and count how many request came from the IP.

[sourcecode]logparser.exe -i:W3C -o:DATAGRID "SELECT c-ip As IP, REVERSEDNS(c-ip) As DNS, COUNT(*) As Requests FROM C:\WINDOWS\system32\LogFiles\W3SVC1\ex121025.log GROUP BY IP ORDER BY Requests DESC" [/sourcecode]

BTW, “W3C” parameter value provided when you want to read log of IIS for further details about input and output parameter, please refer my previous article Log Parser.

Hope you find it useful.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

3 thoughts on “Read IIS log with LOG Parser in SQL-Like language”

Comments are closed.