Operations Management Suite Log Search How To: Part VIII – the IN operator and subsearches

This is the eight installment of a Series that walks thru the concepts of Operations Management Suite (formerly 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.

In the seventh post I continued with another of measure’s statistical functions – Sum() – and introduced the where command.

 

I am very excited to be writing about an important addition to our search syntax: we recently enabled the ‘IN’ operator (along with ‘NOT IN’), which allows you to use subsearches: a subsearch is a search that includes another search as an argument. They are contained in curly brackets within another "primary" or "outer" search. The result of a subsearch (often a list of distinct results) is then used as an argument in its primary search.

You can use subsearches to match subsets of your data that you cannot describe directly in a search expression, but which can be generated from a search. For example, if you're interested in using one search to find all events from "computers missing security updates" you need to design a subsearch that first identifies that "computers missing security updates" before it finds events belonging to those hosts.

So, we could express ‘computers currently missing security updates” as follows:

Type:RequiredUpdate UpdateClassification:"Security Updates" TimeGenerated>NOW-25HOURS | measure count() by Computer

Once we have this list, we can use this search as inner search to feed the list of computers into an outer (primary) search that will look for events for those computers. We do this by enclosing the inner search in curly brackets and feeding its results as possible values for a filter/field in the outer search using the ‘IN’ operator. This query would look like:

Type=Event Computer IN {Type:RequiredUpdate UpdateClassification:"Security Updates" TimeGenerated>NOW-25HOURS | measure count() by Computer}

that’s it!

Also notice the time filter I used in the ‘inner’ search: since I am counting on ‘System Update Assessment’ Solution to have a snapshot of all machines every 24 hours, I am making that inner query more lightweight and precise by only making that go back in time a day; the outer search instead still respects the time selection in the user interface, pulling up events from the last 7 days. [Also check my previous blog post about time in search]

Another thing to notice is that, since we are really only using the results of the inner search as a filter value for the outer one, you can still apply commands in the outer search, i.e. we can still group the above events with another measure command:

Type=Event Computer IN {Type:RequiredUpdate UpdateClassification:"Security Updates" TimeGenerated>NOW-25HOURS | measure count() by Computer} | measure count() by Source

 

Generally speaking, you want your inner query to execute fast – we have some service-side timeouts for it – and to return a small amount of results. If the inner query returns more results, we will truncate the result list, which will cause also the outer search to potentially give incorrect results.

Another rule is that the inner search currently needs to provide ‘aggregated’ results – or in other words, it must contain a ‘measure’ command; you cannot currently feed ‘raw’ results into an outer search.

Also, there can be only ONE ‘IN’ operator (and it must be the last filter in the query), and multiple IN operators cannot be OR’d – this essentially prevents running multiple subsearches: the bottom line is that only one sub/inner search is possible for each ‘outer’ search.

 

Even with these limits, this addition enables all new kinds of correlated searches, and allows you to define something similar to ‘groups’ (of computers, or users, of files – whatever the fields in your data contain). Some more examples:

 

All updates missing from machines where Automatic Update setting is disabled

Type=RequiredUpdate Computer IN {Type=UpdateAgent AutomaticUpdateEnabled!=Enabled | Measure count() by Computer} | Measure count() by KBID

All error events from machines running SQL Server (=where SQL Assessment has run)

Type=Event EventLevelName=error Computer IN {Type=SQLAssessmentRecommendation | measure count() by Computer}

All security events from machines that are Domain Controllers (=where AD Assessment has run)

Type=SecurityEvent Computer IN { Type=ADAssessmentRecommendation | measure count() by Computer }

Which other accounts have logged on to the same computers where account BACONLAND\jochan has logged on?

Type=SecurityEvent EventID=4624   Account!="BACONLAND\\jochan" Computer IN { Type=SecurityEvent EventID=4624   Account="BACONLAND\\jochan" | measure count() by Computer } | measure count() by Account

 

I am sure you will come up with other interesting search scenarios now.

Happy searching!

Comments

• Anonymous
  June 06, 2015
  Great blog - how do we find computers that have been down for a period of time or that haven't checked into OMS for a while?

• Anonymous
  June 07, 2015
  The comment has been removed

• Anonymous
  June 07, 2015
  Consider the following first two queries, and then combining them in a third one thru the IN syntax --Computers that have some data in the last 24 hours TimeGenerated>NOW-24HOURS | measure Max(TimeGenerated) by Computer -- Computers that have some data between 48 and 24 hours ago TimeGenerated>NOW-48HOURS TimeGenerated<NOW-24HOURS | measure Max(TimeGenerated) by Computer --computers that were reporting data yesterday (between 48 and 24 horus ago) but have not reported it today (last 24 hours) TimeGenerated>NOW-48HOURS TimeGenerated<NOW-24HOURS Computer NOT IN { TimeGenerated>NOW-24HOURS | measure Max(TimeGenerated) by Computer } | measure Max(TimeGenerated) by Computer

• Anonymous
  June 28, 2015
  If you want to write shorter and more readable sub-searches, give the 'DISTINCT' command a try - read my next post blogs.msdn.com/.../operations-management-suite-log-search-how-to-part-ix-the-distinct-command.aspx