Log Parser Studio and IIS Advanced Logging

  • Article

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: