Must Learn KQL Part 8: The Where Operator

Chapter 8

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.

…

Hands-on Recommendations

Before jumping directly into coverage of the first KQL operator, I want to extend some recommendations on how to proceed to ensure you get the most out of the hands-on opportunities through the remainder of this series.

In each new part of this series, I’ll talk about a specific KQL operator, command, or concept and supply example queries that you can use to get hands-on experience. The examples will be available here in the text, but also in the Examples folder of the GitHub repository for this series (https://aka.ms/MustLearnKQL).

Recommendation 1: I know it will be tempting to just copy, paste, and run my query examples. But do yourself a favor and type them out instead. Use the blog page, or the book, as a side reference, and type out the queries character-by-character and line-by-line. I’m a big believer of learning by doing. Typing the queries out will solidify your new knowledge.

Recommendation 2: Consider using the KQL Playground (https://aka.ms/LADemo) from the Part 1 resources as your learning environment when typing out the queries. The KQL Playground contains a number of data connections that you may not have in your own environment. The examples that I provide will have been tested to work and to show results. There’s nothing more frustrating than being given an example and there are no results for your effort. You’ll immediately start to think you did something wrong or that the query itself is bad. So, please, if at all possible, use the KQL Playground.

With that, let’s jump into the first KQL operator…

Where Operator

Bear with me (and forgive me) while I repeat myself. In part Part 5: Turn Search into Workflow, I said the following…

Filtering the data is the key to everything. <= Read that again. Filtering the data that is returned produces exact, actionable data. It also improves the results performance of our queries. Where the search operator may return thousands of rows of data in 15 seconds (or less), by properly filtering the data to return exactly what is necessary returns just the number of rows of data we asked for which greatly improves the processing time. Where the search operator may have taken 15 seconds, our new Search structure query will take 5 seconds or less. The Where operator is the key to this operation. Learn it. Know it. Keep the Where operator reference page handy: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/whereoperator.

Rod Trent, circa Part 5 of the Must Learn KQL series

That still holds true. So, based on that, would you agree with me that that makes this Part 8 one of the most important in the series? You betcha.

The syntax for the where operator will always be the same. Using our knowledge from Part 3 on workflow, you know that the flow of the query needs to follow a logical path. We need to tell the query engine the table we want to query against, then we need to tell it how to filter that data.

Where operator syntax:

TableName
| where predicate

Allowable predicates:

• String predicates: ==, has, contains, startswith, endswith, matches regex, etc

• Numeric/Date predicates: ==, !=, <, >, <=, >=

• Empty predicates: isempty(), notempty(), isnull(), notnull()

Quick Note: has and contains are powerful predicates because they provide the capability to search for bits and pieces of string data. However, there is a slight difference. Per the Best Practices doc, the has operator is better performing. Just something to know.

Where operator example:

In the following example, I’ve added the commenting character (the double-forwardslash covered in Part 3) to each line to explain what it is accomplishing.

SecurityEvent // The table
| where TimeGenerated > ago(1h) // Activity in the last hour
| where EventID == 4624 // Successful logon
| where AccountType =~ "user" // case insensitive

As shown, the example queries the SecurityEvent table, looking for normal users (non-admins) that had a successful login in the last hour. Can you see that? For each command line (separated by the pipe character (|) I talked about in Part 3) the where operator is enacting on the data in a specific way based on the predicate. In the example, I’ve used the where operator three different times to further filter the results that will be produced. I can use the where operator ad nauseam, until the results are exactly what I need them to be.

Your results in the KQL Playground (https://aka.ms/LADemo) will look something like the following.

I’m keeping it simple here and focusing only on the string and time predicates. As we move on in the series, we’ll get to the other predicates.

EXTRA: There is one additional piece of clarification I need to make. In the third (last line) where statement of the example query there’s an interesting looking predicate (=~). The tilde (~) character can be used in string predicates to cause the query engine to ignore case (case insensitivity). So, for our example, I’m telling the query engine to find every occurrence of the word “user” in the AccountType column no matter if it’s spelled “User” or “user” or “uSEr”, etc. Otherwise, it’s going to return my request verbatim which could result in zero results for the AccountType column.

Here, try it yourself in the KQL Playground (https://aka.ms/LADemo) without the tilde and notice that the AccountType column is empty:

SecurityEvent // The table
| where TimeGenerated > ago(1h) // Activity in the last hour
| where EventID == 4624 // Successful logon
| where AccountType == "user" // case sensitive

The tilde is an extremely useful tool particularly if there have been data or schema changes.

EXTRA CREDIT: If you’re hungry for more of the where operator, and just want to continue building your KQL knowledge until the next part in this series (see the TOC), take the original query example to the KQL Playground (https://aka.ms/LADemo) and run it line-by-line to see how each line changes the results. You can insert and remove the double-forwardslash (//) character at the beginning of each command line to comment it out or to include it.

For example, the following query will show more data than just in the last hour because, as you can see, the TimeGenerated filter line with the double-forwardslash character.

SecurityEvent // The table
// | where TimeGenerated > ago(1h) // Activity in the last hour
| where EventID == 4624 // Successful logon
| where AccountType =~ "user" // case insensitive