In this module
1.1 The Tabular Data Model — Everything Is a Table
The Tabular Data Model — Everything Is a Table
Introduction
Every KQL query starts with a table and ends with a table. This is the single most important concept in KQL — and the one that experienced SQL users most frequently misunderstand. In SQL, a query can return a scalar value, a single row, a set of rows, or a table. In KQL, the output is always a table. A query that returns one number returns a table with one column and one row. A query that returns nothing returns an empty table with column headers but zero rows.
Understanding this matters because every operator in KQL accepts a table as input and produces a table as output. The pipeline chains these operators together: operator 1 produces a table → operator 2 consumes that table and produces a new table → operator 3 consumes that new table. If you understand the input and output shapes at each stage, you can predict the result of any query.
Figure K1.1 — Every KQL operator takes a table as input and produces a table as output. The pipeline reduces data at each step.
What a table is in KQL
A KQL table has:
SigninLogs
| getschema// How many rows in the last hour?
SigninLogs
| where TimeGenerated > ago(1h)
| count
// What columns exist and what types are they?
SigninLogs
| getschema
// What does a sample row look like?
SigninLogs
| where TimeGenerated > ago(1h)
| take 1// Step 1: How much data is there?
NewTable
| where TimeGenerated > ago(1h)
| count
// Step 2: What columns exist?
NewTable
| getschema
// Step 3: What does the data look like?
NewTable
| where TimeGenerated > ago(1h)
| take 5
// Step 4: What are the key entities?
NewTable
| where TimeGenerated > ago(1h)
| summarize dcount(AccountName), dcount(DeviceName)
// Step 5: What are the common values in categorical columns?
NewTable
| where TimeGenerated > ago(1h)
| summarize count() by ActionType
| sort by count_ desc
| take 20search *
| distinct $table
| sort by $table ascSigninLogs
| where TimeGenerated > ago(1h)
| take 1
| extend
Country = tostring(LocationDetails.countryOrRegion),
City = tostring(LocationDetails.city),
Latitude = toreal(LocationDetails.geoCoordinates.latitude),
Longitude = toreal(LocationDetails.geoCoordinates.longitude),
OS = tostring(DeviceDetail.operatingSystem),
Browser = tostring(DeviceDetail.browser),
FirstAuthMethod = tostring(AuthenticationDetails[0].authenticationMethod),
FirstAuthResult = tobool(AuthenticationDetails[0].succeeded).show table SigninLogs policy retentionTry it yourself
Run getschema against three tables you use regularly
Run getschema against three tables you use regularly. For each, identify:
- How many columns does the table have?
- How many columns are
stringtype vsdynamictype? - Which column contains the primary entity (user, device, IP) for that table?
Understanding the schema before writing queries prevents the most common class of errors: referencing columns that do not exist, treating string columns as integers, and missing data buried in dynamic (JSON) columns.
Check your understanding
1. You run SigninLogs | where ResultType == 0 | count and get the result 14,523. What is the shape of the output?
count produces a table with one column and one row. Understanding this principle is foundational to understanding how operators chain together.The myth: If you know SQL, you know KQL. Just swap SELECT for project and FROM for the table name.
The reality: KQL and SQL share superficial similarities but have fundamentally different execution models. SQL is set-based (define WHAT you want, the engine decides HOW). KQL is pipeline-based (define the SEQUENCE of transformations, each operating on the previous output). SQL subqueries and nested JOINs have no direct KQL equivalent. KQL's let statements and tabular pipeline produce different query patterns than SQL. Analysts who try to write KQL like SQL produce verbose, slow, incorrect queries. Learn KQL as a NEW language, not a SQL dialect.
Troubleshooting
"My query returns a table with columns but zero rows." The pipeline produced an empty table — one of the where filters eliminated all rows. Debug by removing filters one at a time from the bottom up: does the query return rows without the last where? If yes: that filter is too restrictive. Check for field name typos, case-sensitivity issues, or incorrect comparison operators.
You are writing a KQL query and cannot remember the exact operator syntax. Do you check the Microsoft documentation or ask Claude?
Check the documentation first for SYNTAX questions (exact parameter names, return types, operator behavior). The KQL documentation at learn.microsoft.com/kusto is the authoritative source. Claude may generate plausible but incorrect syntax — especially for newer operators or recently changed behavior. Use Claude for DESIGN questions: 'I need to detect accounts with sign-ins from more than 3 countries in 24 hours — what is the best KQL approach?' Claude excels at designing the query strategy; the documentation excels at providing the exact syntax.
You've learned how KQL processes data.
K0 gave you the query language's place in the Microsoft security stack. K1 took you through the semantics — tables, operators, the pipe model, and why KQL isn't SQL. Now you write the queries that find what attackers hope you miss.
- 12 modules of query craft — filtering and shaping, joins and unions, time-series analysis, summarisation, string manipulation, and geospatial analysis
- 68 KQL exercises — every one with a realistic dataset, a reference solution, and a discussion of alternative approaches
- K11 — Threat Hunting with KQL — the course's flagship module. Hypothesis-driven methodology, MITRE ATT&CK-aligned hunting across 7 techniques, UEBA composite risk scoring, and retroactive IOC sweeps
- K13 Capstone — The Hunting Lab — three complete investigation scenarios requiring every query skill from the course
- Hunt management and ROI metrics — the operating model that justifies KQL hunt programs to leadership