Must Learn KQL Part 11: The Summarize Operator

Chapter 11

This post is part of an ongoing series to educate about the simplicity and power of the Kusto Query Language (KQL). If you’d like the 90-second post-commercial recap that seems to be a standard part of every TV show these days…

The full series index (including code and queries) is located here:

https://aka.ms/MustLearnKQL

The book version (pdf) of this series is located here:

https://github.com/rod-trent/MustLearnKQL/tree/main/Book_Version

The book will be updated when each new part in this series is released.

…

For this part in this Must Learn KQL series, I once again want to take the logical next step as we march toward generating our very first Microsoft Sentinel Analytics Rule (see the TOC for the cadence). We have a lot of ground to cover before then, but the next few operators we talk about are useful for various reasons – one of those reasons, like this section’s Summarize operator talk, is to produce number data to encapsulate actions. By creating thresholds, we can generate additional logic for how we want to react to situations. For example, if there’s one person that failed login in the last 10 days, it’s a non-issue. But, if that account failed login 100 times in the last 5 minutes – well – we have a problem. Summarizing the data makes it more meaningful.

The Summarize operator does just what it suggests – it summarizes data. In deeper terms, it produces a table (in the results) that aggregates the content of the input table. As an example of this, use the following KQL query in the KQL Playground (https://aka.ms/LADemo) to see the results. And, as before, try typing the query into the KQL Playground instead of just a copy/paste operation. If you get an error, you might’ve fat-thumbed something so you can use the inline code to compare against. Query troubleshooting is a great skillset to have.

SecurityEvent // The input table
| where TimeGenerated > ago(1h) // Activity in the last hour
| where EventID == 4624 // Successful logon
| summarize count() by AccountType, Computer //Show the number of successful logons per computer and what type of account is being used

Your results should be similar to the following:

Summarize Operator Syntax

Tablename
| summarize Aggregation [by Group Expression]

• Simple aggregation functions: count(), sum(), avg(), min(), max(),

• Advanced aggregation functions: arg_min(), arg_max(), percentiles(), makelist(), countif()

The Simple aggregations should speak for themselves. While the Advanced ones may require a bit more information. I’ll leave these descriptions here for posterity, but we’ll actually circle back later in this series to cover them in depth. Again, our series is a building operation. I don’t want to give you too much too soon and want to do so in a logical fashion so it all makes sense and learning is easier to retain.

Advanced aggregations:

• arg_min(), arg_max(): returns the extreme value

• percentiles():   returns the value at the percentile

• make_list(), make_set(): returns a list of all values/distinct values respectively

Now, that we’re deep into this series with Part 11, I’m going to attempt to do a bit less handholding. If this is your first introduction to this series, I highly suggest going back and making it through from the start because each new section or chapter builds on the previous ones. You can find the entire series tabulated in the TOC.

With that, I want to leave you with some additional Summarize exercises that you can work with in the KQL Playground (https://aka.ms/LADemo). These use the Advanced aggregates and I’ll refer back to these later.

SecurityEvent
| where EventID == 4624
| summarize arg_max(TimeGenerated, *) by Account 

AzureDiagnostics 
| summarize arg_max(TimeGenerated, *) by ResourceId

SecurityEvent 
| summarize AdminSuccessfullLogons = countif(Account contains "Admin" and EventID == 4624), AdminFailedLogons = countif(Account contains "Admin" and EventID == 4625)

As you can tell, this is not quite the end of our Summarize operator discussion. There will be plenty more. In fact, other than later in the series, I’ll talk about Summarize even more in the very next part when I cover the Render operator in Part 12.