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
