cyber-101

What is KQL and how can I write effective queries for Security Operations?

New to Kusto? Learn what KQL is, why it beats SQL for log analysis, and how to write high-performance queries for Microsoft Sentinel and Security Operations

Mike Berggren

25 Jan 2026 — 6 min read

Photo by Jan Antonin Kolar / Unsplash

💡

This is part of an on-going series in cybersecurity foundations. Check the cyber 101 article tag index from time to time for more content.

KQL is quickly becoming a "superpower" that helps good analysts become great ones. It can be a little intimidating to newcomers, but once you understand the logic, it feels much more natural than SQL (in my opinion). In this article, I'll walk through the basics and include some tips for getting maximum value out of it.

Grab your beverage of choice, and let's jump in.

What KQL is and why it matters

Let's answer the obvious questions first: What the heck is KQL? Why should I care?

KQL stands for Kusto Query Language. It was developed by Microsoft around 2013/2014 through their tech incubator program. Like all great solutions, it was born out of necessity. Microsoft Power BI developers were facing massive challenges managing telemetry data at scale, and they needed a language optimized for speed and big data.

Which brings me to an important point: not all query languages are built for the same job. SQL is a general-purpose language intended for transactional databases with CRUD operations (Create, Read, Update, Delete). Log data, however, is fundamentally different:

Append-only: Once a log is recorded, it’s rarely changed or updated.
Time-series focused: Every log entry is anchored to a specific point in time.
Semi-structured: Log data can vary wildly, from structured JSON to unstructured free-form text.

Beyond the technicalities, there is a fundamental mental model difference. When analyzing logs as a human, it’s natural to think: "Start with this data → filter it → transform it → aggregate it." SQL forces you to think "backwards" (SELECT columns before you’ve even defined the FROM source). KQL follows your natural thought process.

All of these factors formed the inspiration for KQL and it's been gaining popularity ever since. The language has evolved from an internal Microsoft tool to an industry-standard query language for log analytics and security operations, with particularly strong adoption in SOC (Security Operations Center) teams and cloud infrastructure monitoring. Probably one of its strongest points is that it forms the backbone of Microsoft Sentinel.

KQL basics - the structure and operators you'll use daily

At its core, KQL uses a data-flow pipeline model. Results stream through operators chained with the pipe symbol (|). Linux and Unix fans will feel right at home here.

To better illustrate how this works, let’s compare a search for property damage caused by storms in the US since 2023, grouped by state.

In SQL:

SELECT State, AVG(DamageProperty)
FROM StormEvents
WHERE StartTime > '2023-01-01'
GROUP BY State

In KQL:

StormEvents
| where StartTime > datetime("2023-01-01")
| summarize avg(DamageProperty) by State

🙏

Note: Credit to John Savill for this excellent example. You can check out his deep-dive videos on YouTube. Check out his KQL tutorial here.

Did you notice the difference? The KQL version reads like a set of logical instructions: "Take the StormEvents table, filter by time, then calculate averages by state."

OK, so let's start getting to the fun stuff: operators. You can filter the data with a variety of options. Here are some popular ones:

where - This is very similar to SQL. It lets you give instructions for specific attributes need to be present (or not present) in output data.
- Example: Return storm events that happened in Florida and have property damage greater than $100,000
  
  StormEvents | where State == "FLORIDA" | where DamageProperty > 100000
project - This lets you specify what specific columns you want returned in the output.
- Example: Return storm events and specificaly show the State, Event Type, and Property Damage columns.
  
  StormEvents | project State, EventType, DamageProperty
take - This limits the returned data to a set amount of records (note that it will just select a seemingly random allotment of records from the filtered output). There's no guarantee that specific records will be returned in a specific order.
- Example: Return 100 storm events
  StormEvents | take 100
summarize - This groups rows based on specified criteria. It has a variety of functions it can pair with... things like:
- count() - count rows
- sum() - total values
- avg() - average values
- min() / max() - minimum/maximum values
- dcount() - count distinct values
- Example: count how many storm events have happened and group them by state: StormEvents | summarize EventCount = count() by State

Nifty right? OK, let's put it together in an example:

StormEvents
| where StartTime > ago(365d)                    // Filter to last year
| where State == "TEXAS"                          // Filter by state
| extend TotalDamage = DamageProperty + DamageCrops  // Calculate total
| where TotalDamage > 0                           // Only events with damage
| summarize 
    TotalEvents = count(),
    AvgDamage = avg(TotalDamage),
    MaxDamage = max(TotalDamage)
  by EventType                                    // Group by event type
| order by TotalEvents desc                       // Sort by event count
| take 10

In this case, we're asking KQL to show the top 10 most common types of damaging storm events in Texas over the past year, ranked by frequency, with their average and maximum damage amounts.

Common mistakes and how to avoid them

Before we wrap this article up, I wanted to cover a few common mistakes and include some tips for how to avoid them.

Case Sensitivity

KQL is case-sensitive. A table called SecurityEvent is not the same as securityevent. If your commands aren't matching the schema case, your query will return zero results.

Tip: Always verify exact column names in the schema sidebar before writing complex queries.

Confusing "contains" vs "has"

This is the most common performance killer.

contains: Searches for a substring (e.g., "Error" finds "Errors", "ErrorCode", etc.). This is slow because it scans every character.
has: Searches for a whole word or token. This is much faster because it uses the built-in term index.

Tip: Use has by default. Only use contains if you are looking for a partial string inside a larger word.

Wrong Operator Order

KQL executes from the top down. If you perform a heavy aggregation before filtering, you are making the engine work way harder than it needs to.

Bad: | summarize ... | where State == "TEXAS" (Summarizing the whole world first!)
Good: | where State == "TEXAS" | summarize ... (Filtering the data set immediately.)

Forgetting About Null Values

KQL doesn't implicitly handle null or empty fields in the dataset. So it's possible that rows with "unknown" values would be absent from results. That may (or may not) be something you want.

For example, if you want to see storm damage records that are greater than $1000 and some incidents don't have a value recorded, those could be missing.

StormEvents 
| where DamageProperty > 1000  // Misses rows where DamageProperty is null

StormEvents 
| where isnotnull(DamageProperty) and DamageProperty > 1000

Tip: Use isnotnull(), isnull(), or isempty() to handle missing data explicitly.

Conclusion and next steps

Phew! We covered a lot here. So what's next? Really, there's no replacement for practice. If you have a Microsoft azure account, check out Microsoft's lab/demo environment here: http://aka.ms/lademo

Additionally, check out these resources:

Azure Data Explorer Technology 101 | Microsoft Azure Blog

For our own troubleshooting needs, the Azure Data Explorer team wanted to run ad-hoc queries on the massive telemetry data stream produced by our service. Finding no suitable solution, we decided to create one.