KQL fundamentals – Search operator
Level: Beginner | Reading time: 5 minutes
We have already seen in the article “KQL Overview – Kusto Query Language” what it is about and how to use the Kusto Query Language to hunt for threats in Sentinel and MDE. In this series, I want to bring you a basic, practical and quick guide on the main uses and operators.
To make it easier, let’s start with the operators with examples of each one. This topic is also a skill required for the SC-200: Microsoft Security Operations Analyst certification exam. Also, remember that I’m bringing the KQL usage examples focused more on the security and compliance products, but KQL is a complete data analysis language used in other Microsoft solutions.
Let’s remember that a KQL query is a read-only request to process data and return the results of that processing, without modifying the data or metadata. So, nothing better than starting with the most basic operator for a search: Search. However, it is being used here as an example since we have more efficient ways to perform searches using Where, but for didactic reasons, let’s start with Search.
By the way, when we use Search, we will receive a warning saying that this is not the best way to search for what we want, in this example I am doing it directly in the Perf table:
As I mentioned in the previous post, if you want to test this in a lab you can log in with any Microsoft account or create a trial and access the lab site that was created precisely for you to test the queries in practice. – https://aka.ms/lademo.
But before talking about the search, we have to understand that searching data using KQL is very similar to what we used to do using the SQL language: searching for databases, tables and columns. We are going to order a sequence of query instructions that we want to perform for the tables we want to search, the example below shows this visually and can also be seen in the free guide on Microsoft Learn.
In this example, the table we are looking up is SecurityEvent. We are going to see more operators in the future but let’s use this one as an example. We also have the EventID column that will bring us a filter of the data, representing the table rows and it will summarize the results creating a new column of the count by account. We will talk about other operators in future articles, for now, we will leave the reference image for now.
Now, thinking about the structure above, we can search tables and columns with one of the simplest operators: Search.
NOTE: // is used for commenting in KQL.
// This version checks all tables, although only the Perf table has this data type
search "Processor Time"
| summarize count(), avg(CounterValue) by Computer
// This version checks all strings in Perf table - much more efficient
Perf
| search "Processor Time"
| summarize count(), avg(CounterValue) by Computer
// This is the most efficient version
Perf
| where CounterName == "% Processor Time"
| summarize count(), avg(CounterValue) by Computer
In the next article, I will talk about another method to reduce the volume of data using the Where operator, that we used in this last example.
Summary
In this article, we learned how to use the Search operator in a KQL search.
Ref.:
Optimize log queries in Azure Monitor – Azure Monitor | Microsoft Docs
