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:

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

    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 (14)

  1. Anonymous says:

    Hey Kary, any updates on DQuotes version being available for download?

    1. Kary Wall says:

      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.

  2. Nathan says:

    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,


  3. Kary Wall says:

    Hi Nathan,

    Actually DQuotes has been properly enabled and I'll be updating the download soon. 🙂

  4. Sushovan says:

    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?

  5. Sushovan says:

    Never mind: I figured it out. The TO_INT( ) function is what I was looking for.

  6. Kary Wall says:

    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.

  7. Kary says:

    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 🙂

  8. evan says:

    I know I'm basically repeating others' questions, but  any updates on the DQuotes version?

  9. Kary Wall says:

    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:


    Change list here:


  10. Kary Wall says:

    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.

  11. miquel says:

    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?

  12. Kary Wall says:

    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:


  13. Martin says:

    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"

Skip to main content