LogParser examples

From IISLog Select all clients that calls a specific page

C:\Program Files (x86)\Log Parser 2.2>LogParser "SELECT date, REVERSEDNS(c-ip) AS Client FROM C:\temp\W3SVC4\u_ex120514.log WHERE cs-uri-stem like '/storage/storagews.asmx%'"

Output

date       Client
---------- -------------------------
2012-05-14 abc064.int.biz
2012-05-14 abc064.int.biz

Notice the use of

WHERE xxx LIKE ‘yyyy%’

instead of

WHERE xxx = ‘yyyy’

The latter is case sensitive, so the LIKE version might often be preferred to get case insensitive search.

From IISLog Select all different clients that calls a specific page

C:\Program Files (x86)\Log Parser 2.2>LogParser "SELECT c-ip, REVERSEDNS(c-ip) AS Client, count(c-ip) as clientcount FROM C:\temp\W3SVC4\u_ex120514.log WHERE cs-uri-stem like '/storage/storagews.asmx%' GROUP BY c-ip ORDER BY c-ip"

Output

c-ip          Client                    clientcount
------------- ------------------------- -----------
10.47.99.94   abc064.int.biz            1975
10.47.99.22   10.47.99.22               101
10.47.99.11   10.47.99.11               121
10.47.99.95   abc065.int.biz            1907
Statistics:
-----------
Elements processed: 7729
Elements output:    4
Execution time:     9.02 seconds

Logparser Help print

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

C:\Program Files (x86)\Log Parser 2.2>logparser -h examples
Create a CSV file containing basic information from an ETW .etl binary log file:
 LogParser "SELECT * INTO Trace.csv FROM myFile.etl"

Create a chart containing the TOP 20 URL's in the "www.margiestravel.com" web site (assumed to be logging in the W3C log format):
 LogParser "SELECT TOP 20 cs-uri-stem, COUNT(*) AS Hits INTO MyChart.gif FROM <www.margiestravel.com> GROUP BY cs-uri-stem ORDER BY Hits DESC" -chartType:Column3D -groupSize:1024x768

Print the 10 largest files on the C: drive:
 LogParser "SELECT TOP 10 * FROM C:\*.* ORDER BY Size DESC" -i:FS

Create an XML report file containing logon account names and dates from the Security Event Log messages:
 LogParser "SELECT TimeGenerated AS LogonDate, EXTRACT_TOKEN(Strings, 0, '|') AS Account INTO Report.xml FROM Security WHERE EventID NOT IN (541;542;543) AND EventType = 8 AND EventCategory = 2"

Load a portion of the registry into a SQL table:
 LogParser "SELECT * INTO MyTable FROM \HKLM" -i:REG -o:SQL -server:MyServer -database:MyDatabase -driver:"SQL Server" -username:TestSQLUser -password:TestSQLPassword -createTable:ON

Parse the output of a 'netstat' command:
 netstat | LogParser "SELECT * FROM STDIN" -i:TSV -iSeparator:space -nSep:2 -fixedSep:off -nSkipLines:3

Display users' job title breakdown from Active Directory:
 LogParser "SELECT title, MUL(PROPCOUNT(*), 100.0) AS Percentage INTO DATAGRID FROM 'LDAP://myusername:mypassword@mydomain/CN=Users,DC=mydomain,DC =com' WHERE title IS NOT NULL GROUP BY title ORDER BY Percentage DESC" -objClass:user

Retrieve all the AccessFlags properties from IIS metabase objects:
 LogParser "SELECT ObjectPath, PropertyValue FROM IIS://localhost WHERE PropertyName = 'AccessFlags'"

Send error entries in the IIS log to a SYSLOG server:
 LogParser "SELECT TO_TIMESTAMP(date,time), CASE sc-status WHEN 500 THEN 'emerg' ELSE 'err' END AS MySeverity, s-computername AS MyHostname, cs-uri-stem INTO @myserver FROM <1> WHERE sc-status >= 400" -o:SYSLOG -severity:$MySeverity -hostName:$MyHostname

Create a pie chart with the total number of bytes generated by each extension:
 LogParser "SELECT EXTRACT_EXTENSION(cs-uri-stem) AS Extension, MUL(PROPSUM(sc-bytes),100.0) AS Bytes INTO Pie.gif FROM <1> GROUP BY Extension ORDER BY Bytes DESC" -chartType:PieExploded -chartTitle:"Bytes per extension" -categories:off

Get the distribution of EventID values for each Source:
 LogParser "SELECT SourceName, EventID, MUL(PROPCOUNT(*) ON (SourceName), 100.0) AS Percent FROM System GROUP BY SourceName, EventID ORDER BY SourceName, Percent DESC"

Parse a TSV file from a web URL:
 LogParser "SELECT * FROM https://myusername:mypassword@www.margiestravel.com /MyUrl" -i:TSV

Create TSV files containing Event Messages for each Source in the Application Event Log:
 LogParser "SELECT SourceName, Message INTO myFile_*.tsv FROM \\MYSERVER1\Application, \\MYSERVER2\Application"

Create a CSV file with information from a custom COM plugin:
 LogParser "SELECT * INTO Report.csv FROM MyFromEntity" -i:COM -iProgID:MyCompany.MyComPlugin -iCOMParams:TargetMachine=localhost,ExtendedFields=on

List the fields extracted from a CSV file:
 LogParser -h -i:CSV myfile.csv -headerRow:on

List the fields extracted from a TSV file:
 LogParser -h -i:TSV myfile.tsv -headerRow:on

Display total network traffic bytes per second:
 LogParser "SELECT QUANTIZE(DateTime, 1) AS Second, SUM(FrameBytes) INTO DATAGRID FROM myCapture.cap GROUP BY Second"

List from an ETW trace all the filepaths accessed by IIS:
 LogParser "SELECT FileName, COUNT(*) AS Total INTO filenames.w3c FROM iistrace.etl GROUP BY FileName ORDER BY Total DESC" -providers:"IIS: WWW Server" -fmode:full -o:W3C -encodeDelim:ON

Show all the IIS events from an ETW trace, grouped together by request:
 LogParser "SELECT EventName, EventTypeName, Timestamp, HASHSEQ(ContextId) AS Id INTO DATAGRID FROM iistrace.etl WHERE ContextId IS NOT NULL ORDER BY Id, Timestamp ASC" -providers:myfile.guid -fmode:full

Display the distribution of registry value types:
 LogParser "SELECT ValueType, COUNT(*) INTO DATAGRID FROM \HKLM GROUP BY ValueType"

Display titles of current channels on MSDN BLogs:
 LogParser "SELECT title INTO MyOutput.txt FROM http://blogs.msdn.com/MainFeed.aspx#/rss/channel/item" -i:XML -fMode:Tree -o:tpl -tpl:mytemplate.tpl

C:\Program Files (x86)\Log Parser 2.2>

Links

The End.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

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: