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:
- 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:
#Fields: 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
- Point LPS to the IIS Advanced log(s) you wish to query.
- 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.
- Choose TSV as the log type.
- 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).
- 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:
- 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: