This room introduces you to the analysis of security logs with Microsoft Sentinel KQL. As security engineers, we think of security logs as treasures because they can help us discover the hidden activities within our infrastructure. A quick background intro: Microsoft Sentinel is a cloud-native security information and event management (SIEM) product that utilizes ingested logs to provide better visibility into your environment. On the other hand, Kusto Query Language (KQL) is the tool used to proactively reveal the hidden secrets within those logs if you are the curious and hands-on type.

Imagine being able to:

• Track down rogue user activities accurately and identify potential breaches before they happen.
• Unravel complex security incidents by correlating events and figuring out every attacker's moves.
• Automating repeated tasks to focus on more serious threats and investigations.

The focus of this room is Kusto Query Language (KQL), which empowers you, the security analyst/engineer, to become a security sleuth, proactively hunting for threats to ensure your organization's digital infrastructure is safe from attackers. Let's dive into the world of KQL to discover the power of security logs analysis.

Learning Objectives

This room aims to provide you with the fundamental knowledge and skills necessary to use Kusto Query Language (KQL) for security analysis within MS Sentinel Log Analytics workspace. Upon completion, you will be able to:

• Better understand the core concepts and functionalities of Microsoft Sentinel as a Security Information and Event Management (SIEM) solution.
• Easily understand the benefits of using Kusto Query Language (KQL) in Microsoft Sentinel for day-to-day security operations.
• Understand how KQL interacts with data stored within MS Sentinel Log Analytics workspaces and its uses in querying and analyzing them.

Prerequisites

• Having completed the Microsoft Sentinel module.

Firstly, let's briefly understand how Microsoft Sentinel works. This is essential before delving into Kusto Query Language.

The cloud computing space is continuously growing, and cyber criminals are always prowling in the shadows, waiting to catch the security team unaware. Organizations with MS Sentinel properly configured are always on guard, protecting themselves against malicious attackers and working restlessly to keep their digital infrastructure safe from all cyber threats.

Microsoft Sentinel Explained

MS Sentinel is a cloud-native Security Information and Event Management (SIEM) solution enabling security administrators to better detect, investigate, and respond to security threats across their enterprise environment. Sentinel is built on top of Azure, providing a centralized platform for security monitoring, log collection, threat detection, response, and investigation. It is not only a SIEM solution but also a Security Orchestration, Automation, and Response (SOAR) solution, which enhances its capabilities.

Microsoft Sentinel Workflow

MS Sentinel can integrate seamlessly with Microsoft and third-party services to create a comprehensive security ecosystem for threat intelligence and security operations in the cloud or on-premise.

Integration With Microsoft Services

MS Sentinel offers pre-built connectors for easy integration with various Microsoft security services, eliminating complex configuration needs. Below are a few key integrations with other Microsoft services:

• Microsoft Entra ID: Sentinel integrates with Entra ID for identity protection, identity and access management, and threat detection. You will be able to monitor user activities, filter audit logs, identify suspicious login attempts, and enforce conditional access policies if configured
• Microsoft Defender: When Sentinel is integrated with Microsoft's Defender suite, it expands its threat detection capabilities to several Azure resources, such as lab machines, databases, and containers. This integration allows MS Sentinel to use Defender's advanced threat protection features for a more comprehensive security analysis. For instance, collecting endpoint security logs to detect and isolate devices with malware or even initiate an automated response
• Azure Logic Apps: Sentinel can leverage Azure Logic Apps to automate response and remediation workflows. This enables the harmonization of complex responses across different services when a threat is detected
• Azure Monitor: Integrating Azure Monitor with Sentinel allows the ingestion of metrics and logs from various Microsoft services, generating comprehensive security insights and analytics

Microsoft Sentinel can also use agent-based log collection tools to collect logs for servers hosted on-premises or in the cloud, providing a big-eye view of your security.

Integration With Third-Party Services

Kusto Query Language, also KQL is more than just a syntax. Within the context of Microsoft Sentinel, it is a tool for discovering and investigating suspicious activities. With each query, you can sift through large amounts of logs, detect subtle traces of cyber threats, and connect the dots to get actionable insight from the logs across your digital estate.

KQL in Action

As cyber threats continue to become sophisticated, MS Sentinel is always ready for action. It gives you insights into all malicious activities and uses the power of KQL to reveal suspicious movements in tables and charts.

With each query, the output gives more insight into every malicious activity, which the security team can then act upon to fortify the defenses of their digital estate.

KQL Explained

Kusto Query Language is developed by Microsoft to query large amounts of structured, semi-structured, and unstructured data. It was initially created for Azure Data Explorer but has been widely adopted across various Microsoft services, including Azure Monitor, Azure Sentinel, Microsoft 365 Defender, and more. It is worth knowing that KQL queries run against a log repository where the ingested logs are stored, in this case, a Log Analytics workspace.

A simple description of KQL would be that it is a:

1. Security analysis language: Used to filter, search, and analyze security logs and events.
2. Powerful and flexible tool: Used to run simple and complex queries to extract detailed security insights.
3. Tool for large datasets: Optimized for handling massive amounts of security logs.
4. Tool built-in for Microsoft Sentinel: Seamlessly integrated for data exploration and threat hunting.
5. Tool used in other Microsoft services: Such as Azure Data Explorer, Log Analytics, Azure Monitor, and Azure Sentinel.

In the hands of vigilant security administrators, KQL will help reveal areas where threats may be hiding within the infrastructure and, in turn, bringing the team peace of mind.

Kusto Query Language Editor

Example KQL Query

Below is what a KQL query may look like:

Heartbeat
| summarize AggregatedHeartbeatCount = count() by Computer
| order by AggregatedHeartbeatCount desc
| take 10

Explanation

• This query retrieves data from the Heartbeat table, which typically contains information about the health and status of devices or systems within the organization.
• Then, the summarize operator aggregates the data by counting the number of heartbeats for each unique computer.
• The result is then outputted in descending order based on the aggregated heartbeat count.
• Finally, the take operator limits the output to the top 10 computers with the highest heartbeats.

All the queries discussed in this room can be practiced by clicking Demo Log Analytics to launch a free demo workspace. However, the logs are dynamic, so you will likely not get the same result. Adjust the time frame to January 30, 2025 if no results are found. If you are required to log in, click the Cloud Details button below then Join Lab to find your login information in the Credentials tab. With the new Microsoft multifactor authentication enforcement, you will also be required to set up MFA to sign in.

Now that you have been introduced to MS Sentinel and KQL. Next, you need to familiarize yourself with some key concepts to fully grasp how KQL works to better query and analyze ingested logs.

Table

Since the log repository (Log Analytics workspace) uses tables to manage the various kinds of logs ingested, each table is usually associated with a specific data source. For instance, security event logs (SecurityEvent), Azure activity logs (AzureActivity), Windows firewall logs (WindowsFirewall), network monitoring logs (NetworkMonitoring), or any custom data sources. These tables are what you query when you run a KQL query. 

Note: Custom logs are identified with _CL at the end of the name.

Example

SecurityEvent

The gif above shows all the security events from the SecurityEvent log table for the past 24 hours.

Functions and Operators

Functions and operators are symbols or keywords that carry out operations, such as performing specific tasks or calculations on ingested logs. They are used to manipulate, aggregate, filter, transform, or analyze data and are usually invoked by name or customized parameters.

Examples

count(), sum(), avg(), where(), parse()

== - (equal to)

!= - (not equal to)

< - (less than)

render

summarize

| - (Pipeline)

Expressions

Expressions are combinations of values, functions, operators, and table names that evaluate a single meaningful unit. It defines conditions, calculations, transformations, and other operations.

Example

SecurityEvent
| where TimeGenerated >= ago(3h) and TargetUserName == "JBOX00$"
| project TimeGenerated, Account, Activity, Computer
| sort by TimeGenerated desc

Explanation

SecurityEvents - The table to query.

where TimeGenerated >= ago(3h) and TargetUserName == "JBOX00$" - This expression checks if the time column value is within three hours and the user account name is equal to JBOX00$.

project TimeGenerated, Account, Activity, Computer - This expression outputs the selected columns.

sort by TimeGenerated desc - This expression arranges the output in a descending order.

For easy reference, the table below summarises commonly used KQL functions, their basic syntax, and a brief description. Just so you know, KQL offers many functions beyond this list:

Kindly refer to the official KQL quick reference for more functions and their respective descriptions.

Note: The pipe | separates each line of the query.

In summary, functions and operators are either symbols or keywords that perform operations on ingested logs, while expressions are a combination of values, functions, and operators that evaluate a single unit. These elements are essential to KQL, enabling security analysts to analyze and manipulate security logs effectively.

The Structure

In the previous task, we discussed some key concepts when writing a KQL query. Let's combine them to make a statement. 

The structure of a KQL statement consists of multiple components, allowing you to retrieve, manipulate, and analyze security logs effectively. You start with a data source and navigate through a set of operators bound together using the pipe | delimiter.

Below is a typical example of a query combining multiple query components:

SecurityEvent
| where TimeGenerated > ago(1d)
| summarize EventCount = count() by Computer
| order by EventCount desc | limit 10 

Explanation

• Data source: The data source specifies the table from which you want to retrieve data.
  • SecurityEvent- Specifies the table from which data will be retrieved.

• Conditions: They are used to perform specific actions on data.
  • | where TimeGenerated > ago(1d) - Filters the data to include only events that occurred within the last 24 hours ago(1d)). This ensures that only recent events are analyzed.
  • | summarize EventCount = count() by Computer - Aggregates the filtered events by the Computer column and counts the number of events for each device.

• Output: This defines how you want to view the output result.
  • | order by EventCount desc - Sorts the aggregated data by the EventCount column in descending order desc).
  • | limit 10 - Limits the output to the top 10 devices with the highest number of security events.

After understanding the query mentioned above, you can try it with the Demo Log Analytics and your lab credentials if prompted to sign in, as explained in Task 3. Also, remember to switch the mode to KQL.

Congratulations! You did it. I am confident that you now understand the concept and structure of KQL queries. Let's explore real-world scenarios to see how KQL can support you, the security analyst, in gaining deeper insights into your security logs.

Real-Life Example

Scenario: Today at the office, you need to identify failed login attempts for a specific user account to investigate potential unauthorized access.

Solution: To identify failed login attempts, you can search the SecurityEvent table for failed login attempts using the query below. This will find all failed login attempts across your organization. You can modify the time range to expand your search.

SecurityEvent
| where EventID == 4625

Then, to focus on failed login attempts for a specific user account, you can add a filter to the query, as shown below:

SecurityEvent
| where EventID == 4625 and TargetUserName contains "admin"

You can also narrow down the results to see the failed login attempts in the past hour, as shown below:

SecurityEvent
| where EventID == 4625 and TargetUserName contains "admin"
| where TimeGenerated > ago(1h)

Notice the progression of the query and how extra statements were applied to give us a refined and specific result; in the output above, there is no result for the past hour. This query can be modified in different ways to fit what we want to achieve. There's no limit to what you can do with KQL.

KQL Technical Use Cases

Here are some technical KQL use cases in Microsoft Sentinel:

• Investigating security incidents: You can use KQL to analyze security alerts, identify the root cause of incidents, and investigate attack timelines by querying relevant logs.
• Hunting for malware: KQL queries can be used to detect anomalies and search for suspicious malware-related activities, such as malicious file downloads, unusual registry changes, or network manipulations.
• Detecting lateral movement: KQL can identify suspicious user logins from different devices or locations, which may indicate unauthorized access and lateral movement within your network.
• Monitoring user activity: As a security analyst, you can review login attempts, access patterns, and privileged user activity using KQL queries, which can help detect account misuse or compromises.
• Security operations automation: Security admins can automate security operations and incident response by integrating KQL queries into workflows and triggering alerts, notifications, or remedial actions based on query results.
• Alerting: In Microsoft Sentinel, custom alerts can be created using KQL queries to inform you of possible security events by specifying particular criteria in the query. Also, an alert rule can be created within KQL from a query.
• Log analysis and reporting: Additionally, you can use KQL queries to extract and analyze security logs, creating custom reports to determine your current security posture, usage and performance metrics, compliance requirements, and threat trends.

Note: To answer the questions, adjust to a custom starting time frame of January 30, 2025.

Summary

Let's summarise what we have learned so far:

We discussed MS Sentinel as a robust SIEM solution that enables organizations to easily identify, investigate, and respond to security threats. KQL, on the other hand, is an effective method of querying and analyzing security logs. When combined, they form a holistic security operations tool for securing modern digital infrastructure.

As we continue to explore KQL, you'll discover its extensive capabilities for enhancing your organization's overall security posture.

Learning Curve

To become a skilled security analyst and threat hunter, here are some pointers to get you going:

• Start simple: Begin with basic queries to identify core security events, and gradually progress to more complex ones as you gain confidence.
• Practice makes perfect: The more you practice writing KQL queries, the more comfortable and proficient you'll become.
• Unlock potential: As your KQL proficiency grows, you can delve into advanced concepts like joins, parse, and user-defined functions. These offer even greater capabilities for complex data analysis and threat-hunting scenarios.