Analyze your IIS Log Files - Favorite Log Parser Queries
[アーティクル]
The other day I was asked if I knew about a tool that would allow users to easily analyze the IIS Log Files, to process and look for specific data that could easily be automated. My recommendation was that if they were comfortable with using a SQL-like language that they should use Log Parser. Log Parser is a very powerful tool that provides a generic SQL-like language on top of many types of data like IIS Logs, Event Viewer entries, XML files, CSV files, File System and others; and it allows you to export the result of the queries to many output formats such as CSV (Comma-Separated Values, etc), XML, SQL Server, Charts and others; and it works well with IIS 5, 6, 7 and 7.5.
To use it you just need to install it and use the LogParser.exe that is found in its installation directory (on my x64 machine it is located at: C:\Program Files (x86)\Log Parser 2.2).
I also thought on sharing some of my favorite queries. To run them, just execute LogParser.exe and make sure to specify that the input is an IIS Log file (-i:W3C) and for ease of use in this case we will export to a CSV file that can be then opened in Excel (-o:CSV) for further analysis:
Number of Hits per Client IP, including a Reverse DNS lookup (SLOW)
SELECT c-ip As Machine, REVERSEDNS(c-ip) As Name, COUNT(*) As Hits FROM c:\inetpub\logs\LogFiles\W3SVC1\* GROUP BY Machine ORDER BY Hits DESC
Machine
Name
Hits
::1
CARLOSAGDEV
57
127.0.0.1
MACHINE1
28
127.X.X.X
MACHINE2
1
Top 25 File Types
SELECT TOP 25 EXTRACT_EXTENSION(cs-uri-stem) As Extension, COUNT(*) As Hits FROM c:\inetpub\logs\LogFiles\W3SVC1\* GROUP BY Extension ORDER BY Hits DESC
Extension
Hits
gif
52127
bmp
20377
axd
10321
txt
460
htm
362
Top 25 URLs
SELECT TOP 25 cs-uri-stem as Url, COUNT(*) As Hits FROM c:\inetpub\logs\LogFiles\W3SVC1\* GROUP BY cs-uri-stem ORDER By Hits DESC
Url
Hits
/WebResource.axd
10318
/favicon.ico
8523
/Tools/CodeTranslator/Translate.ashx
6519
/App_Themes/Silver/carlosag.css
5898
/images/arrow.gif
5720
Number of hits per hour for the month of March
SELECT QUANTIZE(TO_LOCALTIME(TO_TIMESTAMP(date, time)), 3600) AS Hour, COUNT(*) AS Hits FROM c:\inetpub\logs\LogFiles\W3SVC1\* WHERE date>'2010-03-01' and date<'2010-04-01' Group By Hour
Hour
Hits
3/3/2010
10:00:00
33
3/3/2010
11:00:00
5
3/3/2010
12:00:00
3
Number of hits per Method (GET, POST, etc)
SELECT cs-method As Method, COUNT(*) As Hits FROM c:\inetpub\logs\LogFiles\W3SVC1\* GROUP BY Method
Method
Hits
GET
133566
POST
10901
HEAD
568
OPTIONS
11
PROPFIND
18
Number of requests made by user
SELECT TOP 25 cs-username As User, COUNT(*) as Hits FROM c:\inetpub\logs\LogFiles\W3SVC1\* WHERE User Is Not Null GROUP BY User
User
Count
Administrator
566
Guest
1
Extract Values from Query String (d and t) and use them for Aggregation
SELECT TOP 25 EXTRACT_VALUE(cs-uri-query,'d') as Query_D, EXTRACT_VALUE(cs-uri-query,'t') as Query_T, COUNT(*) As Hits FROM c:\inetpub\logs\LogFiles\W3SVC1\* WHERE Query_D IS NOT NULL GROUP BY Query_D, Query_T ORDER By Hits DESC
Query_D
Query_T
Hits
Value in Query1
Value in T1
1556
Value in Query2
Value in T2
938
Value in Query3
Value in T3
877
Value in Query4
Value in T4
768
Find the Slowest 25 URLs (in average) in the site
SELECT TOP 25 cs-uri-stem as URL, MAX(time-taken) As Max, MIN(time-taken) As Min, Avg(time-taken) As Average FROM c:\inetpub\logs\LogFiles\W3SVC1\* GROUP BY URL ORDER By Average DESC
URL
Max
Min
Average
/Test/Default.aspx
23215
23215
23215
/WebSite/Default.aspx
5757
2752
4178
/Remote2008.jpg
3510
3510
3510
/wordpress/
6541
2
3271
/RemoteVista.jpg
3314
2
1658
List the count of each Status and Substatus code
SELECT TOP 25 STRCAT(TO_STRING(sc-status), STRCAT('.', TO_STRING(sc-substatus))) As Status, COUNT(*) AS Hits FROM c:\inetpub\logs\LogFiles\W3SVC1\* GROUP BY Status ORDER BY Status ASC
Status
Hits
200
144
304
38
400
9
403.14
10
404
64
404.3
2
500.19
23
List all the requests by user agent
SELECT cs(User-Agent) As UserAgent, COUNT(*) as Hits FROM c:\inetpub\logs\LogFiles\W3SVC1\* GROUP BY UserAgent ORDER BY Hits DESC
UserAgent
Hits
iisbot/1.0+(+https://www.iis.net/iisbot.html)
104
Mozilla/4.0+(compatible;+MSIE+8.0;…
77
Microsoft-WebDAV-MiniRedir/6.1.7600
23
DavClnt
1
List all the Win32 Error codes that have been logged
SELECT sc-win32-status As Win32-Status, WIN32_ERROR_DESCRIPTION(sc-win32-status) as Description, COUNT(*) AS Hits FROM c:\inetpub\logs\LogFiles\W3SVC1\* WHERE Win32-Status<>0 GROUP BY Win32-Status ORDER BY Win32-Status ASC
Win32-Status
Description
Hits
2
The system cannot find the file specified.
64
13
The data is invalid.
9
50
The request is not supported.
2
A final note: any time you deal with Date and Time, remember to use the TO_LOCALTIME function to convert the log times to your local time, otherwise you will find it very confusing when your entries seem to be reported incorrectly.
If you need any help you can always visit the Log Parser Forums to find more information or ask specific questions.
Any other useful queries I missed?
Comments
Anonymous
May 26, 2010
Found this query for standard deviation on: www.codinghorror.com/.../microsoft-logparser.htmlSELECT DIV ( MUL(1.0, SUM(time-taken)), Hits ) as AvgTime, SQRROOT ( SUB ( DIV ( MUL(1.0, SUM(SQR(time-taken)) ), Hits ) , SQR(AvgTime) ) ) AS StDev
Anonymous
February 06, 2012
Thanks for the samples. I will be sure to give a link to this blog post whenever I teach IIS courses
Anonymous
February 06, 2012
Thanks for the samples. I will give this link next time I'll do an IIS course
Anonymous
August 07, 2012
This is a very good blog and helpful
Anonymous
August 22, 2012
i dont see csv file created. is it copy and pask from cmd prompt
Anonymous
September 11, 2012
vgullu: CSV is how the data is organized. Just use > file.csv to redirect the organized data to a CSV file and open with Excel or Calc.
Anonymous
September 22, 2012
every topic is very importan in the lern about to differents aplication suport part, i hope put in in practice every concept
Anonymous
January 21, 2013
Thanks a lot for your blog, it helped me a lot..Awesome stuff
Anonymous
March 04, 2013
This is all excellent stuff... but none of it works if you switch on advanced logging in IIS 7
Anonymous
July 10, 2013
Thanks a lot, it helped me a lot :)
Anonymous
April 01, 2014
Very helpful blog. We were looking to find a particular string in the IIS logs and on finding the occurrence wanted to list the username and how may times they occurred. Finally output to a grid for easy reading. Hope the following piece of code helps:LogParser.exe -i:W3C "SELECT cs-username As UserName, COUNT(*) as TimesOccured FROM 'File or foldername' WHERE cs-uri-query LIKE '%string%' and UserName Is Not Null GROUP BY UserName" -o:DataGrid
Anonymous
February 02, 2015
Nice blog, very very useful for deriving information out of IIS log data!!..thanks!!!
Anonymous
April 27, 2015
Nice blog and saves my time. A big thanks.