Azure Operational Insights Search How To: Part III – Manipulating Results: the pipeline “|” and Search Commands

[Edited October 27th 2014 - System Center Advisor is now a part of the new Microsoft Azure Operational Insights - Click to learn more]

This is the third installment of a Series (I don’t know yet how many posts they will be in the end, but I have at least 5 in mind at this point…) that walks thru the concepts of Microsoft Azure Operational Insights Search Syntax – while the full documentation and syntax reference is here, these posts are meant to guide your first steps with practical examples. I’ll start very simple, and build upon each example, so you can get an understanding of practical use cases for how to use the syntax to extract the insights you need from the data.

In my first post I introduced filtering, querying by keyword or by a field’s exact value match, and some Boolean operators.

In the second post I built upon the concepts of the first one, and introduced some more complex flavors of filters that are possible. Now you should know all you need to extract the data set you need.

 

In this post we’ll look at how you can manipulate and have control over those results, once retrieved, by applying commands to transform them.

 

Commands in Advisor Search MUST follow the vertical pipe sign “|”. A filter must always be the first part of a query string: it tells what data set you’ll be working on, and you “pipe” those results into a command. You can then further pipe them into another command and so on.

This is loosely similar to the Windows PowerShell pipeline.

[Start PowerShell digression]

In general, Advisor Search language tries to follow PowerShell style and guidelines to make it ‘sound’ familiar to our ITPro audience, and ease the learning curve. Anyhow, Advisor Search is not identical to PowerShell for a number of reasons – mostly the fact that this is a specialized query language, not a general-purpose scripting language. In Advisor Search all we do is: we GET data. We can’t really call methods, don’t have functions, don’t have loops of flow control…none of that. Our use case is just: pulling some data we have previously collected, and shape it to some extent so that it tells me something more useful and lets me unlock insights.

Yes, our data has ‘Types’, but we discussed in the first post how these are not real object types/classes – they are just a property on each record. There are no objects here – only data. Therefore, there are some things which we intentionally simplified from a full blown PowerShell syntax, given the more specialized use case of Search. Not having real object and types, we considered it superfluous and redundant to use the Verb-Noun command format. I.e. in Powershell you would use the Get-Process cmdlet and you’d get back actual .NET process objects you can interact with… but all our commands just work on pipeline input, which doesn’t have any type or ‘Noun’ – it’s just a ‘Result’ – and yes we ‘got it’, but there is no need to explicitly ask to ‘Get’ something – since that’s all that Search does anyway!

We thought it would be stupid to force everyone to start their queries with Get-Result, for example, then followed by the actual filter. We thought we’d just start WITH the filter, which yes – it should GET you some ‘results’, obviously, and then we can ‘pipe’ those ‘results’ into a command to transform and shape them before presenting them to us. Since all commands deal with ‘results’ coming from pipeline input, there is no need for a Noun in the command name (all commands are applicable to all results, no strong types, remember?), and all we have in our commands are VERBS. Now, when it comes to choosing verbs, here we do stick to the PowerShell guidance and we think very hard before doing anything that doesn’t follow the PowerShell VERBS. We think this will empower you to intuitively tell what the ‘Sort’ or ‘Limit’ commands would do to the results, without looking up the documentation too often.

[End PowerShell digression]

Stefan had asked, but I could not answer thoroughly in 140 characters. Hence the long version.

 

So, we were saying, after a pipe, we can use commands. And those have names of VERBS so you can tell what they do. So let’s test our theory and see if you can tell what certain commands would do. I’ll describe it below and see if it makes sense.

 

The very first command I want to introduce is SORT.

As you’d suspect, SORT allows you to define the sorting order by one (or multiple) fields. Even if you don’t use it, by default, we enforce a Time descending order (=most recent results are always on top). This means that when you run a search, say

Type=Event EventID=1234

what we really execute for you is

Type=Event EventID=1234 | Sort TimeGenerated desc

just because that is the type of experience you are used to with logs, i.e. event viewer in windows.

Anyhow, you can use Sort to change the way we return results, i.e.

Type=Event EventID=1234 | Sort TimeGenerated asc

Type=Event EventID=1234 | Sort Computer asc

Type=Event EventID=1234 | Sort Computer asc,TimeGenerated desc

and so forth.

This simple example in a nutshell gives you a feeling of how commands work: they change the shape of the results the filter got you in the first place.

 

The second, less known, command, is LIMIT. Limit is the Powershell like verb, it is also supported to use TOP, which might sound familiar to some. They are identical behind the scenes. Imagine the scenario if you only want to know if there is ANY result at all for a given event – has it ever occurred? - , but you are not interested in how many they are. Looking at the most recent one is enough. Consider the syntax

Type=Event EventID=2110 | Limit 1

Type=Event EventID=2110 | Top 1

Note that while there were 988 records with that EventID: the fields / facets / filters on the left side of the screen always show information about the results returned BY THE FILTER PORTION of the query, the part before any pipe “|” character. Anyhow the ‘Results’ pane on the right only returns the most recent 1 result, since that is how we used a command to shape and transform those results!

 

My favorite command, also lesser known, but very useful in a variety of situations, is SELECT.

SELECT behaves like Select-Object in PowerShell: it gives you filtered results that don’t have all their original properties (which again you will still see in facets) but it will ‘select’ only the properties you specify.

Example to try:

Type=Event

(now click ‘show more’ in one of the results and look at all the properties those results have)

and then Select some of those explicitly

Type=Event | Select Computer,EventID,RenderedDescription

This is particularly useful when you want to control output and only pick the pieces of data that really matter for your exploration, which typically isn’t the full record. It is also useful when records of different ‘Types’ have SOME common properties (but not ALL of their properties are common!) to produce an output that more naturally looks like a ‘table’ and will be useful / work well when exported to CSV and massaged in Excel.

If you think this is useful, you might want to vote these ideas to make it even better https://feedback.azure.com/forums/267889-azure-operational-insights/suggestions/6519220-columns-in-search and https://feedback.azure.com/forums/267889-azure-operational-insights/suggestions/6519229-allow-resize-of-columns-in-table-view-for-aggregat .

 

In the next post we’ll talk of our most powerful command: MEASURE. Stay tuned, and in the meantime: Happy searching!