13

I have IIS log with extra field 'foo'. 

#Fields: foo date s-sitename ...
foo1 2009-02-15 W3SVC1 ...
foo2 2009-02-15 W3SVC1 ...

As result all LogParser queries are broken:

logparser -i:IISW3C  "SELECT c-ip, s-ip FROM my.log"

Statistics:
-----------
Elements processed: 0
Elements output:    0
Execution time:     0.00 seconds

Is it possible to inform LogParser about such extra fields, so it can parse IIS files?

alex2k8
  • 42,496
  • 57
  • 170
  • 221

5 Answers5

27

Try W3C format (-i:W3C).

If that doesn't work and this is a one-time analysis, you could create a script to strip out that column. If this is an ongoing activity, you might want to consider using a standard format, or at least moving the extra field to the end.

By the way LogParser does support custom input formats.

jdigital
  • 11,926
  • 4
  • 34
  • 51
  • Log Parser W3C format works with "X-Forwarded-For" header for testing load balancer "stickiness" – Chris Mills May 28 '15 at 10:40
  • Also make sure the custom field exists in all log files you are querying. Otherwise you will still get this same error. Even if only a few of the files don't have it. – Zar Shardan Feb 22 '19 at 10:11
  • The W3C format doesn't support checkpointing for incremental log processing, which can be a dealbreaker. – brianary Jun 22 '21 at 18:10
1

use option -iHeaderFile to define your own fields. logparser -h will prove additional inforamtion

Bob
  • 11
  • 1
  • From what I found in the documentation iHeaderFile is only available for TSV and CSV input formats. – bkqc Sep 24 '20 at 14:28
  • 1
    @bkqc : You can use the TSV parser with IIS Logs files too, even if fields are separated by spaces and not tabs. Simply define the **iSeparator** and **nSep** properties too. As my IIS Log files contains 14 fields, I use the following options myself to parse them : ***-iSeparator:space -fixedSep:OFF -headerRow:OFF -iHeaderFile:MyCustomPath -nFields:14 -lineFilter:-# -iTsFormat:yyyy-MM-dd hh:mm:ss*** – AlexLaforge Sep 25 '20 at 17:27
0

It's very easy to do !

Simply use the TRIM function around your string. This way, you can type any string you want as a custom extra field in a Log Parser Query.

Reference : http://logparserplus.com/Functions#function_TRIM

For example, I do it in this query (used to retrieve the Average and Max time) :

logparser -i:IISW3C -rtp:-1 -o:NAT -headers:OFF -iw:ON "SELECT TRIM('my-website-custom-extra-column-name.com') AS siteName, TRIM('foo-bar-custom-extra-column-name') AS fooBar, AVG(time-taken) As AverageTimeTaken, MAX(time-taken) As MaxTimeTaken, COUNT(*) As Hits, TO_LOWERCASE(cs-uri-stem) As Uri FROM C:\inetpub\yourwebsite.com\ex*.log TO c:\myOutputParsedLog.txt WHERE (Extract_Extension(To_Lowercase(cs-uri-stem)) IN ('aspx')) GROUP BY TO_LOWERCASE(cs-uri-stem) ORDER BY AverageTimeTaken DESC"
AlexLaforge
  • 499
  • 3
  • 19
  • I tried using TRIM but I still get the following error. Is there anything else that needs to be set for this to work? @Parse errors: "Unknown field MyCustomField found in #Fields directive Cannot find '#Fields' directive in header of file "\\server\share\LogFiles\W3SVC37\ex200924_x.log". Lines 5 to 22178 have been ignored" – bkqc Sep 24 '20 at 14:48
  • @bkqc My apologizes: My original answer is not relevant to this post It only permits to add an arbitrary column to the results ! Regarding your question, if you need to parse a source log files with already existing extra columns, your best best is to get rid of the **-i:IISW3C** and use the **-i:TSV** instead. You then have to specify the **iHeaderFile** and **nFields** properties so that the parsing engine knows which columns are expected. This suits your needs as you can specify any column name in the Header file :-) Look for these keywords in the Documentation Help shipped with Log Parser. – AlexLaforge Sep 25 '20 at 17:24
0

This is applicable to HTTErrLog files: Create a header file using the columns of your log file in the same order and save it as - header.txt

Use the following format for your SQL query

LogParser.exe "SELECT sc-status, s-reason, s-queuename, count(*) from C:\temp\HTTPLogs\*.log group by sc-status, s-reason, s-queuename order by count(*) desc" -i:TSV -iseparator:space -iHeaderFile C:\temp\HTTPLogs\header.txt
rjose
  • 557
  • 5
  • 13
-1

Don't know about LogParser but if you're not successful with that you could try splunk which seems to handle different log formats easily enough.

Robin
  • 2,616
  • 22
  • 30