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:

LogParser.exe -i:W3C "Query-From-The-Table-Below" -o:CSV

Purpose Query Sample Output
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
    January 07, 2014
    Why reinvent the wheel?  There are affordable 3rd party tools that do a much better job of this.Check out Angelfish Software:<a href="http://analytics.angelfishstats.com" target="_blank">http://analytics.angelfishstats.com</a>
  • Anonymous
    January 08, 2014
    Can anyone help me in getting the code for reading the IIS logs of last ten minutes or half an hour
  • Anonymous
    January 16, 2014
    Hi All,this is very usefull link for Log Parserwww.hanselman.com/.../ParsingMyIISLogFilesWithLogParser22ToLearnMoreAboutBlogsStatsFromNewsGatorAndNewsGatorOnline.aspx
  • 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.