KQL for Beginners: First Queries for Incident Investigation

Basic Query Structure

The first line usually specifies a Table. Each Pipe adds an Operator. For example, a Query that shows the last ten logins from the SigninLogs table:

SigninLogs
| where TimeGenerated > ago(24h)
| project TimeGenerated, UserPrincipalName, IPAddress, ResultType
| sort by TimeGenerated desc
| take 10

Read the query as a sentence: Take SigninLogs, keep only events from the last 24 hours, display four fields, sort from newest to oldest, and take ten rows. The order of steps is important for both understanding and performance. Early filtering reduces the amount of data passed to subsequent steps.

Step 1: Get to Know the Table

Before investigating, run a few lines to see the Schema and examples. The take command is excellent for learning, but it doesn't guarantee the latest events unless you sort. You can use project to narrow down the view and project-away to hide unneeded fields.

SigninLogs
| take 5

Pay attention to fields of type datetime, string, dynamic, and int. A dynamic field may contain JSON or an array and sometimes requires parse_json, mv-expand, or access to an internal property. Do not assume that ResultType is the same for every source. In the table documentation or in actual examples, check the meaning of the values.

Step 2: Filtering with where

where is the central investigation tool. You can filter by time, user, IP, result, or text. It is best to start with a narrow time range and use comparisons that match the field type.

SigninLogs
| where TimeGenerated between (datetime(2026-08-01 08:00:00) .. datetime(2026-08-01 12:00:00))
| where UserPrincipalName =~ "student@contoso.example"
| project TimeGenerated, UserPrincipalName, IPAddress, AppDisplayName, ResultType

The =~ operator compares a string case-insensitively. For partial searches, you can use contains, has, or startswith. In many cases, has is more efficient and accurate for searching for a whole term. Avoid very broad filtering with contains when you can use a structured field.

Step 3: Selecting Fields and Creating Context

project returns only the selected fields. extend creates a new field without removing existing ones. This is useful for labels, calculations, and local normalization.

SigninLogs
| where TimeGenerated > ago(24h)
| extend Outcome = iff(tostring(ResultType) == "0", "Success", "Failure")
| project TimeGenerated, UserPrincipalName, IPAddress, AppDisplayName, Outcome

When investigating, prefer field names that clarify meaning. You can use project-rename to change the display name, but do not obscure the data source. A professional ticket should indicate from which Table and field each important finding came.

Step 4: summarize — Turning Events into a Picture

summarize groups events and calculates Aggregations. You can count, find the first and last time, collect values, calculate the number of unique users, and build a Baseline. BY defines the groups.

SigninLogs
| where TimeGenerated > ago(24h)
| summarize Attempts=count(),
            FirstSeen=min(TimeGenerated),
            LastSeen=max(TimeGenerated),
            Applications=make_set(AppDisplayName, 10)
  by UserPrincipalName, IPAddress
| sort by Attempts desc

make_set is useful for displaying different values, but large lists are cumbersome and hard to read. Limit the number of items. dcount gives an approximate count of unique values and is suitable for broad analysis; distinct returns unique combinations when you need to see them.

countif and dcountif

In authentication investigations, we sometimes want to count successes and failures in the same group. countif counts only rows that meet a condition:

SigninLogs
| where TimeGenerated > ago(24h)
| summarize Failures=countif(tostring(ResultType) != "0"),
            Successes=countif(tostring(ResultType) == "0"),
            UniqueUsers=dcount(UserPrincipalName)
  by IPAddress
| where Failures >= 5
| sort by Failures desc

The result is a Lead. It does not prove that success occurred after the failures, and a shared IP can be NAT, Proxy, or VPN. You must open the raw events and build a Timeline before Classification.

Time Windows with bin

bin groups datetime into fixed segments. This allows you to see bursts of activity instead of summarizing a whole day. Choose a Span according to behavior: a Password Spray may stretch over time to avoid a threshold; a Brute Force against one account may be fast.

SigninLogs
| where TimeGenerated > ago(24h)
| summarize Failures=countif(tostring(ResultType) != "0"),
            Successes=countif(tostring(ResultType) == "0"),
            Users=dcount(UserPrincipalName)
  by IPAddress, bin(TimeGenerated, 30m)
| where Failures >= 10 and Successes >= 1
| sort by TimeGenerated desc

The TimeGenerated column in the result represents the beginning of the Bin. To see the exact order, use the result to select an IP and window, then run a second Query that displays the events by time.

let — Making a Query Readable

let allows you to name a value or a temporary table. It is useful for defining a Time range, Threshold, or a data set used multiple times.

let Lookback = 24h;
let FailureThreshold = 10;
SigninLogs
| where TimeGenerated > ago(Lookback)
| summarize Failures=countif(tostring(ResultType) != "0"),
            Successes=countif(tostring(ResultType) == "0"),
            Users=dcount(UserPrincipalName),
            UserList=make_set(UserPrincipalName, 20)
  by IPAddress, bin(TimeGenerated, 30m)
| where Failures >= FailureThreshold and Successes >= 1
| project TimeGenerated, IPAddress, Failures, Successes, Users, UserList
| order by Failures desc

Clear names and comments reduce errors. In KQL, you can add a Comment using //. A Query intended to become an Analytics rule should describe the detection purpose, assumptions, sources, version, and Owner.

Practical Exercise: Failures Followed by Successful Login

In the lab, run the summary Query on simulated data. Select a row where Failures and Successes exist. Then perform a Drill-down:

let TargetIP = "203.0.113.25";
let WindowStart = datetime(2026-08-01 09:00:00);
SigninLogs
| where TimeGenerated between (WindowStart .. WindowStart + 30m)
| where IPAddress == TargetIP
| extend Outcome = iff(tostring(ResultType) == "0", "Success", "Failure")
| project TimeGenerated, UserPrincipalName, IPAddress, AppDisplayName, Outcome, ResultDescription
| order by TimeGenerated asc

Answer questions: Did the failures affect one user or many? Does the success belong to the same user? Is the IP known as a VPN? Was MFA activated? Is there additional activity after the success? Only after gathering context can it be determined if it is a Password Spray, user error, old service, or authorized activity.

Optimization and Safe Work Practices

• Filter time early and use the most precise table.
• Filter on structured fields instead of performing text search on Raw data.
• Display only necessary fields using project.
• Avoid joins on large volumes before checking alternatives such as lookup or summarize.
• Limit make_set and make_list.
• Test a Query on a small range before expanding.
• Document assumptions, Thresholds, and the meaning of Result values.
• Do not turn a Query into Detection before checking True/False/Benign Positives.

Common Mistakes

• Copying a Query without checking the local Schema.
• Summarizing a whole day and concluding that there was a sequence of events.
• Forgetting a time range and scanning a large volume unnecessarily.
• Using a different field name between two sources as if it were uniform.
• Treating Query results as proof of an attack.
• Creating huge lists using make_set.
• Writing one long Query instead of checking each step.

Summary and CTA

Open a Log Analytics environment or a lab with simulated data and build a Query in three steps: display five rows, filter one event, and summarize by user or IP. Save each version and explain what it answers. Then proceed to the Incident Investigation Guide in Microsoft Sentinel to see how Query integrates into a real Case.

FAQ