Must Learn KQL Part 9: The Limit and Take Operators
Chapter 9
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:
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.
…
Because limit and take are so similar and used for the same purposes, I’m going to combine those in this part of this series. I’m not going to rehash my hands-on recommendations here, but please check out the section in Part 8 for those if you either missed it or have forgotten. In my opinion, the hands-on part of this series is the most important piece.
Up front – there are no functional differences between limit and take. They’re like fraternal twins. They have the same origin and similar attributes but have different names and looks.
In some cases, there are those KQL operators or commands that have similar functions, but one is better than another in how it reacts with the underlying technologies. Or, better said, one is better performing in most situations than another. In fact, we have a living document around this. See the KQL Best Practices doc for more information. Take special notice of the has and contains operators in the list in the Best Practices doc since I talked about the String Predicates in Part 8.
That said, since there are no true functional differences between limit and take it comes down to personal preference.
Limit/Take operator syntax:
Tablename
| limit <number>
-or-
Tablename
| take <number>
There are a few things to keep in mind about these fraternal twin operators:
Sort is not guaranteed to be preserved. This speaks for itself. Don’t expect any special sorting of columns of data to work.
Consistent result is not guaranteed. No matter how many times you run the same query with limit or take, it will most assuredly produce different results. The results are always random.
Very useful when trying out new queries or performing data sampling. Data Sampling is a powerful capability of any data scientist or meager KQL query maven. This is a similar activity for when we used the search operator in Part 4.
Default limit is 30,000. No matter what number you supply in the query, the results will never show more than 30,000. That’s a hard limit. And, when you think about it, since limit and take are part of a data sampling technique, you may want to seriously rethink your strategy (and use a different operator) if you need more than 1,000 rows of data returned – and that’s a generous number.
limit/take operator example:
As recommended in Part 8, use the KQL Playground (https://aka.ms/LADemo) to test the following query example. And for those wanting to better retain the knowledge, try typing the query out instead of copying/pasting.
And guess what? I’ve supplied both the limit and take operator versions so you can start to formulate your favorite.
SecurityEvent // The table
| where TimeGenerated > ago(1h) // Activity in the last hour
| where EventID == 4624 // Successful logon
| where AccountType =~ "user" // case insensitive
| limit 10 //random data sample or 10 records
-or-
SecurityEvent // The table
| where TimeGenerated > ago(1h) // Activity in the last hour
| where EventID == 4624 // Successful logon
| where AccountType =~ "user" // case insensitive
| take 10 //random data sample or 10 recordsAlso notice that I’m using the same query example from Part 8 – just adding the limit and take command lines at the end. I’ll use this same query throughout (as much as possible) to show a standard method of query development that will lead to creating your very first Analytics Rule for Microsoft Sentinel. Creating an Analytics Rule for Microsoft Sentinel is a very similar process of starting simple and building bigger.
Your results for either query example will look like the following. Just remember that your results will be slightly different because of the random nature of the operators.
