Introduction
The max
and min
aggregation functions are common to almost every language, and the Kusto Query Language is no exception. As you would think, when you pipe in a dataset max
returns the maximum value for the column name you pass in. Likewise min
returns the lowest value.
In addition, there are variants for each, maxif
and minif
. We’ll see examples for all of these in this post.
The samples in this post will be run inside the LogAnalytics demo site found at https://aka.ms/LADemo. This demo site has been provided by Microsoft and can be used to learn the Kusto Query Language at no cost to you.
If you’ve not read my introductory post in this series, I’d advise you to do so now. It describes the user interface in detail. You’ll find it at https://arcanecode.com/2022/04/11/fun-with-kql-the-kusto-query-language/.
Note that my output may not look exactly like yours when you run the sample queries for several reasons. First, Microsoft only keeps a few days of demo data, which are constantly updated, so the dates and sample data won’t match the screen shots.
Second, I’ll be using the column tool (discussed in the introductory post) to limit the output to just the columns needed to demonstrate the query. Finally, Microsoft may make changes to both the user interface and the data structures between the time I write this and when you read it.
Max
The max
function is easy to use. In this example we use summarize
to call an aggregation function, in this case max
.
Here we can see the maximum CounterValue in the Perf table where the CounterName was Free Megabytes was 236,999.
Using Max in Other Places
You can use max
in many other places in KQL. As one example, refer back to my previous post Fun With KQL – Top-Nested. Instead of the count
or sum
aggregations we used in the post, we could have also used max
.
In this example, we used the max
function to rank our top nested values.
MaxIf
There is a variant to max
called maxif
. It allows us to include a condition in the second parameter such that in order for the value to be considered for max
, the condition must be true.
In this example, we use CounterValue in the first parameter, then we put the condition CounterName == "Free Megabytes"
in the second parameter, thus restricting the search for a maximum value to only rows with Free Megabytes in the CounterName.
As of now, you may not see much difference between using the combination of where
and max
versus the maxif
. In a moment we’ll see another way to use maxif
, but for now, lets move onto min
.
Min
The min
function can be used like max
, except it returns the lowest value in the column indicated.
Here, we found the minimum CounterValue in the dataset that was passed in was 34.
Again, like max
, the min
aggregate function can be used in many places in KQL, like the Top-Nested
operator.
MinIf
min
also as an alternate version, minif
. Just like maxif
, you pass the column name as the first parameter and the condition in the second parameter.
Since it is so similar to maxif
we’ll skip a detailed look at it for now, but we’ll show an example of it momentarily.
Max and Min as an Output Columns
It’s possible to include max
and min
as output columns in your query. In this example we used summarize
to calculate the max
and min
values, giving them better names.
Using by CounterName
will group the summarized values by their CounterName, and include the CounterName column in the output.
I used the in
operator to limit to just two CounterNames, but you could include all of them, or your own set.
MaxIf and MinIf as Columns
In the previous example, we saw how to create columns to hold min
and max
values in the output. It had one drawback though. The values were for one of the two CounterNames we limited the results to. There was no way to distinguish which CounterName these values reflected.
This is where the maxif
and minif
aggregate functions come into play.
In this example we create four columns using the summarize
operator. For each column we use either maxif
or minif
to create a value for just the CounterName we want.
maxif
and minif
can be used in other places as well. Think back to the previous example with top-nested
. Perhaps we were only interested in a handful of ObjectNames.
The second line of the query could have been written:
| top-nested 3 of ObjectName
by ObjectMax = maxif( CounterValue
, CounterName in ("CounterName1", "CounterName2")
)
This functionality would really let us hone in on just the data we need.
See Also
The following operators, functions, and/or plugins were used or mentioned in this article’s demos. You can learn more about them in some of my previous posts, linked below.
Conclusion
In this post we saw how to use the aggregate functions min
and max
. First we saw how to use them with summarize
to return a specific value, then saw how to use them with part of another query. We also saw their alternates, maxif
and minif
.
The demos in this series of blog posts were inspired by my Pluralsight courses on the Kusto Query Language, part of their Kusto Learning Path.
The first course in the series is Kusto Query Language: Getting Started. The second course is Kusto Query Language: Beginning Operators. The third course, to be published soon, is Kusto Query Language: Basic Scalar Operators
I have two previous Kusto courses on Pluralsight as well. Kusto Query Language (KQL) from Scratch and Introduction to the Azure Data Migration Service, and these are two of the many courses I have on Pluralsight. All of my courses are linked on my About Me page.
If you don’t have a Pluralsight subscription, just go to my list of courses on Pluralsight . At the top is a Try For Free button you can use to get a free 10 day subscription to Pluralsight, with which you can watch my courses, or any other course on the site.