KQL for Beginners: Learn Kusto Query Language with Real Security Examples

If you work with Microsoft Defender, Microsoft Sentinel or Azure Monitor, sooner or later someone will say "just run a KQL query". For many IT admins that's the moment things get scary. The good news: KQL (Kusto Query Language) is one of the easiest query languages to learn. You can write useful security queries after learning about ten operators.

This KQL for beginners guide teaches you those operators with real security examples: failed sign-ins, suspicious emails and malicious PowerShell. No database background needed.

The one idea to remember: a KQL query starts with a table and passes the data through a series of steps separated by the pipe symbol |. Each step filters, shapes or summarizes the result of the previous step.

What is KQL and where is it used?

KQL is a read-only query language created by Microsoft for searching large amounts of log data quickly. You'll use it in:

• Microsoft Defender XDR advanced hunting (email, identity, endpoint data).
• Microsoft Sentinel, Microsoft's cloud SIEM.
• Azure Monitor / Log Analytics for server, application and Entra ID logs.
• Azure Data Explorer and Microsoft Fabric.

Because KQL only reads data, you can't break anything by experimenting. That makes it perfect for learning.

Where can I practice KQL for free?

• Your Defender portal: Investigation & response > Hunting > Advanced hunting (if your organization has the license).
• Azure Data Explorer help cluster: a free Microsoft cluster with sample data such as the StormEvents table. Sign in with a Microsoft account at dataexplorer.azure.com.
• Log Analytics demo environment: Microsoft offers a demo workspace with sample security data.

Your first KQL query

EmailEvents
| take 10

That's a complete query. It takes the EmailEvents table and shows any 10 rows. Use take to peek at a table and see what columns it has before writing a real query.

The 10 KQL operators you need to start

1. where: filter rows

EmailEvents
| where Timestamp > ago(1d)
| where SenderFromDomain == "example.com"

ago(1d) means "one day ago". You can use m for minutes, h for hours and d for days. Always filter on time first; it makes queries much faster.

2. Comparison operators

Operator	Meaning	Example
==	Equals (case-sensitive)	ErrorCode == 0
=~	Equals (case-insensitive)	AccountUpn =~ "user@contoso.com"
!=	Not equal	DeliveryAction != "Blocked"
has	Contains a whole word (fast)	Subject has "invoice"
contains	Contains any substring (slower)	Subject contains "invo"
in	Matches any value in a list	ErrorCode in (50126, 50053)
has_any	Contains any of several words	ProcessCommandLine has_any ("-enc", "bypass")

3. project: choose columns

EmailEvents
| where Timestamp > ago(1d)
| project Timestamp, SenderFromAddress, RecipientEmailAddress, Subject

4. extend: add a calculated column

EmailEvents
| where Timestamp > ago(1d)
| extend SenderDomain = tolower(SenderFromDomain)

5. summarize: count and group

This is where KQL gets powerful. summarize works like a pivot table:

EmailEvents
| where Timestamp > ago(7d)
| summarize Emails = count(), Recipients = dcount(RecipientEmailAddress) by SenderFromDomain

Useful functions: count(), dcount() (distinct count), countif(), min(), max(), make_set() (list of unique values) and arg_max() (the latest row per group).

6. order by / top: sort results

EmailEvents
| where Timestamp > ago(7d)
| summarize Emails = count() by SenderFromDomain
| top 10 by Emails desc

7. let: store values for reuse

let suspiciousDomain = "fake-docs.example";
EmailEvents
| where Timestamp > ago(30d)
| where SenderFromDomain =~ suspiciousDomain

Put the values you change often in let lines at the top, so you edit them in one place.

8. join: combine two tables

EmailEvents
| where Timestamp > ago(1d)
| join kind=inner (EmailUrlInfo | where Timestamp > ago(1d)) on NetworkMessageId
| project Timestamp, RecipientEmailAddress, Subject, Url

kind=inner keeps only matching rows; kind=leftouter keeps all rows from the left table even without a match.

9. distinct: unique values

EmailEvents
| where Subject has "invoice"
| distinct RecipientEmailAddress

10. render: make a chart

AlertInfo
| where Timestamp > ago(14d)
| summarize Alerts = count() by bin(Timestamp, 1d), Severity
| render timechart

bin(Timestamp, 1d) groups events into daily buckets, perfect for trends.

5 real-world security queries for beginners

Failed sign-ins per user (password spray check)

EntraIdSignInEvents
| where Timestamp > ago(1d)
| where ErrorCode == 50126
| summarize FailedLogons = count(), IPs = make_set(IPAddress, 5) by AccountUpn
| order by FailedLogons desc

Error 50126 means a wrong username or password. Many failures for many users from the same IPs suggest password spraying. Older tenants may use AADSignInEventsBeta instead.

Phishing emails that reached the inbox

EmailEvents
| where Timestamp > ago(7d)
| where ThreatTypes has "Phish" and DeliveryLocation == "Inbox/folder"
| project Timestamp, RecipientEmailAddress, SenderFromAddress, Subject

Encoded PowerShell commands (common in malware)

DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName in~ ("powershell.exe", "pwsh.exe")
| where ProcessCommandLine has_any ("-enc", "-EncodedCommand")
| project Timestamp, DeviceName, AccountName, ProcessCommandLine

Top 10 external sender domains this week

EmailEvents
| where Timestamp > ago(7d) and EmailDirection == "Inbound"
| summarize Emails = count() by SenderFromDomain
| top 10 by Emails desc

High-severity alerts in the last 24 hours

AlertInfo
| where Timestamp > ago(1d) and Severity == "High"
| project Timestamp, Title, Category, ServiceSource

Common beginner mistakes

• Forgetting the time filter: queries without ago() scan everything and run slowly or time out.
• Running several queries at once: in Defender, only the last query in the editor runs. Select one query before clicking Run.
• Case problems: == is case-sensitive. Use =~ for email addresses and names.
• Using contains everywhere: prefer has for whole words; it's much faster on big tables.
• Time zones: Timestamp is in UTC. In India, add 5 hours 30 minutes (Timestamp + 5h + 30m).

KQL cheat sheet

I want to...	Use
See sample rows	| take 10
Filter rows	| where Column == "value"
Pick columns	| project Col1, Col2
Add a column	| extend New = expression
Count by group	| summarize count() by Column
Sort	| order by Column desc
Top N	| top 10 by Column
Combine tables	| join kind=inner (Table2) on Key
Chart over time	| summarize count() by bin(Timestamp, 1h) | render timechart

What to learn next

Once you're comfortable with these operators, use them on real investigations. Our 20 Microsoft Defender XDR advanced hunting queries show how they combine for phishing, identity and endpoint hunting. Then learn the most important email table in our EmailEvents table guide, and automate your best queries with custom detection rules.

FAQ

Is KQL hard to learn?

No. If you understand Excel filters and pivot tables, you can learn the basics of KQL in a few hours. Most daily security queries use fewer than ten operators.

Is KQL the same as SQL?

No, but they're similar in purpose. KQL reads top to bottom with pipes, which many people find easier than SQL. KQL is read-only; it can't change or delete data.

Is KQL used in Microsoft Sentinel and Defender the same?

Yes, it's the same language. The table names differ (for example, SigninLogs in Sentinel vs EntraIdSignInEvents in Defender), but the operators are identical.

How long does it take to learn KQL for security?

A few days of practice for the basics, and a few weeks of real investigations to become confident. Start by rewriting queries from guides like this one with your own values.