Must Learn KQL Part 16: The Order/Sort and Top Operators

Chapter 16

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.

…

In this last part/chapter of the series-within-the-series for data view manipulation, I’m going to combine a couple operator types. Looking at the title of this part/chapter, it may seem that I’m focused on three operators (order, sort, and top), but really – like the Limit/Take operators from Part 9 – Order and Sort provide functionally no difference. This is one of those situations, again, where it becomes personal preference which one to use. In fact, when you read through the KQL reference doc it will tell you that…

…and then tell you to go check out the Sort operator page.

So, let’s focus on that first.

The Order By/Sort By operator type enables you to sort data columns in the query results so you can view the data first in a way that’s more meaningful. For example, the following query (which you can use in the KQL Playground https://aka.ms/LADemo), queries the SecurityEvent table for the last 7 days of data and shows a random 100 records in descending order by the time each returned record was generated.

SecurityEvent //the table
| where TimeGenerated > ago(7d) //look at data in the last 7 days
| order by TimeGenerated desc //sort or order the TimeGenerated data column in descending order
| limit 100 //show 100 random records

There are a couple important things to call out about the Order By/Sort By operations:

1. You can Sort by multiple columns and each column by different directions. For example, replace the Order By line above with the following: | order by TimeGenerated desc, Computer asc

2. The default view returned for data is descending order (desc).

3. If you are sorting by a data column that has null values (empty records), those will be displayed first using the default order (desc).

You have the option with Order/Sort to directly – as part of the sorting – to adjust where the nulls show up by adding either a nulls first or nulls last option as shown in the next example.

SecurityEvent //the table
| where TimeGenerated > ago(7d) //look at data in the last 7 days
| order by TimeGenerated desc nulls first //sort or order the TimeGenerated data column in descending order, showing nulls first
| limit 100 //show 100 random records

TIP: If the null records thing bothers you like it does me (must be an OCD thing), you may want to modify your query so that null records aren’t returned at all. Here’s a simple modification to the above query to stop showing data if the Account data column is empty.

SecurityEvent //the table
| where TimeGenerated > ago(7d) and isnotnull(Account) //look at data in the last 7 days where the Account column isn't empty
| order by TimeGenerated desc //sort or order the TimeGenerated data column in descending order
| limit 100 //show 100 random records

Just be careful with this. Sometimes, null columns can be an important delimiter.

Lastly, to continue to improve and hone your query knowledge – particularly for efficiency – the Top operator can be used to simplify our example. Ascending and Descending order work the same for Top as it does for Order/Sort, but we can combine expressions using Top, as is the case in the following example. Plus, the Top operator is a great way to retrieve the most recent records instead of always relying on random samples.

SecurityEvent //the table
| top 100 by TimeGenerated desc //Retrieving the top 100 records sorted in descending order by TimeGenerated

See what I did there? The Top operator is performing, essentially, the same operation as before, but it has simplified the query that it is required. In this case, though, I’m returning top values instead of the random ones that the Limit operator supplies. Top also provides the same options as Order/Sort for null values, so you can choose where to place the empty data columns in the display.