LOG Parser – IIS LOGS Analysis


In this post I will show some examples how to collect information from IIS Logfiles. For this porpuse I will use LogParser version 2.2.

You can download here – http://www.iis.net/community/default.aspx?tabid=34&g=6&i=1976 and read more about it here http://technet.microsoft.com/en-us/library/bb878032.aspx

From command prompt run LogParser.exe /? for Help menu.

C:\Program Files\Log Parser 2.2>LogParser.exe /?
Microsoft (R) Log Parser Version 2.2.10
Copyright (C) 2004 Microsoft Corporation. All rights reserved.
Usage:   LogParser [-i:<input_format>] [-o:<output_format>] <SQL query> |
                   file:<query_filename>[?param1=value1+...]
                   [<input_format_options>] [<output_format_options>]
                   [-q[:ON|OFF]] [-e:<max_errors>] [-iw[:ON|OFF]]
                   [-stats[:ON|OFF]] [-saveDefaults] [-queryInfo]
         LogParser -c -i:<input_format> -o:<output_format> <from_entity>
                   <into_entity> [<where_clause>] [<input_format_options>]
                   [<output_format_options>] [-multiSite[:ON|OFF]]
                   [-q[:ON|OFF]] [-e:<max_errors>] [-iw[:ON|OFF]]
                   [-stats[:ON|OFF]] [-queryInfo]
 -i:<input_format>   :  one of IISW3C, NCSA, IIS, IISODBC, BIN, IISMSID,
                        HTTPERR, URLSCAN, CSV, TSV, W3C, XML, EVT, ETW,
                        NETMON, REG, ADS, TEXTLINE, TEXTWORD, FS, COM (if
                        omitted, will guess from the FROM clause)
 -o:<output_format>  :  one of CSV, TSV, XML, DATAGRID, CHART, SYSLOG,
                        NEUROVIEW, NAT, W3C, IIS, SQL, TPL, NULL (if omitted,
                        will guess from the INTO clause)
 -q[:ON|OFF]         :  quiet mode; default is OFF
 -e:<max_errors>     :  max # of parse errors before aborting; default is -1
                        (ignore all)
 -iw[:ON|OFF]        :  ignore warnings; default is OFF
 -stats[:ON|OFF]     :  display statistics after executing query; default is
                        ON
 -c                  :  use built-in conversion query
 -multiSite[:ON|OFF] :  send BIN conversion output to multiple files
                        depending on the SiteID value; default is OFF
 -saveDefaults       :  save specified options as default values
 -restoreDefaults    :  restore factory defaults
 -queryInfo          :  display query processing information (does not
                        execute the query)
Examples:
 LogParser "SELECT date, REVERSEDNS(c-ip) AS Client, COUNT(*) FROM file.log
            WHERE sc-status<>200 GROUP BY date, Client" -e:10
 LogParser file:myQuery.sql?myInput=C:\temp\ex*.log+myOutput=results.csv
 LogParser -c -i:BIN -o:W3C file1.log file2.log "ComputerName IS NOT NULL"

Help:
 -h GRAMMAR                  : SQL Language Grammar
 -h FUNCTIONS [ <function> ] : Functions Syntax
 -h EXAMPLES                 : Example queries and commands
 -h -i:<input_format>        : Help on <input_format>
 -h -o:<output_format>       : Help on <output_format>
 -h -c                       : Conversion help

Lets begin

I use this template and just change de SQL query’s.

Run in command prompt just in one line,

LogParser.exe -i:W3C "One of the SQl sentences in examples below" -o:CSV

Examples Of SQL Query’s for multiple situations.

The hard is to get some valid SQL query’s that cover multiple situations, below you can find some of them that I think interesting to be used daily.

SQL query’s are in different lines to get reading easier , and also some information was cleared from the output because is irrelevant to this post and to extensive, but you can get a picture of the final result. I’ve also copied the logs from my server, to my laptop.

Number of Hits per Client IP, including a Reverse DNS lookup

SELECT c-ip As Machine, REVERSEDNS(c-ip) As Name, COUNT(*) As Hits 
FROM H:\LOGSIIS\W3SVC1\U*.* GROUP BY Machine ORDER BY Hits DESC

Out Put

Machine,Name,Hits
 10.xxx.xxx.x1,10.xxx.xxx.x1,505923
 10.xxx.xxx.x2,10.xxx.xxx.x2,443922

Statistics:
 -----------
 Elements processed: 1114134
 Elements output:    53
 Execution time:     156.64 seconds (00:02:36.64)

Top 25 File Types

SELECT TOP 25 EXTRACT_EXTENSION(cs-uri-stem) As Extension, COUNT(*)
As Hits FROM H:\LOGSIIS\W3SVC1\* GROUP BY Extension ORDER BY Hits DESC

Out Put

Extension,Hits
 gif,70954
 aspx,56951
 css,14300
 js,9342
 vbs,3994
 swf,6
 png,5
 pdf,1

Statistics:
 -----------
 Elements processed: 1114134
 Elements output:    24
 Execution time:     2.39 seconds

Top 25 URLs Hits

SELECT TOP 25 cs-uri-stem as Url, COUNT(*) As Hits FROM H:\LOGSIIS\W3SVC1\*
GROUP BY cs-uri-stem ORDER By Hits DESC

Out Put

Url,Hits
 /,949949
 /Report/lgx_Engine/lgx_LoadPicture.aspx,5893
 /Connect/lgx_Engine/lgx_LoadPicture.aspx,4263
 /Report/lgx_Engine/lgx_Utils/lgx_Logon/lgx_Revive.aspx,3671
 /lgx_images/months/w2d30.gif,3237
 /lgx_images/months/w4d31.gif,3236
 /lgx_images/months/w1d31.gif,3207
 /lgx_images/months/w6d31.gif,3207
 /lgx_images/months/w0d31.gif,3200
 /lgx_images/months/w3d29.gif,3141
 /Report/rdPage.aspx,3141
 /lgx_images/months/w4d30.gif,3129
Statistics:
 -----------
 Elements processed: 1114134
 Elements output:    25
 Execution time:     2.07 seconds

Number of hits per Method (GET, POST, etc)

SELECT cs-method As Method, COUNT(*) As Hits FROM H:\LOGSIIS\W3SVC1\* GROUP BY Method

Output

Method,Hits
 GET,1088636
 POST,24746
 OPTIONS,593
 HEAD,147
 PROPFIND,12
Statistics:
 -----------
 Elements processed: 1114134
 Elements output:    5
 Execution time:     2.46 seconds

Number of requests made by user

SELECT TOP 25 cs-username As User, COUNT(*) as Hits FROM H:\LOGSIIS\W3SVC1\*
WHERE User Is Not Null GROUP BY User

Output

UP BY User" -o:CSV
 User,Hits
 MyDomain\username1,237
 MyDomain\username14,3
 MyDomain\username11,25
Statistics:
 -----------
 Elements processed: 1114134
 Elements output:    25
 Execution time:     2.28 seconds

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 H:\LOGSIIS\W3SVC1\*
WHERE Query_D IS NOT NULL 
GROUP BY Query_D, Query_T ORDER By Hits DESC

Output

 URL,Max,Min,Average
 /Control/,13782,13782,13782
 /Translate/lgx_Engine/lgx_Frame.aspx,327972,0,13066
 /Translate/lgx_Engine/lgx_Detail.aspx,191354,15,11080
 /Translate/_Scripts/Transaction/TrackingFrameSet.aspx,10125,10125,10125
 /Trade/lgx_Engine/lgx_SubmitPage.aspx,17594,0,7757
 /Translate/lgx_Engine/lgx_Gateway.aspx,84567,15,7679
 /Connect/lgx_Engine/lgx_Menu.aspx,42471,0,3309
 /Home/_Temp/14425.gif,9328,15,3176
 /Trade/_Scripts/ReferencePriceListForm.aspx,5281,890,3085
Statistics:
 -----------
 Elements processed: 1114134
 Elements output:    25
 Execution time:     2.62 seconds

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 H:\LOGSIIS\W3SVC1\* 
GROUP BY Status ORDER BY Status ASC

Output

Status,Hits
 200.0,773106
 301.0,110
 302.0,1926
 304.0,43803
 401.1,21296
 401.2,245000
 403.14,77
 404.0,28778
 404.15,1
 500.0,17
 500.22,6
 500.24,14
Statistics:
 -----------
 Elements processed: 1114134
 Elements output:    12
 Execution time:     4.00 seconds

List the count of each Status code

SELECT sc-status As Status, COUNT(*) As Number FROM H:\LOGSIIS\W3SVC1\*
GROUP BY Status ORDER BY Status

Output

Status,Number
 200,773106
 301,110
 302,1926
 304,43803
 401,266296
 403,77
 404,28779
 500,37
Statistics:
 -----------
 Elements processed: 1114134
 Elements output:    8
 Execution time:     2.04 seconds

List all the requests by user agent

SELECT cs(User-Agent) As UserAgent, COUNT(*) as Hits FROM H:\LOGSIIS\W3SVC1\*
GROUP BY UserAgent ORDER BY Hits DESC" -o:CSV

Output

UserAgent,Hits
 ,949846
 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727;
 +.NET+CLR+3.0.4506.2152;+.NET+CLR+3.5.30729;+InfoPath.2),32945
 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727;
 +.NET+CLR+3.0.04506.30;+.NET+CLR+3.0.04506.648;+.NET+CLR+3.5.21022
 ;+.NET+CLR+3.0.4506.2152;+.NET+CLR+3.5.30729;+InfoPath.2;+.NET4.0C),24775
 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+chromeframe/11.0.696.68;+.NET+CLR+1.1.4322;
 +.NET+CLR+2.0.50727;+.NET+CLR+3.0.04506.30;+.NET+CLR+3.0.0450
 6.648;+.NET+CLR+3.5.21022;+.NET+CLR+3.0.4506.2152;+.NET+CLR+3.5.30729;+InfoPath.2),22805
 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+Trident/4.0;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727;
 +.NET+CLR+3.0.04506.30;+.NET+CLR+3.0.04506.648;+.NET+CLR+3
 .5.21022;+.NET+CLR+3.0.4506.2152;+.NET+CLR+3.5.30729;+InfoPath.2;+.NET4.0C),21243
 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727;
 +.NET+CLR+3.0.04506.30;+.NET+CLR+3.0.04506.648;+.NET+CLR+3.5.21022
 ;+.NET+CLR+3.0.4506.2152;+.NET+CLR+3.5.30729;+InfoPath.2),16795
 Mozilla/4.0+(compatible;+MSIE+8.0;+Windows+NT+6.1;+WOW64;+Trident/4.0;+SLCC2;+.NET+CLR+2.0.50727;
 +.NET4.0C;+.NET4.0E;+.NET+CLR+3.5.30729;+.NET+CLR+3.0.30729),79
 66
 Statistics:
 -----------
 Elements processed: 1114134
 Elements output:    54
 Execution time:     2.02 seconds

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 H:\LOGSIIS\W3SVC1\* 
WHERE Win32-Status<>0 GROUP BY Win32-Status 
ORDER BY Win32-Status ASC

Output

Win32-Status,Description,Hits
 2,The system cannot find the file specified.,26914
 5,Access is denied.,244993
 22,The device does not recognize the command.,11
 50,The request is not supported.,20
 64,The specified network name is no longer available.,2771
 121,The semaphore timeout period has expired.,1
 995,The I/O operation has been aborted because of either a thread exit or an application request.,6
 2148074252,The logon attempt failed,22
 2148074254,No credentials are available in the security package,21251
 3221225581,ErrorCode=-1073741715 (0xc000006d) - Unknown error message,2
 3221225585,ErrorCode=-1073741711 (0xc0000071) - Unknown error message,2
Statistics:
 -----------
 Elements processed: 1114134
 Elements output:    11
 Execution time:     2.11 seconds

Hope that this information can be useful.

Advertisements

About rodvars
Been working in IT Services/Consulting for the past 15 years. My main areas of work are planning, development, managing and administration System infrastructures focusing on optimizing user processes, enforcing business security, performance enhancements, high availabilty and infrastucture scalability.

11 Responses to LOG Parser – IIS LOGS Analysis

  1. Pingback: LOG Parser – IIS Charts « Another MSFT Knowledge Base

  2. Pingback: Log parsing Working Event Viwer data « Another MSFT Knowledge Base

  3. Thanks for the great blog post! I certainly loved reading through it, you are an awesome writer. I am going to be sure to take note of your web site and will eventually come back down the road.

  4. I could not agree more! Fantastic post, have a very wonderful day, L8rs.

  5. I was searching for Logfiles w3svc1 ex log and found your blog. Really nice article though. Please keep posting about these things. Thanks, Patricia.

  6. Many thanks, I’ve been browsing for details in regards to this subject for a long time and this particular site is the best I have identified so far.

  7. Tom says:

    2012-10-22 04:53:35 W3SVC1 203.116.93.86 GET /CIPS/bin/admin/login – 443 – 176.175.25.31 Mozilla/4.0+(compatible;+MSIE+8.0;+Windows+NT+5.1;+Trident/4.0;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727;+.NET+CLR+3.0.4506.2152;+.NET+CLR+3.5.30729) 404 0 3

    use jrun , iis 6, win2k3, added mimetype , still cannot display page…..please advise…

    • rodvars says:

      the error 404.0 means “not found”,

      • Tom says:

        what should i do then please?
        how to i ensure it connected to jrun server from iis 6?
        TQ

      • rodvars says:

        use my e-mail rodvars@gmail.com for questions!

  8. Pingback: Fix Log Parser Ignore Errors Windows XP, Vista, 7, 8 [Solved]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: