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:
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.
- 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.
- 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.
- Press F4 which is exactly the same as #2 but functions as a toggle with a single key.
- 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.
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.
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.
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.
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.
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.
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.
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.