Log Parser Studio and IIS Advanced Logging

I was approached on two occasions with questions about LPS and IIS Advanced Logging and if LPS can successfully query these logs. The use double-quotes option isn't yet available in LPS (hopefully coming soon) so here is a workaround that should suffice for now:

1. Open an existing IIS Advanced log, copy the fields header row, paste into a text file (let's call it IISADVHeader.txt) and save it somewhere. You'll need to remove the #Fields comment as well. Here is a before/after:

   Before:
   #Fields:   date time cs-uri-stem cs-uri-query s-contentpath sc-status s-computername cs(Referer) sc-win32-status sc-bytes cs-bytes

   After:
   date time cs-uri-stem cs-uri-query s-contentpath sc-status s-computername cs(Referer) sc-win32-status sc-bytes cs-bytes

2. Point LPS to the IIS Advanced log(s) you wish to query.

3. Create a new query. Keep the default LPS new query for your initial testing. You can modify the query to your needs after we know we are configured correctly.

4. Choose TSV as the log type.

5. Open the TSV log settings by clicking the properties button (The gray "gear" icon to the right of the log type drop down in LPS).

6. Change iSeparator from "tab" to "space", add the path to the header file you just created for iHeaderFile and change nSkipLines to 4 then click OK:

   

7. Run the query, that's it. If you (or I) didn't miss anything you should be up and running with LPS and Advanced IIS Logging:

Remember that this is a workaround. As soon as I can get the IISW3C double-quotes option tested again, I'll enable it and this workaround will no longer be required.

If you don't want those double-quotes in the final output you can remove them using the REPLACE_STR() function. Here is an example using the cs(Referer) IIS field which in the advanced logs is surrounded by quotes:

SELECT REPLACE_STR(cs(Referer), '"', '') AS Referrer FROM '[LOGFILEPATH]'

You would obviously want to include more fields in the above query. It's just a quick example of how to remove those pesky double-quotes if you need to. Notice I created an alias called Referrer for the field name. Otherwise the field name will be pretty ugly when the query executes:

The field name will appear like this without using an alias:

But like this with an alias. I think this looks much better:

Comments

• Anonymous
  January 01, 2003
  Hey Kary, any updates on DQuotes version being available for download?

  • Anonymous
    August 18, 2016
    Hey guys, not yet. I'll try to revisit soon - there is a somewhat annoying issue with how LP 2.2 is interpreting the dQuotes option.

• Anonymous
  July 15, 2013
  I don't guess there is any chance you've got something working for "-dQuotes ON" because that would just about round out all of my problems! cs(User-Agent) isn't very useful without it, Thanks!

• Anonymous
  July 16, 2013
  Hi Nathan, Actually DQuotes has been properly enabled and I'll be updating the download soon. :)

• Anonymous
  September 09, 2013
  I was using the TSV option to load up my logs, but it seems that it is treating the numeric fields as strings, and it won't let me do SUM or AVERAGE operations on them. Is there a way to coerce them into numbers?

• Anonymous
  September 09, 2013
  Never mind: I figured it out. The TO_INT( ) function is what I was looking for.

• Anonymous
  September 09, 2013
  Hi Sushovan, There is a setting in preferences to "make columns sortable" which will fix the sorting issue but as you described TO_INT() is always a good function to use anyway. I typically keep "make columns sortable" disabled by default because in some cases if a field contains two types of data (which does happen) it'll throw an error. Otherwise, you can leave it enabled. Keep in mind that in the LPS grid, data types are not directly connected to what the underlying LP 2.2 engine thinks. I basically have to make an evaluation of the first few records and assume what the data type it is most likely to be, hence the option to turn it on/off at the LPS level.

• Anonymous
  October 11, 2013
  Hi elephantoutlook, It is ready, I'm trying to get it updated on the site as I have been a little busy as of late. Nevertheless, it is ready :)

• Anonymous
  December 10, 2013
  I know I'm basically repeating others' questions, but  any updates on the DQuotes version?

• Anonymous
  December 11, 2013
  Hello All, I just updated with the latest bits that includes IIS Advanced Logging support. UseDoubleQuotes under the properties for the IISW3CLOG format in LPS is now enabled. Please note that using this setting may cause unexpected results for non-IIS Advanced logs. You can download the latest here: gallery.technet.microsoft.com/Log-Parser-Studio-cd458765 Change list here: blogs.technet.com/.../log-parser-studio-v2-updated-12-11-2013.aspx

• Anonymous
  February 17, 2015
  After several reports of IIS Advanced logging, I've confirmed it does not work correctly. Please see the workaround blog post in the mean time.

• Anonymous
  May 07, 2015
  Hi! Thanks for that post I have a problem using TSV file to define columns in IIS log. the fields are trated as text instead their native data types. Any ideas?

• Anonymous
  October 30, 2015
  Hi Miguel, It depends, there are some built in functions that may help convert some of these.
  
  1. Make sure "Make columns sortable" is checked in preferences which causes the LPS result grid to attempt to discover the underlying data type.
  
  2. Consider built in functions - reference can be found via SHIFT+F3 or see this new post that contains all keyboard shortcuts:
  
  http://blogs.technet.com/b/karywa/archive/2015/10/30/log-parser-studio-keyboard-shortcuts.aspx

• Anonymous
  December 09, 2015
  If there is a space in a value it doesn't show the value after the space. Any workaround for this?
  
  for example if a uri stem value is "this is url" the result just show "this"