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:

If you saw the post about Exchange log file formats known from within LPS as "EEL" and "EELX" you may remember that they all follow the same basic format albeit with a few exceptions. Exchange 2003 message tracking logs is one of those exceptions. Exchange 2003 message tracking logs are in a TSV type format so the TSV input log type works but it needs a slight modification of the default settings. Here's how to set up LPS to query these logs:

Point LPS to the Exchange 2003 message tracking logs in the log file manager window.

1. Create a new query. File > New Query or click the new query icon (far left in the toolbar).
2. Next you'll need to change the log file type to TSVLOG:
   
   
   
3. Once TSVLOG has been selected click the gear icon to bring up the properties for TSV:
   
   
   
   
   
4. Once the properties window appears, the only change that is required is to set nSkipLines to a value of 2 and click OK:
   
   
   
   
   
5. Press F5 to run the query.

Remember that you can revert to the default TSV format at any time by opening the properties window and clicking reset. The properties window will close and the defaults are now in effect. To bring this one to a close, I'll leave you with a starter query for Exchange 2003 Message Tracking. This query counts the number of messages per sender, recipient, message subject, date and client IP address for the top 20,000 messages in order of occurence:

/* Exchange 2003 Message Tracking - Count Messages per Sender/Recipient/Date/IP/Subject */

SELECT TOP 20000 [# Date] AS Date, client-ip, Count(*) AS Total, Sender-Address, Recipient-Address, Message-Subject
FROM '[LOGFILEPATH]'
GROUP BY Date, client-ip, Sender-Address, Recipient-Address, Message-Subject

You can copy and paste the above into LPS or you can import/merge the XML file below into the LPS library. To import, from LPS choose File > Import > XML to Library. Make absolutely sure you choose "Merge now" instead of "Replace now" then search for "Exchange 2003:" in the library without the quotes.

You can also import it directly into the new query you setup above (bypassing the library) by right-clicking inside the query window and choosing Import. Change the file type to .XML, load the file and if you have already created a query and made the settings above, it is ready to run. Just be sure to save it (File > Save or CTRL+S) if you decide you want it as part of the current library.
 

I mentioned in several posts that there were lots of improvements and hidden nuggets in Log Parser Studio that haven't been documented yet. Well, let's start listing them. Many of these are either concepts or simple one-off shortcuts that may not fit a formal structure all that well so here goes:

The Purple Timer

I wonder how many people notice this. When LPS is executing one or more queries, at some point during thier execution, the timer that displays the query's elapsed time may suspiciously turn from black to purple text.

When LPS executes a query, it formats it into a Log Parser 2.2 friendly format, creates new thread and hands this off to the Log Parser 2.2 engine. LP 2.2 begins processing the logs and doesn't report back until it has completed most of its work. If the query results are going to be sent to the result grid in LPS, it has to populate a dataset in the background that gets passed back to the LPS GUI which in turn fills the grid with that data AKA the results.

So the short story is that when the timer text turns purple, the results have been returned but are being processed on their way back to the grid. If the text is black, the underlying LP 2.2 engine is still churning away and LPS hasn't received anything back yet. But wait, there's more...You can use this indication as to how efficient your query is, and whether it would be better served as an export to CSV (using the INTO statement) and bypassing the grid altogether, or allowing them to be sent back to the LPS grid. Here are a couple rules of thumb:

• If the text stays purple most of the time and queries seem to run longer than they should, send the output to CSV instead. Bypassing the grid and going straight to CSV is always faster.
• If it is black most of the time and purple much less, it probably means its OK for the grid. It could also possibly point to a query that is causing excessive recursion in the underlying LP engine. IE: The query is causing LP 2.2 to iterate the same fields multiple times per pass and one or more of those fields also contain lots of text to iterate through.

To elaborate on this iteration, consider the IIS field cs-uri-query on an Exchange 2010 CAS server. Exchange uses this field extensively to log performance information about the request resulting in very long strings in this field:

 ae=Item&a=Preview&t=IPM.Conversation&id=CID.rVJdahzZXGamqABCVOAJQYQ%3d%3d.LgAAAAAlZwvuGcy3Q4dSE2TQ7naBAQAMifUxL48tRKNTKMcW4SQmAAAAtzWGAAAB.3wQFFFC3NYIAAAAABPgGAADDAAAA%3d&pf=1&UA=0&canary=536bc4b7ca264eb2501f7bb958d55aca&SP=1&Initial+Budget>>Conn:1,HangingConn:0,AD:60000/60000/0%,CAS:90000/89645/1%,AB:60000/60000/0%,RPC:90000/89775/1%,FC:1000/0,Policy:DefaultThrottlingPolicy_57bas234-6a17-4d84-8873-4cde234b8a5f,Norm&v=14.1.287.0&mbx=MBX1CAS-37.EX123.contoso.internal&sessionId=536bc4b7ca264eb2801f7bb958d55aca&prfltncy=16&prfrpccnt=3&prfrpcltncy=16&prfldpcnt=0&prfldpltncy=0&prfavlcnt=0&prfavlltncy=0&End+Budget>>Conn:1,HangingConn:0,AD:60000/60000/0%,CAS:90000/89630/1%,AB:60000/60000/0%,RPC:90000/89760/1%,FC:1000/0,Policy:DefaultThrottlingPolicy_57bdd422-6a17-4d84-8873-dcde358b8a5f,Norm

Now imagine a query searching for any occurrence of MBX1, MBX2, or MBX3: SELECT * FROM '[LOGFILEPATH]' WHERE cs-uri-query LIKE '%MBX1%' OR cs-uri-query LIKE '%MBX2%' OR cs-uri-query LIKE '%MBX3%'

Due to the existence of three LIKE statements all with wildcards (%) on both sides of the search term, that single field is going to take some extra time to parse. Imagine a few gigabytes of IIS logs and you'll understand how perfectly good query can result in churn. The above could probably be modified to be more efficient or you may rethink how you want to retrieve the data. Just remember, purple text = results are now being returned to LPS and black text = the underlying LP 2.2 engine is still searching the actual files on disk.

Hover, Expand or Pop Out  

Since all types of logs are to be queried with varying field sizes LPS sets grid column widths to an assumed size. This means you'll have queries where you can't see all of a column because the text is longer than the column width. There are actually multiple shortcuts around this.

1. Hover your mouse over the field. This will show the field in "most" of its entirety. If it is extremely long like the query string above then some of it will still be cut off.
2. Right-click > Expand/Collapse which will expand that column width to encompass the cell with the longest line of text. A second click collapses in toggle fashion.
3. Press F4 which is exactly the same as #2 but functions as a toggle with a single key.
4. Double-click the cell itself which will display the field contents in a small popup window.

If there is a large number of rows in your query result as in >10000 then consider using #4. All the other options expand the entire column which means every cell has to be accessed, measured, resized etc. which is going to take some time and possibly cause the GUI to hang until it is finished.

Quick Import

I keep most queries I use in the library but I spend just as much time writing one-off queries for one-off situations; who knows what that may be but it usually results in my exporting that single query as either XML or SQL into folder that is common to the issue I am working with. At some later time I need to revisit the project so it's nice to have an easily accessible set of the exact queries I used previously. I don't really want to import them into the library, I just need to run them a couple times for that project etc. 

Right-Click anywhere inside the query window, choose Import and browse to the SQL or XML query and voila it opens in the query window. It isn't in the library unless you click save otherwise it just falls away when you close the query window. I didn't need to import and mix it in with my main library queries, I just used them quickly and discarded.

Search the Results

The library search feature in LPS now extends into the queries themselves and is context sensitive. Let's say you have five query tabs, each with results. You notice the queries were really close to what you wanted but you would like to narrow those results a little further. The search feature now does this to the query tab that has focus. Just type what you are searching for and the results will be narrowed to any fields containing that search term. You can then narrow further by searching again as new searches only search the latest result.

The text turns a bluish color to signify the results are currently narrowed due to search. To return to the original full result set, just click the X on by the search text box. This works across all query tabs independently.

Search the Library

If you haven't noticed the categories feature has been deprecated. It is still available if needed in preferences by enabling legacy categories. However, it was deprecated because it didn't get used much (only from my visibility), took up real estate and the search function is so much better. That being said, all queries are typically prefixed by a categorical identifier of some sort. So if you want to only see OWA queries just type OWA: and click the search button. Or for only IIS specific queries search for IIS:. We get the exact same results. The main thing I wanted to mention is that with the query library growing, continuing this format and remembering the search library feature will get you to your queries quicker and easier.

Copy Cells + Headers or Not

If you don't already know all query results can by copied/pasted as needed. You can select cells in any manner you wish and only the selected cells get copied to the clipboard and the header/field names are included (CTRL+C or right-click > copy). This is great for pasting into Excel or an email etc. Sometimes however, we might want just the contents of a cell such as when we need to use that cell value in a new query. To copy the cell contents with no headers use SHIFT+CTRL+C or right-click + SHIFT > copy.

Drag -n- Drop Columns

If you have by chance ran a query, clicked the chart button but nothing happened you may not be aware the requirements for creating a chart. In order to draw the bars/lines on the chart it needs a number. That is always supplied by the contents of the second column in any result grid. For example if you were counting HTTP 500 errors per hour you'd want the time/hour in column one and the number of errors in column two. Knowing this you may also notice that there are lots of queries in the library that would/could make a great chart but the number value column is not in the column two position. What to do?

Just drag that column over into the column two position and click the chart button (or F6). If you have multiple columns you care about, you can drag any one you wish over and generate a chart from it so remember any query with at least two columns and one of those is a number can be charted, you just need to drag the numbered column into position two.

Popular Keyboard Shortcuts

There are 23 or so keyboard shortcuts but there are a handful that I think get the most use when writing queries. I use these constantly and I think they need to be shared:

F7 - Inserts the following: '[LOGFILEPATH]'
The above is a bit tricky quickly type and not miss anything. F7 inserts it for you.

F8 - Inserts the following: '[OUTFILEPATH]\Output.CSV'
Output.CSV is auto-selected, just type the filename you wish to use. This is when you want to run a query to CSV, you can type INTO and press F8 and you are good to go.

F9 - Inserts the following: LIKE '%%'
One of the most used SQL keywords in LPS. Another one that at least for me can be aggravating to type all the time. Pressing F9 and then typing the search term is much faster

SHIFT+F10 - Inserts: EXTRACT_PREFIX(EXTRACT_SUFFIX(FieldName, 0, 'StartChar'), 0, 'EndChar') as FieldAlias
If you remember the cs-uri-querystring example I posted above, there are lots of individual indicators tucked into that field. We can extract exactly the piece we want as its own field using the above statement. I happen to use it extensively in the library and you can probably see how much fun it would be to type it or find in another query and copy paste. So, there is now a shortcut key for it that I hope is self-explanatory. You'll need to replace the placeholder names with the real names AKA FieldName, StartChar, EndChar, FieldAlias:

• FieldName - The field you are extracting from such as cs-uri-query
• StartChar - The actual character(s) that is the last char before the one you want.
• EndChar - The actual character(s) that is just after the last one you want.
• FieldAlias - What you want the column created to be named.

For example if the cs-uri-query contained &user=user1&id=123456;someotherinfo and you wanted to extract the ID from that the following would accomplish this. Notice that 'id=' and the semicolon are our start and end chars and that the result would be 123456:

EXTRACT_PREFIX(EXTRACT_SUFFIX(cs-uri-query, 0, 'id='), 0, ';') as IDField

F5 - Runs the query that is focused.

F3 - What was the name of that IIS field?
IIS logs are by far one of the most queried logs files. You may have noticed that the Log Type drop-down in the query window is blue text while all the others are black. That's why, there is such a chance that someone may be querying an IIS log, we wanted to make it easy to find. Additionally, those darn field names can be hard to remember. Just hit F3 and a window will pop up with the fieldnames for quick reference:

If you are just looking for a single field name, double-click it and it will be copied to the clipboard and the window will automatically close. If you want to select multiple fields, use CTRL+Click to select the one's you want, now press enter and each of those will be copied to your clipboard and comma delimited ready to paste directly into the query window. Note that fields denoted with an asterisk are not enabled by default in IIS so it's important to remember that if you haven't previously enabled these in your IIS environment they will not appear in the logs.

Auto-Select Tabs

In preferences there is an option to "Auto-Select tab on query completion". If you are working in tab 1 for example and you have a long running query in tab 10, LPS will automatically send you to that tab when the query completes. If disabled the query completes without taking you away from the tab you are currently working in.

Query Logging

For the heavy user, lots of queries are being written vs. just running queries from the library. I start working on query that does "x", spend some time on it, get distracted, close LPS thinking there was nothing I really wanted to save and bam, there was that one line in the query that I struggled over, has value and now is gone. It only took a few times of getting it too big a hurry and losing such moments of genius that a query logger was added to LPS. That's right, if the option is enabled, every single time you press the execute query button, the query gets appended to query.log along with its name, when it was executed and its log type.

So theoretically, you could realize you don't have a great query you thought you had, you know you ran it but you can't find it. It'll be waiting for you in the query log. :) Just remember to enable it in Options > Preferences > Log all queries to file.

The file is always written to the LPS output directory which you can set to whatever you wish in Options > Preferences > Default Output Path. It's the same directory that CSV output goes to by default. To quickly access the query log, click the OUT button just to the left of the PowerShell button on the main menu bar in LPS. and open query.log where the newest queries are at the bottom. 

Renaming Tabs

You have twenty query tabs open, you are reviewing the results, you see something interesting, switch to another tab and forget which of the other 19 you were on. Right-click the tab and give it a name that makes sense to you. You can rename any query tab to anything you wish for quick access.

Hide the Query Window

The little "SQL" icon on the left of the query window just above the query, below the results grid and to the left of the orange lock shows/hides the query window. Once the query has executed and you need more grid real estate, click this button to hide the query.

LP 2.2 Function List

We found ourselves constantly browsing to and opening the LP 2.2 help file when referencing all the built in functions in LP 2.2. If you are an LP 2.2 query pro you probably use these functions quite a bit. Click SHIFT+F3 which will launch a copy of the LP 2.2 function list in the default browser.

File Manager Tips

The file manager is where we choose all the log files we need to query. You can add all the files you want including different log file types, then control which files are queried based on whether their respective checkboxes are checked. This is very handy and keeps file browsing at a minimum and you can keep paths you use often without having to remove and add back them later, just disable/enable them.

You may have a list of files that you return to but for whatever reason you want those exclusively available as a "set" of files. File > Save from within the File Manger allows you to save all files listed as a .FLD file that you can load at anytime. This could be lots of files with lots of paths all conveniently saved in one file. Automation also uses this type of file.

Right-click any file in the list to copy its full path to the clipboard.

In the bottom right of the main LPS GUI you'll see the log file that is being queried. If there is more than one file being queried it will display as "Multiple Files". In either case, clicking the text opens the file manager for you.

Library Tips

All queries now include a date modified field on the far right. It's easy to tell when a query was last saved and you can sort by date modified.

Selecting a query then CTRL+C will copy the query name, description and the query itself to the Windows clipboard.

Pressing F2 from the library view will open the selected query in quick-edit mode allowing minor changes. This is so you can change query meta-data easily when you don't intend on modifying or executing the query. For example you saved it with the wrong log type or name and that's all you need to change. F2 makes it happen.

File > Reload library will reload the last saved library. If you choose File > Save Library any saved queries are permanently saved to the library.

You can export one, few, many queries in groups as XML files. You may have ten queries that you want to share or save with a project or issue you are working on so that all files involved with the project are in one location. File > Export > Queries as XML is the choice. Use CTRL+Click to select non-adjacent queries; only the selected queries will be written to the XML file which can be later imported as needed. This is most valuable if you don't always use the standard library.

For example you always export your custom queries leaving the default library as-is. This ultimately depends on the person and what works best for them. An advanced user may want to separate queries into mini-libraries that they load depending on the job at hand. The default library can always be recovered using Help > Recover Library.

Lastly, LPS 2.0 includes a somewhat configurable library location option. In other words LPS can use a common AppData path for the library or it can be stored directly in the LPS installation directory. This had advantages and implications:

• Since LPS doesn't require an install you could make several copies of the LPS folder with each having it's own separate library; running LPS.exe from LPSFolder1 would use a different library than LPS.exe in LPSFolder2 and so on.
• All instances use the exact same library because it is stored in the user's AppData directory.
• If you place the LPS directory in a protected folder such as Program Files  you must use AppData as the library location or UAC will prevent saving the library and you'll get an access denied error when saving queries.

To modify this setting see Options > Preferences > Store library in AppData folder for UAC compatibility. The default is disabled or unchecked.

CSV Tip

If you are executing queries where the output is directed to a CSV file instead and you don't have auto-open CSV enabled, the CSV is created but doesn't automatically open. However, for any query that goes to CSV or TSV etc. the file name of the file created appears in the result grid. You can just click that to open it. If auto-open CSV is enabled the CSV will auto-open in the default CSV editor either when the query completes or if you are saving a result grid as CSV. To save results that are already in the grid as CSV, click the green export button (fourth icon from the left) on the main toolbar.

Tool Tips

Many buttons, icons and interface elements contain tool tip text. If you are wondering what a button does, hover your mouse over it for a few seconds.

That should be a good start, as you can see there is more to LPS than meets the eye at first glance. I'll try to document more tips and tricks in the future.

After quite a bit of blood, sweat and tears, LPS 2.0 was released today. A large number of improvements have been made since V1. When considering improvements many were the result of either feedback from engineers and customers or, removing workflow barriers whenever I ran into them. In other words since I use LPS everyday I inevitably hit something that makes me think "well that could be easier than it is" which led attempting to meet that need, OK that's better, rinse and repeat.

This means that many times I was running LPS in debug mode in the Visual Studio 2012 development environment. If I stumbled across something that could be a little better, I could make the change right then and there. So, there was a good bit of refinement based on day to day use which I hope will provide the same value to you.

I'll be posting many of these improvements along with their explanations in the very near future so be sure to keep checking back as there will be goodies abound. :) In the meantime, check out the EHLO article, download LPS 2.0, check out the existing articles to experience some of the new stuff: Here is the EHLO article.

http://blogs.technet.com/b/exchange/archive/2013/06/17/log-parser-studio-2-2-is-now-available.aspx

Direct link to the page with the latest bits:

http://gallery.technet.microsoft.com/Log-Parser-Studio-cd458765

The existing articles on LPS:

http://blogs.technet.com/b/karywa/

If you are installing Log Parser Studio for the first time, you don't need anything in this post. If this your first time using LPS please see this post which explains the prerequisites and how to get LPS up and running quickly.

If you already have LPS V1 installed and you have been modifying existing queries and/or creating your own queries and adding them to the library there are a few things to be aware of. The changes that may affect upgrades are the schema of the library itself and, you'll need to import any custom or modified queries that you may have added in V1.

Ideally you will want to use the new V2 library then import your modified queries from V1. So, if you have custom V1 queries you wish to keep and you want to use LPS V2 along with it new queries, consider the following steps:

1. Before running V2, open LPS V1 and export any queries you wish to use keep using File > Export > Library as XML. You can select only the queries you wish to export by using CTRL+CLICK.

2. Run LPS V2. If you see any warnings about not being able to read/find/load the library or if you are asked to attempt to convert, just cancel and allow an empty library to be created.

3. Now choose Help > Recover Library to generate the new V2 library.

4. Now we need to convert and import the queries you exported earlier and merge them into the new V2 library:
 
     » Find the custom queries XML file you exported earlier and drag-n-drop it onto convertlib.exe.
     » Convertlib will attempt to convert the exported queries to the new format.
     » If the conversion is successful, use File > Import > XML to Library, load *_converted.XML.
     » Choose Merge Now to merge these into the new library. Do not choose replace now!

5. If you don't have any custom queries you wish to keep, just run LPS V2, if the library fails to load, cancel any warnings then Help > Recover library.

The above covers most the common scenarios. If you don't have previously saved V1 queries you care about none of the above really applies other than step 5.

SUMMARY

• If you have V1 and simply want to upgrade to V2, just run V2. If there are warnings that the library can't be found/loaded then cancel the warning, Help > Recover Library.
• If you have V1 and you also have custom queries in the library that you don't want to lose, export them first from V1, convert with convertlib.exe and import back into LPS V2.
• If you don't have LPS V1 and you just downloaded LPS V2 and want to use it now you don't have to do anything.

IMPORTANT

LPS is designed to be self-contained, in other words it doesen't require an install. If you decide to place LPS in a protected location such as the Program Files directory you must check the check box for the option below in preferences. If you run LPS from a protected location you will get access denied errors when saving queries etc. If you run LPS from any other non-protected location you do not need this setting:

"Store library in AppData folder for UAC compatibility" [x]

Lastly, you can enable the option even when it isn't needed if you want all LPS V2 installs to share the same library.