Kary Wall

Cruising the streets of Microsoft Exchange, networking, debugging and more....

Log Parser Studio and IIS Advanced Logging

Log Parser Studio and IIS Advanced Logging

  • Comments 9
  • Likes

=====================================================
UPDATE:
LPS has been updated to natively allow IIS Advanced Logging. Download the latest bits here and enable "UseDoubleQuotes" and there should no longer be any need for the workaround below.

For a short change list and description see the latest log post concerning the new bits:

http://blogs.technet.com/b/karywa/archive/2013/12/11/log-parser-studio-v2-updated-12-11-2013.aspx

=====================================================
NOTE: Due to the above he following article contains outdated content. However, the steps below do still work.


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
  • 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!

  • Hi Nathan,

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

  • 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?

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

  • 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.

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

  • 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 :)

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

  • 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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment