Microsoft SC-500: KQL for Security Investigations

Kusto Query Language is the common investigative language across Microsoft Sentinel and Microsoft Defender advanced hunting. In the Microsoft Defender portal, analysts can query Defender XDR data and, when Sentinel is onboarded, use Sentinel workspace data in the same hunting experience. That makes KQL one of the most transferable technical skills in Microsoft security operations.

Microsoft’s current advanced hunting experience supports guided mode for analysts who do not yet know KQL and advanced mode for direct query authoring. The underlying language remains Kusto Query Language, with operators such as where, project, extend, summarize, join, and top forming the core of most security investigations.

KQL investigation skills belong inside Microsoft Identity & Security.

Begin with one investigative question

Good KQL starts with a hypothesis: which devices contacted this IP, which accounts ran this process, which users received this message, or which sign-ins occurred after this credential event?

KQL for analysts is more effective when the question comes before the dashboard.

Do not begin by joining every table in the portal and hope an incident emerges from the result.

Use where early

where reduces the working set to the time window, entity, action type, or condition relevant to the hypothesis.

Filtering early improves performance and readability.

Use exact identifiers when you have them, then broaden carefully if the investigation needs adjacent activity.

Project only useful columns

project makes the result set easier to read by selecting and renaming the fields investigators need.

Large security schemas can expose dozens of columns, most of which are irrelevant to one question.

A focused result is easier to export, compare, and hand to another analyst.

Use summarize to see patterns

summarize can count events, group by users or devices, calculate time ranges, and reveal which values appear unusually often.

This is useful for discovering whether one account, process, domain, or host dominates the suspicious activity.

Aggregation should support a hypothesis rather than replace inspection of the raw events behind the count.

Join only when the relationship matters

join can connect device events to identities, network activity to processes, or one stage of an attack to another.

Joins can also create expensive or misleading results when keys are not unique.

Threat hunting should use joins to test a specific relationship, then verify a sample of joined records before drawing conclusions.

Use time as part of the investigation

Security events are sequences. The same command before credential theft and after credential theft can have very different meaning.

Use timestamps, bins, ordering, and time-window comparisons to reconstruct the attack timeline.

Incident triage becomes stronger when KQL can confirm whether alerts belong to the same activity window.

Use shared queries and functions carefully

Microsoft Defender and Sentinel support saved and shared queries, functions, and sample query libraries.

Reuse is valuable when the logic is understood and maintained.

Do not trust a copied query simply because it came from a familiar repository; check the schema, time assumptions, and environment-specific fields.

Turn proven hunts into detections

An investigation query that repeatedly identifies malicious behavior may be a candidate for a custom detection or Sentinel analytics rule.

Detection engineering should begin with a hypothesis, validate expected false positives, and define what response should follow an alert.

The query should be tuned for scheduled detection rather than copied unchanged from an exploratory hunt.

Keep KQL investigation-focused

KQL skill is not measured by query length. The best query is the shortest one that answers the security question with trustworthy evidence.

For analysts preparing around SC-200, the durable pattern is to filter early, select useful columns, aggregate patterns, join deliberately, preserve time context, and turn proven hunting logic into repeatable detections.

Table selection is an investigative skill. Microsoft Defender advanced hunting exposes device, email, identity, application, cloud, and other tables depending on the licensed services and integrated Sentinel data. Start with the table that owns the event you are testing, then join outward only when the hypothesis requires more context.

Schema literacy matters because similar concepts can use different column names across tables. An account can appear as an object ID, account name, UPN, SID, or other identifier. Normalize identifiers before joining to avoid false mismatches or accidental many-to-many joins.

Use let statements to make complex investigations readable. Store a suspicious time window, list of hashes, user set, or reusable subquery at the top of the query, then reference it throughout. This makes it easier for another analyst to review and adjust one parameter without editing several filters.

Use dynamic arrays carefully. Operators such as make_set, mv-expand, and JSON parsing are powerful when security telemetry stores collections or nested fields, but they can expand one row into many and change result size unexpectedly. Validate counts before and after expansion.

Investigation queries should handle time zones explicitly. Microsoft security data is generally timestamped in UTC, while analyst reports or local incidents may be discussed in local time. Converting informally in your head is a common source of missed events around daylight-saving or cross-region activity.

Use materialize or optimized query structure only when performance evidence justifies it. The first goal is a correct query. Once a hunt is reused repeatedly or runs over large Sentinel datasets, query optimization can reduce execution time and resource use.

Entity lists from incidents can seed KQL. Start with known users, devices, domains, IPs, and hashes, then pivot to adjacent activity. This creates an evidence-driven expansion of scope rather than an open-ended search across every schema.

Queries used for response actions deserve extra review. Advanced hunting can support actions on query results, and custom detections can trigger automated workflows. A filter error that is merely noisy during exploration can become operationally serious when it drives isolation or account actions automatically.

Keep a small validated investigation library. Queries for suspicious PowerShell, impossible travel follow-up, OAuth abuse, mailbox activity, ransomware behavior, or lateral movement can be versioned and improved after incidents. This lets responders begin from trusted patterns while still adapting to the facts of the current case.

KQL investigations should be reproducible. Save the exact query, time range, parameters, and relevant workspace or tenant context used for important findings so another analyst can rerun the evidence later.

Query results should preserve source identifiers and timestamps when exported into incident notes. Screenshots are useful for communication but weaker as evidence because they hide schema, filters, and raw values that may matter during review.

When Sentinel data is available in the Defender portal, be aware of retention differences. Defender XDR advanced hunting has its own raw-data window, while Sentinel workspace data can follow configured analytics-tier retention. The same KQL interface does not mean every table has the same historical depth.

Use KQL to shorten investigations, not to prove expertise. A small query library, disciplined hypothesis, and careful result validation create more operational value than long clever syntax that no one else can maintain during an incident.

Queries should be peer-reviewed when they support major incident conclusions. A subtle join, timestamp, or field-assumption error can change the investigation narrative. A second analyst should be able to explain what the query proves and what it does not prove before the result becomes part of an executive or legal incident record.

Keep environment-specific constants such as tenant IDs, known admin accounts, or expected service IP ranges in small reusable lists or functions where possible. This makes hunts easier to update and reduces hardcoded values scattered across many saved queries.

Use comments for queries that are likely to be reused. Explain the hypothesis, expected tables, important assumptions, and any environment-specific field. During a fast-moving incident, a short note can prevent another analyst from misusing a query outside the context it was designed for.

Query libraries should have owners and review dates. Security schemas evolve as Defender and Sentinel add or rename data, and a query that silently stops matching the intended events can be more dangerous than one that fails loudly.

When a query becomes operationally important, test it with known benign and malicious examples. This provides confidence that the logic is selecting the intended behavior instead of merely returning a plausible-looking result set.

Keep time filters explicit in every reusable query. A hidden default or stale fixed date can produce convincing but incomplete evidence during an investigation.

Use saved queries as starting points, not unquestioned truth; verify schema, tenant context, and time range before using results for containment or executive conclusions.

Keep query evidence reproducible and reviewable.

Review hunting assumptions.