KQL: Practical Starter

When you work in cybersecurity, a huge part of the job is not just collecting logs. It is knowing how to ask the right questions from those logs.

Who logged in?

Which IP address generated the traffic?

Were there multiple failed login attempts?

Which machine communicated with a suspicious domain?

This is where KQL (Kusto Query Language) becomes useful.

Kusto Query Language is the query language behind Microsoft Sentinel, Defender XDR, Azure Monitor and Azure Data Explorer. I like it because the syntax is readable, and you can start getting useful results without learning a huge amount of syntax first. It is especially relevant if you work with Microsoft’s security and cloud stack, so knowing KQL can be useful for SOC, threat hunting, detection engineering and cloud-security roles.

What KQL is

KQL is a read-only query language built for large volumes of time-stamped data such as logs, telemetry and events. You cannot modify or delete data with it, which makes it safe to practice on.

The core idea is the pipe (|). Each line takes the output of the previous line and transforms it, the same way Linux commands chain together. If you know SQL, KQL will look familiar, but the syntax and execution model are different. SQL commonly describes a query with clauses such as SELECT, FROM and WHERE; KQL usually starts with the table and then pipes the result through operators.

Where you will use it

• Microsoft Sentinel: threat hunting, detection rules, incident investigation

• Defender XDR (Advanced Hunting): endpoint, email and identity data

• Azure Monitor / Log Analytics: application and infrastructure monitoring

• Azure Data Explorer: analytics on large datasets, including IoT telemetry

Tools to practice with

• Log Analytics demo workspace: free, preloaded with sample data, runs in the browser. This is the best place to start.

• Azure Data Explorer free cluster: your own database to load data into.

• Kusto Explorer: a desktop client for heavier querying.

• VS Code Kusto extension: for saving and versioning your queries.

Syntax:

Every query starts with a table name, followed by pipes.

Also useful: ago(1d) for relative time, bin() for time buckets, let for variables, and render for charts.

Example 1: Failed Windows logons

SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4625
| summarize FailedAttempts = count() by Account, IpAddress
| where FailedAttempts > 10
| sort by FailedAttempts desc

Event 4625 is a failed logon. This groups failures by account and source IP, keeps only the noisy ones, and keeps the highest-volume sources at the top. This can be a useful starting point for investigating brute-force activity, although failed logons alone do not prove an attack.

Example 2: Sign-in failures over time

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType != "0"
| summarize Failures = count() by bin(TimeGenerated, 1h)
| render timechart

bin() groups events into one-hour buckets and render draws the chart. A sudden spike is worth investigating. It could indicate password spraying, but it could also come from expired credentials, application problems or other legitimate causes.

Example 3: Failed logins followed by a success

let failed = SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType != "0"
| summarize FailCount = count() by UserPrincipalName, IPAddress;
SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == "0"
| join kind=inner failed on UserPrincipalName, IPAddress
| project TimeGenerated, UserPrincipalName, IPAddress, FailCount

This finds user/IP combinations that had failed and successful sign-ins within the same day. That combination can be worth investigating, especially when the number of failures is unusually high.

How KQL is used in industry

• Detection rules: A query like Example 1 is saved as a Sentinel analytics rule that runs on a schedule and raises an incident when it returns results.

• Detection engineering: Analysts turn investigation logic into repeatable detections, then tune them to reduce false positives.

• Threat hunting: Analysts write ad hoc queries starting from a hypothesis, such as “which hosts made rare outbound connections last week?”

• Incident investigation: KQL is used to pull a timeline for one user or device across sign-ins, processes and network logs.

• Dashboards and workbooks: Queries feed visuals for SOC managers and audits.

• Non-security work: DevOps and IoT teams use the same language for application performance and device telemetry.

Habits that separate beginners from practitioners

1. Filter on time first. Putting where TimeGenerated > ago(...) at the top makes queries faster and cheaper.

2. Prefer has when you're searching for terms. It can take advantage of term-based indexing, while contains performs substring matching.

3. Use project early to drop columns you do not need.

4. Test with take 10 before running a query on the full table.

5. Save reusable logic as functions so your team does not rewrite the same query.

KC7: learn KQL by solving a case

If you want to learn KQL by actually investigating something, KC7 is a great place to start. It is a free cybersecurity game where you play a security analyst and investigate a breach by querying logs with KQL.

Why it works for learning

• Nothing to set up. It runs in any browser, is free for players, and lets you play as a guest. Guest progress is not saved, so create a free account if you plan to continue. kc7cyber

• You learn KQL through a reason to use it.

• The questions check your understanding. You cannot skip ahead by copying a query. You have to read the results and reach the right answer.

• The difficulty grows in steps.

• It teaches judgment, not just syntax. 

• It uses security scenarios. Phishing emails, suspicious logins and malware activity are the same kinds of questions analysts answer daily.

KQL looks big at first, but most beginner queries come down to a few repeated ideas: filter, select, aggregate, sort and visualize. Learn those five moves, practice on free browser-based platforms like KC7 (let me know more good resources if you find one). The rest comes from investigating real questions and writing down what you find.

See you next Thursday. Till then, go through this cheat sheet if you want something extra.