KQL fundamentals – Where operator
Level: Beginner | Reading time: 5 minutes
We saw in the last post about KQL fundamentals – Search operator, how to get started in the world of searches using the Search operator. In this article we will evolve a little more to another very useful one: Where.
It is also worth remembering that this subject is charged in the certification exam SC-200: Microsoft Security Operations Analyst.
As we’ve already advanced a bit, let’s get straight to the point, but if you want to see all the articles in this series, go to the following link: https://cybergeeks.cloud/category/kql/.
Where – It’s pretty similar to the Search operator as we talked about in the last article, but more efficient because instead of doing like search, and fetching all columns by values, it limits the result based on the conditions you specified. A very practical example is to search a table for results of events generated only in the last day or hour. You will see that this is one of the most used operators.
Example 1 – security events from up to 1 day ago.
SecurityEvent
| where TimeGenerated > ago(1d)
Example 2 – I can specify time + an event id.
SecurityEvent
| where TimeGenerated > ago(1h) and EventID == "4624"
Example 3 – I can add more conditions to the search.
SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == 4624
| where AccountType =~ "user"
Note that in the search above we have two string operators, which are: == and the =~. Let’s understand what they are in the table below:
| Expression | Description | Example |
|---|---|---|
| == | Check equality – case sensitive | Level == 8 |
| =~ | Check equality – case insensitive | EventSourceName =~ "microsoft-windows-security-auditing" |
| ! =, <> | Check inequality (both expressions are identical) | Level != 4 |
| and, or | Required between conditions | Level == 16 or CommandLine != "" |
Let’s see a practical example of a Microsoft Defender for Endpoint search, which can be performed on the DeviceNetworkEvents table, accessed through the portal – https://security.microsoft.com/advanced-hunting or even through the Azure Sentinel portal if it is receiving logs from the MDE.
- Search device events by given IP address
DeviceNetworkEvents
| where RemoteIP == “200.1.2.3”
- We can also list devices containing virus file name in DeviceFileEvents table.
DeviceFileEvents
| where FileName == 'Invoice.pdf.exe'
Tip: To run the search you can use the Run Query button, but it is much more practical to use Shift + Enter.
Evolving the Where a little bit, I can add more filters, such as: if I want to search for devices with a Phishing file extension (double extension) such as .pdf.exe, .docx.exe, .doc.exe, .mp3.exe . In this example, we use the where and also the boolean OR operator.
DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName endswith ".pdf.exe"
or FileName endswith ".doc.exe"
or FileName contains ".docx.exe"
or FileName contains ".mp3.exe"
| project Timestamp, DeviceName, FileName, AccountSid, AccountName, AccountDomain
| top 100 by Timestamp
In the previous example, I also used the project operator, which will be detailed soon in a future article.
In the future, I will also show you how to use advanced hunting queries more proactively to create custom detection rules. These rules run automatically to check for and respond to suspicious breach activity, misconfigured machines, and other findings.
Summary
In this article, we advance the study of KQL with some practical examples of the where operator.
References:
Microsoft Defender for Endpoint Commonly Used Queries and Examples – Microsoft Tech Community
Using where – Learn | Microsoft Docs
Thank you!
