Azure Operational Insights Search How To: Part VII – Measure Sum() and Where command

This is the seventh installment of a Series 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 the third post I introduced the use of the pipeline symbol “|” and how to shape your results with search commands.

In the fourth post I introduced our most powerful command – measure – and used it with just the simplest of the statistical functions: count() .

In the fifth post I expanded on the measure command and showed the Max() statistical function.

in the sixth post I continued with measure’s statistical functions – I showed how Avg() is useful with Performance data among other things.

 

By now you should have grasped how ‘measure’ works, therefore I will not actually spend time on the Sum() function, other than this mention – if you want to see an example of its use, refer to this blog post https://blogs.msdn.com/b/dmuscett/archive/2014/09/20/w3c-iis-logs-search-in-system-center-advisor-limited-preview.aspx where I already showed how to use it to get aggregate amount of traffic download to a given IP from a webserver (IIS logs):

Type=W3CIISLog | Measure Sum(scBytes) by cIP

Interesting/additional thing to notice before we move on: you can use Max() and Min() with numbers, datetimes and strings. with strings, they basically get sorted alphabetically and you get first and last.

You cannot however use Sum() – which does a REAL calculation – with anything but numerical fields. Same applies to Avg().

 

Where

The last command we have is Where.

Where works like a filter, but it can be applied in the pipeline to further filter ‘aggregated’ results that have been produced by a Measure command – as opposed to ‘raw’ results that get filtered at the beginning of a query.

Given this query:

Type=PerfHourly CounterName="% Processor Time" InstanceName="_Total" | Measure Avg(SampleValue) as AVGCPU by Computer

I can add another pipe “|” character and the Where command to only get computers whose average CPU is above 80%

Type=PerfHourly CounterName="% Processor Time" InstanceName="_Total" | Measure Avg(SampleValue) as AVGCPU by Computer | Where AVGCPU>80

You see what this represents?

If you are a system center person, talking in ‘management pack terms’, if this was a rule, the first part would be the ‘data source’, while the where command becomes the condition detection. (see my other dissertation on ‘rules’ and ‘searches’ similarities in this other post https://blogs.msdn.com/b/dmuscett/archive/2014/11/05/iis-mp-event-alerting-rules-s-opinsights-searches-equivalents.aspx )

Where’s the write action, you might ask?

Well, if you bind this to a tile in ‘My Dashboard’, this essentially is a ‘monitor’ that lets you see in real time if machines are under CPU pressure – soon even on your phone – as shown below, in the bottom two tiles, both as a list and as a number: you basically always want the number to be zero and the list to be empty – otherwise it indicates an alert condition, essentially, and you can peek which machines are under pressure:

 

Welcome to monitoring in the modern world!

For other style of ‘write actions’, consider this idea in our feedback forum for the actual creation of alerts and other notification (email etc) whenever those ‘where’ criteria (really if you think of them, those are ‘thresholds’) are met https://feedback.azure.com/forums/267889-azure-operational-insights/suggestions/6519198-run-saved-search-on-a-schedule-raise-alert-and-or or this other one to kick off an automation runbook in response to a query matchin results https://feedback.azure.com/forums/267889-azure-operational-insights/suggestions/6658045-add-ability-to-execute-runbook-automation-from-aoi

This concludes the planned ‘HowTo’ series on the search syntax. I hope it was useful and enjoyable. The idea was to let new users read one post a day and having learned the syntax and be productive with it in a week!

As we add more capabilities and commands I will continue it. Please remember the official syntax reference can be found here https://technet.microsoft.com/en-us/library/dn500940.aspx and that Stefan Roth has also produced a handy cheat sheet on his blog https://stefanroth.net/2014/11/05/microsoft-azure-operational-insights-search-data-explorer-cheat-sheet/

I will also likely blog some more scenario-focused posts, i.e. same use case and how to go thru more examples of ‘searches in action’.

I also keep updating this other post as I come up with some new useful queries https://blogs.msdn.com/b/dmuscett/archive/2014/10/19/advisor-searches-collection.aspx

Remember the feedback forum is always open for you to vote and ask what you would like us to improve in the service at https://feedback.azure.com/forums/267889-azure-operational-insights/

Happy searching!