KQL - Basics for SOC - Analysts #0

‌❕This article has not been completed yet. However, it may already contain helpful Information and therefore it has been published at this stage

Introduction:

The Kusto Query Language, or KQL for short, is a powerful query language. It is a domain-driven language (see Domain-Driven Design (DDD): A Summary) specifically designed for querying data and information. As mentioned, it is used to explore and analyse data. It doesn't matter if the data is structured, semi-structured or unstructured. The goal is to identify patterns, detect anomalies and recognise outliers in data series. The goal behind KQL was to develop a language that is easy to understand, read and write, and that allows users to query and interact with data effortlessly.

Fields of application:

KQL involves data analysis and aggregation. How can data be related to each other, evaluated, and relationships made visible? Topics such as geodata analysis and vector similarity search, which are particularly important in AI and machine learning, play a role. Time series operators and functions are also included. This means that if data is received over a period of time, whether it's IoT data or telemetry data, it should be analysed over time to identify differences between different time units. It's all about data exploration. Data from different sources should be explored, analysed and hopefully meaningful insights derived.

Microsoft context for KQL:

Microsoft is using KQL in several environments. Its primary use is in Azure Data Explorer, originally codenamed Kusto after Jacques Cousteau, with the goal of finding data truth in the sea of data. Azure Data Explorer (see What is Azure Data Explorer?) evolved from Kusto, but the Kusto query language remains. KQL is still used today in Azure Data Explorer. It is also used in Azure Monitor, specifically in Azure Log Analytics (see Log Analytics workspace overview) for log analysis of monitoring data in Microsoft Azure. In addition, KQL is used in Azure Resource Graph (see Overview of Azure Resource Graph ), which provides a complete index of Azure resources in the Azure environment. KQL is also used in security environments such as Microsoft Defender Suite (What is Microsoft Defender XDR? ) and Microsoft Sentinel (What is Microsoft Sentinel?), to query security alerts. Therefore, it is imperative for SOC analysts to understand and learn KQL.

Fundamentals of the syntax of KQL:

Let's take a closer look at the basic syntax of KQL. The first time you use KQL, you need to write a Kusto query. A Kusto query is a read-only request for data processing, meaning that it queries a dataset without modifying it. Any changes to the data are made beforehand, during data logging, telemetry or IoT data input. With KQL, data is queried and processed, allowing manipulation, calculations and other necessary operations, but never changing the data source, such as a database. The goal is to present the results in the form of a table, graph, dashboard or similar output. Similar to PowerShell, Kusto Queries uses a data flow model known as a pipe, where the output of one command serves as the input for the next. This is how Kusto Queries work. It should also be noted that KQL is case sensitive. Upper and lower case must always be respected.

Let's take a closer look at this using an example query (leveraging the DeviceNetworkEvents Table):

This KQL query 👇 is used to analyze device network events related to Microsoft OneDrive. It filters the events to include only those where the initiating process's product name is "Microsoft OneDrive." Then, it summarizes the data by counting the occurrences of each action type, resulting in a count of different action types associated with Microsoft OneDrive network events.

DeviceNetworkEvents
| where InitiatingProcessVersionInfoProductName == "Microsoft OneDrive"
| summarize ActionTypeCount = count() by ActionType

Let's break down the query and look at it step by step.

1. Table referencing

//1
DeviceNetworkEvents 
// Reference to a particular table (DeviceNetworkEvents table | created by MDE (Microsoft Defender for Endpoint))

2. Filtering

//2
| where InitiatingProcessVersionInfoProductName == "Microsoft OneDrive" 
// In this step, the entire table dataset of the last 24 hours is passed from the DeviceNetworkEvents table via a pipe '|', then a filter is set up with 'where', which specifies that we are only interested in values from the column 'InitiatingProcessVersionInfoProductName' with the value 'Microsoft OneDrive'.

3. Aggregation

//3
| summarize ActionTypeCount = count() by ActionType
// In the final step, the now filtered data is passed on again to a pipe '|' and aggregated. This is done by summarising and counting the data in individual action types.

It has been demonstrated that the pipeline notation, symbolised by the vertical bar "|", is used, so that the output of the first operator, e.g. DeviceNetworkEvents, is the input of the next operator, where InitiatingProcessVersionInfoProductName == 'Microsoft OneDrive'.Without this pipe and without passing on the data, this entire command would not work. And because the whole thing is nicely filled with comments, we have also seen that we can add comments to our KQL queries using the double slash.

Syntax rules (see KQL quick reference):

Block - a string that has to be entered like this
italics - parameter that has to be transferred for use
[] - optional values
() - at least one value required
| (pipe) - when used within [] or () as logical OR, otherwise KQL query pipe
[,....] - previous parameter can be repeated comma-separated
; - query terminator

Example leveraging the Search Operator:

[TabularSource |] search [kind=CaseSensitivity] [in (TableSources)] SearchPredicate

Example leveraging the Where Operator:

T | where Predicate

For more see KQL quick reference.

KQL - Basics for SOC - Analysts #0

Thomas Bründl

Thomas Bründl

Introduction:

Fields of application:

Microsoft context for KQL:

Fundamentals of the syntax of KQL:

Let's take a closer look at this using an example query (leveraging the DeviceNetworkEvents Table):

Syntax rules (see KQL quick reference):

References:

KQL - Basics for SOC - Analysts #4 - Distinct

KQL - Basics for SOC - Analysts #3 - Where

KQL - Basics for SOC - Analysts #2 - Search