Share via

Azure Operational Insights Search How To: Part I - How to filter big data

  • Article

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

With this blog post I am starting a series where I walk thru some concepts of the Microsoft Azure Operational Insights Search Syntax – the full documentation and syntax reference is here, but 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.

The first thing to know that the first part of a search query (before any “|” vertical pipe character – of which we’ll talk in a future blog post) is always a FILTER – think of it as a WHERE clause in TSQL: it determines WHAT subset of data to pull out of the system, from the Big Data store. After all, Searching a Big Data store is largely about specifying the characteristics of the data we want to extract, so it is natural that a query would start with the WHERE clause.

The most basic filters you can use are KEYWORDs – such as ‘error’ or ‘timeout’, or a computer name – this type of simple queries will generally return diverse shapes of data within the same result set. This is because we have different Types of data in the system – my 'query for ‘error’ in the screenshot below returned 100K ‘Event’ records (collected by the Log Management feature), 18 ‘Alerts’ (generated by Advisor Configuration Assessment) and 12 ‘ ConfigurationChange’ (captured by the Change Tracking Intelligence Pack):

Types of System Center Advisor Search Results

These are NOT really object types/classes: if you are familiar with OpsMgr, please try to FORGET all you know about Classes and Objects in SCOM! It’s much easier here: Type is just a tag, or a property, a string/name/category, that is attached to a piece of data.

Some documents in the system are tagged as Type:Alert and some are tagged as Type:PerfHourly, or Type:Event... you get the idea.

Each search 'result' (or document, or record, or entry) shows all the raw properties and their values for each of those pieces of data, and you can use those field names to specify in the filter that you want to retrieve only the records where the field has that given value.

'Type' is really just a field that all records have, but it is for any practical use not different from any other field.

Anyhow, by convention, we established that based on the value of the ‘Type’ field, that record will have a different 'shape' or form (different fields). Incidentally, Type=PerfHourly, or Type=Event is also the syntax that you need to learn to query for hourly performance data aggregates or events.

[Note that you can use either a colon or a equal sign after the field name and before the value: Type:Event and Type=Event are absolutely identical in meaning, you can chose the style you prefer.]

So, if the Type=PerfHourly records have a field called 'CounterName', you can write a query like Type=PerfHourly CounterName="% Processor Time"   

this will give you only the performance data where the performance counter name is "% Processor Time".

You can also be more specific and throw a InstanceName="_Total" in there (if you know Windows Performance Counters, you know what I am talking about).

Also you can click on a facet and another field:value filter will be automatically added to your filter in the query bar – i.e. screenshot below shows you where to click to add InstanceName:’_Total’ to the query without typing

Interacting with Fields / Filters / Facets in System Center Advisor Search

Your query now becomes

Type=PerfHourly CounterName=”% Processor Time” InstanceName=”_Total”

Note that you DO NOT HAVE to specify Type=PerfHourly at all to get to this result. Since the fields ‘CounterName’ and ‘InstanceName’ (at the time of this writing) only exist on records of Type=PerfHourly, even just the query below is specific enough to bring back the exact same results as the longer, previous one

CounterName=”% Processor Time” InstanceName=”_Total”

This is because all the filters in the query are evaluated as being in AND with each other: effectively, the more fields you add to the criteria, the less and more specific/refined results you get.

For example this query 
Type=Event EventLog="Windows PowerShell"
is identical to this query

Type=Event AND EventLog="Windows PowerShell"

and it will return all events that were logged in (and collected from) the 'Windows Powershell' eventlog in windows. If you add a filter multiple times (i.e. clicking repeatedly on the same facet), the issue is purely cosmetic: it might clutter the search bar but still returns the same identical results since the implicit AND operator is always there.

You can easily reverse the implicit AND operator by using a NOT operator explicitly, i.e.:

Type:Event NOT(EventLog:"Windows PowerShell")

or (equivalent)

Type=Event EventLog!="Windows PowerShell"
this will return all events from ALL OTHER logs, that are NOT the 'Windows Powershell' log.

Or you can use other Boolean operator, such as ‘OR’: the query below returns back records for which the EventLog is either Application OR System

EventLog=Application OR EventLog=System

With the above query you’ll get entries for BOTH logs in the same result set.

While removing the OR (hence leaving the implicit AND in place) such as the following query

EventLog=Application EventLog=System

Will produce NO results – because there isn’t a event log entry that belongs to BOTH logs – each event log entry was written in just to one of the two logs.

Easy.

Till the next installment. I’ll try to keep a frequent pace.

Comments