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:
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:
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,
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.
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?
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?
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:
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.
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?