2.1 How KQL Works and Key Tables

90 minutes · Module 2 · Free

How KQL Queries Work

Kusto Query Language is the query language behind Microsoft Sentinel, Defender XDR Advanced Hunting, and Log Analytics. Every detection rule, every hunting query, and every workbook you build in the Microsoft security stack runs on KQL. If you can write KQL, you can investigate. If you cannot, you are dependent on whoever wrote the queries you are running.

This module covers the operators and patterns that account for the vast majority of security work. It is not a complete language reference — Microsoft publishes that at learn.microsoft.com/en-us/kusto/query. What follows is the working subset that a SOC analyst reaches for daily, explained in the context of security tables and real investigation questions.

The pipe model

A KQL query is a pipeline. Data starts at a table, flows through operators separated by the pipe character |, and emerges as a result set. Each operator receives tabular input, transforms it, and passes it to the next operator.

Figure 2.1: The pipe model. Each operator transforms the data and passes it downstream.

Here is that model in action:

1
2
3
4
5
6
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != 0
| summarize FailedAttempts = count() by UserPrincipalName
| where FailedAttempts > 10
| sort by FailedAttempts desc

Read that top to bottom: start with sign-in logs from the last 24 hours, keep only failures, count failures per user, keep users with more than 10 failures, sort by count descending. The pipe model means you build queries incrementally — run each line, inspect the output, add the next operator.

Three rules that apply to everything in this module

1. KQL is case-sensitive for table names, column names, operators, and functions. SigninLogs works. signinlogs does not. String comparisons default to case-insensitive for most operators, but the language itself is strict.

2. Filter early, project late. Every row and column you eliminate early reduces the data the engine processes downstream. Put your where clauses — especially time filters — before aggregations or joins.

3. Time-filter first. Kusto maintains a highly efficient index on datetime columns. Filtering on TimeGenerated first allows the engine to skip entire data partitions without reading them. Always start with a time constraint.

Building queries incrementally

The most effective way to write KQL is to build up one line at a time. Run the first line to see the raw table. Add a filter and run again. Add another filter. Add a summarize. Check the output at every stage.

This is not a weakness or a beginner habit — it is how experienced analysts work. Every line is a checkpoint. If the output looks wrong, you know exactly which operator caused the problem. If you write a 15-line query and run it once, debugging is a nightmare.

Try it yourself

Open the Log Analytics demo environment and build this query one line at a time. Run after each line and observe how the result set changes.

1
SigninLogs

Then add:

1
2
SigninLogs
| take 10

Then:

1
2
3
SigninLogs
| where TimeGenerated > ago(7d)
| take 10

Line 1 alone (SigninLogs) returns every row in the table — potentially millions. The query editor may time out or return a truncated result.

Adding take 10 returns just 10 rows — a quick peek at the table structure. You can see all the available columns: TimeGenerated, UserPrincipalName, IPAddress, AppDisplayName, ResultType, ConditionalAccessStatus, and dozens more.

Adding the time filter first (where TimeGenerated > ago(7d)) then take 10 returns 10 rows from the last 7 days. This is the correct pattern: time filter first, then explore.

The Tables You Will Query

Before learning operators, you need to know where the data lives. Microsoft Sentinel and Defender XDR expose dozens of tables. These are the ones you will query most as a security analyst.

Figure 2.1b: The key security tables grouped by data source. Every table has a TimeGenerated column for time-based filtering.

You do not need to memorise these

The schema explorer in Advanced Hunting and Log Analytics lists every available table with all its columns. Click a column name to insert it into your query. Bookmark this subsection and return to it when you need to remember which table holds which data.

Table	What it contains	Source
`SigninLogs`	Interactive Azure AD / Entra ID sign-in attempts	Entra ID
`AADNonInteractiveUserSignInLogs`	Token refreshes, background app sign-ins	Entra ID
`SecurityAlert`	Alerts from Sentinel, Defender XDR, Defender for Cloud	Multiple
`SecurityIncident`	Incidents created from correlated alerts	Sentinel
`SecurityEvent`	Windows Security Event Log entries	Windows agents
`EmailEvents`	Email delivery and blocking events	Defender for Office 365
`EmailUrlInfo`	URLs within emails	Defender for Office 365
`DeviceProcessEvents`	Process execution on endpoints	Defender for Endpoint
`DeviceNetworkEvents`	Network connections from endpoints	Defender for Endpoint
`AuditLogs`	Entra ID configuration changes	Entra ID
`OfficeActivity`	SharePoint, OneDrive, Exchange, Teams operations	Office 365

Every table has a TimeGenerated column that records when the event was ingested. This is the column you use for time-based filtering. (Microsoft Sentinel data source schema reference — Microsoft Learn)

Exploring an unfamiliar table

When you encounter a table you have not used before, this three-query sequence tells you everything you need:

1
2
TableName
| take 5

Returns 5 rows so you can see the columns and data types.

1
2
TableName
| getschema

Returns the full schema: every column name and its data type (string, datetime, int, dynamic).

1
2
3
TableName
| where TimeGenerated > ago(1d)
| summarize count()

Tells you how much data the table receives per day — useful for understanding volume and query cost.

Check your understanding

1. Why should time-based filters always come first in a KQL query?

It makes the query easier to read

Kusto indexes on datetime columns — filtering on TimeGenerated first allows the engine to skip entire data partitions, dramatically improving performance

It is required by the KQL syntax

Kusto stores data in time-ordered partitions. A time filter lets the engine skip partitions outside your range without reading them. Without it, every query scans the entire table — which can be terabytes of data in a production workspace.

2. Which table records token refresh events and background application sign-ins?

SigninLogs

AADNonInteractiveUserSignInLogs

SecurityAlert

AADNonInteractiveUserSignInLogs captures authentication events that happen without user interaction — token refreshes, background app access, service-to-service calls. This is critical for token replay detection: attackers using stolen tokens generate non-interactive sign-in events.

3. You need to quickly check what columns a table has. Which query do you run?

where TimeGenerated > ago(1d)

TableName | getschema

TableName | count

getschema returns every column name and its data type. It is the fastest way to understand an unfamiliar table's structure. Alternatively, take 5 gives you sample rows which also shows the columns with example values.

← 2.2 Core Operators