Purpose of This Lesson

Kusto Query Language (KQL) is the primary query language used to search and analyze large volumes of Microsoft security, audit, log, and telemetry data. For an SC-200 analyst, KQL is not just a reporting tool: it supports alert investigation, incident scoping, threat hunting, suspicious-activity analysis, and the creation of Sentinel detections and workbooks.

The key skill is understanding the query flow: start with a table, pass the results through filters and transformations with the pipe operator, and then summarize or organize the results into useful findings.

Key Concepts

What KQL is used for

KQL is a structured but flexible, query-based language optimized for log and telemetry data. It resembles SQL in some ways, but its syntax and execution model are designed for large, time-oriented datasets.

Common security uses include:

• Filtering millions of events to find a specific user, device, IP address, process, or activity type.
• Investigating Microsoft Defender XDR alerts and incidents.
• Hunting for indicators of compromise (IOCs) across endpoint, identity, email, and cloud data.
• Identifying abnormal user behavior and suspicious patterns.
• Building Microsoft Sentinel analytics rules.
• Performing threat hunting in Microsoft Sentinel.
• Creating queries that support Sentinel workbooks and investigation dashboards.
• Reviewing audit and compliance activity, such as user actions, file access, sensitivity-label changes, and data loss prevention (DLP) policy matches.
• Correlating security events from multiple sources over time.

Core KQL characteristics

Tables are the starting point

A KQL query normally begins with the name of a table. The table represents the dataset being queried, such as device logon events, network events, email activity, or a Sentinel data source.

The pipe operator creates a processing pipeline

The pipe character (|) passes the result of one query step to the next. This allows an analyst to progressively reduce and transform a large dataset instead of trying to interpret every record at once.

Filtering narrows the investigation

The where operator retains only rows that meet a condition. Multiple filters can be applied sequentially. For example, an analyst might first filter by account, then by logon type, and finally summarize the matching events.

Aggregation turns events into findings

Operators such as summarize calculate counts or other grouped results. Time bucketing with bin() is useful for identifying activity by hour, day, or another interval.

Time is a first-class investigation dimension

Security incidents are often understood through sequences: initial access, authentication, process execution, lateral movement, and subsequent actions. KQL’s time-oriented analysis helps analysts establish when activity occurred and identify unusual bursts or recurring patterns.

Microsoft Security Operations Context

Microsoft Defender XDR

KQL is used to query data available through Microsoft Defender XDR, including telemetry from services such as:

• Microsoft Defender for Endpoint
• Microsoft Defender for Identity
• Microsoft Defender for Office 365
• Microsoft Defender for Cloud Apps

In Defender XDR, KQL supports advanced hunting and helps analysts move beyond the summary shown in an alert or incident. An analyst can use it to investigate related events, search for IOCs, identify other affected devices or users, and determine whether activity is isolated or widespread.

Microsoft Sentinel

Microsoft Sentinel is Microsoft’s cloud-native SIEM and SOAR platform. It collects and correlates security data from Microsoft and non-Microsoft sources, and KQL is used to analyze the resulting data.

KQL is central to Sentinel activities such as:

• Creating analytics rules that detect suspicious conditions.
• Running ad hoc investigation queries.
• Performing threat hunting.
• Building workbooks and visualizations.
• Supporting incident investigations across multiple data sources.

Automation rules and playbooks can respond to Sentinel incidents, but KQL generally supplies the detection or investigation logic that identifies the relevant activity. Query design and automated response should therefore be treated as separate decisions.

Microsoft Purview and audit data

Microsoft 365 generates substantial audit and compliance data. KQL-oriented analysis can help security teams examine activity such as:

• User actions and access history.
• File access and sharing activity.
• Sensitivity-label changes.
• DLP policy matches.
• Activity that may need to be correlated with a security incident.

In practice, the exact query experience and available fields depend on the Microsoft service and the way its data is exposed. Always confirm the relevant schema and supported query surface before assuming that a table or column is available in a particular portal.

KQL Notes

The following example illustrates the basic flow of a KQL query:

DeviceLogonEvents
| where AccountName == "jdoe"
| where LogonType == "RemoteInteractive"
| summarize count() by bin(Timestamp, 1h)

This query:

• Starts with the DeviceLogonEvents table.
• Filters for events associated with the jdoe account.
• Further filters for remote interactive logons, such as remote desktop-style authentication activity.
• Counts matching records.
• Groups the counts into one-hour time buckets based on Timestamp.

The important exam concept is the pipeline, not memorizing this exact query. The general pattern is:

Table | filter | additional filter | summarize or transform

KQL comparison operators include == for equality, >= for greater than or equal to, and <= for less than or equal to. Field names, table names, and available values vary by product and dataset, so schema awareness remains essential.

Tool / Feature Decision Guide

Investigation need	Appropriate KQL context	Why it fits
Investigate an alert or incident involving Microsoft security products	Defender XDR advanced hunting	Queries Defender telemetry and helps expand the scope beyond the initial alert evidence
Search across Microsoft and third-party log sources	Microsoft Sentinel	Sentinel centralizes connected data sources for SIEM investigation and correlation
Create a reusable detection for suspicious activity	Sentinel analytics rule	A KQL query defines the detection conditions that can generate alerts or incidents
Explore activity interactively without creating a detection	Defender XDR hunting or Sentinel investigation query	Supports ad hoc analysis and hypothesis-driven investigation
Identify trends over time for an investigation or report	KQL aggregation with time grouping	Counts and groups events into useful time intervals
Present security data to analysts or stakeholders	Sentinel workbook	Uses query results to provide dashboards and investigation views
Respond automatically after a detection	Sentinel automation rule and/or playbook	Handles response actions; KQL normally identifies the activity rather than performing the response
Review Microsoft 365 audit or compliance activity	Applicable Microsoft audit/Purview query experience, with correlation to Sentinel where appropriate	Provides visibility into user, file, label, and DLP-related actions

Exam-Relevant Takeaways

• KQL is used to search and analyze security, audit, log, and telemetry data at scale.
• Defender XDR uses KQL for advanced hunting and investigation across endpoint, identity, email, and cloud-app data.
• Sentinel uses KQL for analytics rules, hunting, investigation queries, workbooks, and cross-source analysis.
• A KQL query commonly begins with a table and uses the pipe operator to create a sequence of filtering and transformation steps.
• where filters rows; summarize aggregates results; bin() groups time values into intervals.
• Time-based analysis is important for reconstructing event sequences and detecting bursts or abnormal patterns.
• KQL is similar in purpose to SQL but is optimized for log and telemetry workloads.
• Querying and automated response are different functions: KQL finds or describes activity, while automation rules and playbooks perform response actions.
• Table names, column names, and retention vary by product and data source. A query that works in one Microsoft portal may not work unchanged in another.

Common Exam Traps

• Confusing KQL with SQL: KQL may look familiar to someone with SQL experience, but it is the language used for Microsoft log and telemetry analysis and has its own syntax and operators.
• Choosing a playbook for investigation: A playbook is intended for automated response or orchestration. Use KQL to search, filter, correlate, and analyze the data first.
• Assuming every Microsoft security product exposes the same tables: Defender XDR and Sentinel have different schemas and data availability. Validate the relevant table and fields.
• Treating an alert as the complete incident scope: An alert is a starting point. Use KQL to look for related users, devices, events, and earlier or later activity.
• Ignoring time windows: A query without an appropriate time range can produce excessive results, increase investigation noise, or obscure the relevant sequence of events.
• Confusing aggregation with filtering: where removes nonmatching rows; summarize groups or calculates values from the rows that remain.
• Assuming KQL automatically creates a detection: A query becomes a recurring Sentinel detection only when implemented as an analytics rule with suitable configuration.

Real-World SOC Analyst Notes

• Start investigations with a narrowly defined time range and expand it when the evidence suggests earlier or later activity.
• Filter progressively by high-value pivots such as account, device, IP address, process, or alert-related indicator.
• Preserve the original alert and incident context before modifying or closing anything. Query results should support the investigation record, not replace it.
• Document the query, time range, data sources, and relevant results so another analyst can reproduce the conclusion.
• Expect incomplete visibility. Missing connectors, telemetry gaps, schema differences, and retention limits can all affect query results.
• Query cost and performance matter in large Sentinel environments. Avoid unnecessarily broad searches when a focused time range or data source can answer the question.
• Test analytics-rule queries against representative data before enabling them. Poorly tuned rules create false positives and alert fatigue.
• Treat automated actions with caution. A query may identify suspicious behavior, but automated disabling, blocking, or deletion should have clear conditions, permissions, exception handling, and change control.
• Correlate technical findings with identity, endpoint, email, and cloud context before escalating. A single event may be benign; a consistent sequence across sources is stronger evidence.

Quick Reference Summary

• KQL: Microsoft query language for large-scale log and telemetry analysis.
• Primary security uses: Investigation, hunting, IOC searches, anomaly analysis, detections, and dashboards.
• Defender XDR: Query and hunt across Microsoft security-product telemetry.
• Sentinel: Analyze centralized SIEM data and build analytics rules, hunts, and workbooks.
• Purview/audit: Examine Microsoft 365 activity and correlate relevant audit or compliance events with security investigations.
• Query flow: Table → pipe → filter → additional transformations → aggregation or output.
• Key operators in the example: where, summarize, count(), and bin().
• Operational principle: Use KQL to find and understand activity; use configured Sentinel automation to respond.

Flashcards

Q: An analyst needs to investigate whether a Defender for Endpoint alert affected other devices and accounts. Which capability should they use, and why?
A: Use Defender XDR advanced hunting with KQL. It can search related endpoint and other Defender telemetry beyond the evidence displayed in the original alert.

Q: When should an analyst use Microsoft Sentinel rather than only Defender XDR for a KQL investigation?
A: Use Sentinel when the investigation requires centralized correlation across Microsoft and non-Microsoft data sources or Sentinel-specific detections, hunts, or workbooks.

Q: What does the first table name in a KQL query represent?
A: It identifies the dataset being queried, such as device logon events. The remaining pipeline steps operate on the rows returned from that table.

Q: What is the purpose of the KQL pipe operator (|)?
A: It passes the result of one step to the next, allowing an analyst to build a sequential pipeline of filters, transformations, and aggregations.

Q: When would you use where instead of summarize?
A: Use where to retain only rows meeting a condition. Use summarize to calculate or group results, such as counting events by time period.

Q: What does summarize count() by bin(Timestamp, 1h) provide?
A: It counts matching records and groups them into one-hour timestamp intervals, making event frequency and activity bursts easier to analyze.

Q: Why is time-based analysis especially useful in incident investigation?
A: It helps reconstruct the order and timing of events, such as authentication followed by execution or lateral movement, and can reveal unusual bursts of activity.

Q: A query identifies malicious activity, but the requirement is to disable an account automatically. Which capability performs the response?
A: A Sentinel automation rule and/or playbook performs the automated response. KQL supplies the detection or investigation logic but does not itself execute the response workflow.

Q: What is the main difference between KQL and SQL in this course context?
A: Both are query languages, but KQL is designed for large-scale log and telemetry analysis in Microsoft services and security platforms.

Q: Why should an analyst verify table and column names before reusing a KQL query?
A: Schemas and available data differ between Defender products, Sentinel connectors, and audit sources. A query can fail or produce incomplete results if its fields are unavailable.

Q: An analyst wants a reusable Sentinel detection rather than a one-time search. What should they create?
A: Create a Sentinel analytics rule using the KQL detection logic, then configure its schedule, threshold, entities, and incident behavior appropriately.

Q: What is the investigation risk of starting with an unrestricted query across a very large dataset?
A: It can produce excessive noise, consume resources, and hide the relevant evidence. Start with a suitable time range and focused filters, then expand as needed.

Q: How can KQL help identify suspicious user activity?
A: It can filter and correlate user-related events and compare activity patterns over time, such as unusual logon types, timing, or access behavior.

Q: Why should query results be documented during an incident?
A: Recording the query, data source, time range, and findings preserves investigative context and allows another analyst to reproduce or validate the conclusion.

Practice Questions

Question 1

A SOC analyst must determine whether a remote interactive logon by a user occurred repeatedly during a particular period. Which KQL approach is most appropriate?

A. Start with a device logon events table, filter by the account and remote interactive logon type, then count events by hourly time bins.

B. Create a playbook that searches every mailbox for the user’s name.

C. Use a workbook without defining a query or time range.

D. Close the alert because one successful logon does not prove compromise.

Correct answer: A

The requirement is event filtering followed by time-based aggregation. A KQL pipeline using where, summarize, and bin() provides the requested result.

Question 2

An organization wants to correlate endpoint events with firewall, identity, and third-party application logs in one investigation. Which Microsoft capability is the best fit?

A. Defender for Endpoint device timeline only

B. Microsoft Sentinel

C. Microsoft Purview sensitivity labels only

D. A Defender XDR email alert without additional data connectors

Correct answer: B

Sentinel is the cloud-native SIEM designed to collect and correlate data from multiple Microsoft and non-Microsoft sources. KQL can then analyze those centralized datasets.

Question 3

An analyst writes a KQL query that correctly finds suspicious events during an ad hoc hunt. The security team wants the query to run regularly and generate Sentinel incidents. What should the analyst do next?

A. Convert the query into a Sentinel analytics rule and configure its detection and incident settings.

B. Save the query as a workbook only.

C. Add a second where clause and assume it will run automatically.

D. Replace the query with a playbook.

Correct answer: A

A recurring Sentinel detection is implemented as an analytics rule. A workbook presents query results, while a playbook handles response actions rather than defining the detection itself.

Question 4

A query returns no results after being moved from Defender XDR to Sentinel, even though the analyst believes the events exist. What is the most likely first troubleshooting step?

A. Assume the incident was a false positive and delete the alert.

B. Verify that the required data connector, Sentinel table, field names, and retention period are available.

C. Add an automation rule to generate more events.

D. Replace all filters with summarize.

Correct answer: B

Microsoft security products and Sentinel data sources can expose different schemas and retention. The analyst should confirm data availability and field mappings before changing the investigation conclusion.