What is KQL: From Beginner to Advanced

Kusto Query Language (KQL) is the backbone of querying in Microsoft platforms like Azure Data Explorer and Azure Monitor Logs. Whether you’re hunting for threats in Microsoft Sentinel, analyzing telemetry in Azure Monitor, or exploring application logs, KQL is your powerful ally.

This guide will take you from KQL basics to advanced techniques, with clear explanations and examples.

---

🧑‍🏫 What is KQL?

KQL is a read-only query language used to process large datasets stored in a tabular form. It’s designed to be simple and intuitive, especially for data exploration, filtering, and aggregation.

You’ll often use KQL in:

• Microsoft Sentinel (SIEM)
• Azure Monitor Logs
• Log Analytics Workspace
• Application Insights
• Defender for Endpoint

---

🟢 Beginner Level KQL

Let’s start with the building blocks.

📄 1. Basic Structure of a KQL Query

A simple query consists of:

TableName
| operators

✅ Example: List all entries from the ‘SecurityEvent’ table

SecurityEvent
| take 10

take 10 displays the first 10 rows.

---

🔍 2. Filtering Data: where

SecurityEvent
| where EventID == 4624

This filters for successful logon events.

Additional Filtering:

SecurityEvent
| where EventID == 4624 and AccountType == "User"
| take 5

---

📊 3. Selecting Columns: project

SecurityEvent
| where EventID == 4624
| project TimeGenerated, AccountName, Computer

This only shows the selected fields.

---

📅 4. Time Filtering: where TimeGenerated

SecurityEvent
| where TimeGenerated > ago(1h)

Other options:

• ago(30m) – past 30 minutes
• ago(7d) – past 7 days
• between(datetime(2024-01-01) .. datetime(2024-02-01))

---

🟡 Intermediate KQL

Let’s level up with summarization, joins, and string functions.

📈 5. Aggregating: summarize

SecurityEvent
| where EventID == 4624
| summarize SuccessfulLogins = count() by AccountName

Now you’re counting successful logins per user.

---

⏱ 6. Time-based Aggregation

SecurityEvent
| where EventID == 4624
| summarize Logins=count() by bin(TimeGenerated, 1h)

bin() groups events into 1-hour intervals — ideal for charting.

---

🧵 7. String Operations

contains, startswith, endswith:

SecurityEvent
| where AccountName contains "admin"

parse to extract fields:

let raw = "user=rfut;role=admin;status=success";
print raw
| parse raw with "user=" user ";role=" role ";status=" status

---

🔄 8. Joining Tables

Example: Join Heartbeat with SecurityEvent

Heartbeat
| where TimeGenerated > ago(1d)
| project Computer, HeartbeatTime=TimeGenerated
| join kind=inner (
    SecurityEvent
    | where EventID == 4624
    | project Computer, LoginTime=TimeGenerated
) on Computer

This shows when computers were active and when logons occurred.

---

🔴 Advanced KQL

Now let’s go deep with pattern matching, let, subqueries, and anomaly detection.

🧠 9. Using let for Reusability

let logons = SecurityEvent
| where EventID == 4624
| project TimeGenerated, AccountName, Computer;
logons
| summarize count() by AccountName

let defines reusable queries or variables.

---

🕵️ 10. Searching for Rare Events

SecurityEvent
| summarize Count = count() by EventID
| where Count < 10

This finds rare event types — great for threat hunting.

---

🌀 11. Using invoke for Reusable Logic

Define a function once and reuse:

let GetUserLogons = (username:string){
    SecurityEvent
    | where EventID == 4624 and AccountName == username
    | summarize Count=count() by Computer
};
GetUserLogons("rfut")

---

🔗 12. Advanced Joins with Anti and Semi

join kind=anti – “not in”

SecurityEvent
| where EventID == 4624
| project AccountName
| join kind=anti (
    IdentityInfo
    | project AccountName
) on AccountName

Finds logins from users not in the identity database.

join kind=semi – “in”

SecurityEvent
| where EventID == 4624
| project AccountName
| join kind=semi (
    IdentityInfo
    | where Department == "Finance"
    | project AccountName
) on AccountName

Filters logons to finance department users only.

---

🧬 13. Detecting Anomalies

SecurityEvent
| where EventID == 4624
| summarize Count=count() by bin(TimeGenerated, 1h)
| extend AnomalyScore = iff(Count > 100, "High", "Normal")

Basic anomaly detection — use ML functions in Azure for more.

---

📉 14. Trend Analysis with series

SecurityEvent
| where EventID == 4624
| summarize LogonCount = count() by bin(TimeGenerated, 1h)
| render timechart

Perfect for dashboards.

---

📦 15. Nested Queries (Subqueries)

let TopUsers = SecurityEvent
| where EventID == 4624
| summarize LogonCount=count() by AccountName
| top 5 by LogonCount;
TopUsers
| join kind=inner (
    SecurityEvent
    | where EventID == 4624
) on AccountName

Finds events only for the top 5 users.

---

🚀 Pro Tips

• Use intellisense in the Log Analytics workspace.
• Always use project to reduce clutter.
• Prefer let for readable, reusable, modular code.
• Bookmark this site: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/

---

🧠 Final Thoughts

KQL is more than a query language — it’s a tool for fast, flexible, scalable exploration of structured logs. With its rich syntax and powerful analytics, KQL can help you uncover insights and threats buried in gigabytes of logs within seconds.

Whether you’re a SOC analyst, a DevOps engineer, or an Azure admin — learning KQL deeply will pay off tremendously. Keep visiting us at futurecybers.com

---