Query IIS logs with extra fields using LogParser

Question

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?

Savannahsavant

Answer 1

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.

Sochor

Log Parser W3C format works with "X-Forwarded-For" header for testing load balancer "stickiness" – Insight

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. – Ceil

The W3C format doesn't support checkpointing for incremental log processing, which can be a dealbreaker. – Arguseyed

Answer 2

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

Mesomorphic

From what I found in the documentation iHeaderFile is only available for TSV and CSV input formats. – Dunston

@Dunston : 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 – Micmac

Answer 3

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"

Micmac

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" – Dunston

@Dunston 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. – Micmac

Answer 4

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

Apocarp

Answer 5

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.

Riddell