Multi-level group by in OMS

Summary: Learn how to group by multiple fields in OMS.

Good morning everyone, Leyla Kazemi here, and today I am happy to announce that multi-level group by is now supported in OMS Log Analytics search language. Before, you could only group by one field using OMS Search language. This new capability enables you to group by multiple fields, which lets you do much more advanced querying.

Multi-Level group by

Let’s say that you are looking at the security events across your computers, and you want to know which accounts on which computers have the highest failed logons. Previously, you could either look at accounts with the highest number of failed logons or computers with highest number of failed logons. You could not look at both at the same time. With multi-level group by, you can do the following:

Type=SecurityEvent EventID=4625 | measure count() by TargetAccount, Computer

This tells you in one view if an account has a high number of failed logons on multiple computers, which makes it much easier to identify a potential security breach.

You can even go one level deeper and find out for each of these logons, what type of logon (for example, network or interactive) have been used.

Type=SecurityEvent EventID=4625 | measure count() by TargetAccount, Computer, LogonType

Multi-Level group by using Interval

This new functionality also works with the interval query. Let’s say that you want to look at average CPU and available memory across a group of your computers. Previously, you were only able to either look at one particular counter across a set of computers or look at all the counters for one computer. This new functionality now allows you to look at multiple counters from multiple computers in one chart, which helps you better correlate different metrics, and, therefore, makes troubleshooting much easier. The following query compares the average CPU and available memory across a computer group with 10-minute interval.

Type:Perf (CounterName="% Available Memory") OR (ObjectName=Processor AND CounterName="% Processor Time")  Computer IN $ComputerGroups[TamagoCluster] | measure avg(CounterValue) by Computer, CounterName Interval 10minutes

 

As you can see, the legend at the bottom of the chart is presented as a tree view. You can collapse/expand any of the groups by clicking on them. You can also hide/unhide any of the groups. In the following example, the two last groups are unselected, and they are no longer shown on the chart. In order to do that, you hover on the group name. You will see SELECT ALL / SELECT NONE appear on the right of the group name. Clicking SELECT NONE hides all the line charts that belong to that group.

 

You can also perform multi-group by when multiple aggregations are used. Let’s say that, in the previous example, you want to look at both average and maximum of CPU and memory across a set of computers. Here is how you do it:

Type:Perf (CounterName="% Available Memory") OR (ObjectName=Processor AND CounterName="% Processor Time")  Computer IN $ComputerGroups[TamagoCluster]| measure avg(CounterValue) as AVG, max(CounterValue) as MAX by Computer, CounterName Interval 10minutes

That is all I have for you today. Hope you enjoy this new Log Analytics functionality. Don’t have an OMS account? Get a free Microsoft Operations Management Suite (#MSOMS) subscription.You can also get a free subscription for Microsoft Azure. I invite you to follow the Microsoft IT Management Twitter.

You can also try this out in a live environment!

Leyla Kazemi
Microsoft Operations Management Team

Comments

• Anonymous
  September 01, 2016
  Hey Thanks For the information man !! :) Funny Videos
• Anonymous
  September 11, 2016
  Thanks.Just We want to see the view in OMS
• Anonymous
  December 19, 2016
  Could I use this to get last ConfigurationState of a Windows Service? Stopped or running?