2.2 Core Operators

Core Operators

These are the operators you will use in every investigation query. Master these six and you can answer the majority of security questions: where filters rows, project selects columns, extend adds calculated columns, summarize aggregates data, sort orders results, and take limits output.

where — Filter Rows

where keeps rows that match a condition. It is the operator you will use more than any other.

1
2
3
4

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| where AppDisplayName == "Microsoft Teams"

This returns successful sign-ins to Microsoft Teams in the last seven days. Multiple where clauses act as logical AND — every condition must be true. You can combine conditions in a single where with and and or:

1
2
3

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType != 0 and (AppDisplayName == "Exchange Online" or AppDisplayName == "SharePoint Online")

Comparison operators:

The in operator is particularly useful for filtering on known values:

1
2
3

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType in (50126, 50053, 50074)

This returns sign-ins that failed with specific error codes: invalid credentials (50126), account locked (50053), or MFA required but not completed (50074).

has vs contains — The Performance Difference

This is the most important distinction in KQL string matching. Getting it wrong costs you minutes per query on large tables.

1
2

EmailEvents
| where SenderFromAddress has "paypal"

1
2

EmailEvents
| where SenderFromAddress contains "paypal"

Both return similar results in most cases, but has runs faster because it checks the term index for “paypal” rather than scanning every character of every email address.

When you must use contains: when your search term is part of a larger word and not a standalone term. For example, searching for “admin” inside “sysadmin” requires contains because “admin” is a substring of the term “sysadmin”, not a separate term.

The full family of string operators:

Case-sensitive variants (the _cs suffix) are faster than their case-insensitive counterparts because the engine can use stricter index matching. Use them when you know the exact casing — for example, when matching known IOC hashes.

project — Select and Rename Columns

project controls which columns appear in your output. It reduces the width of your result set, making it easier to read and reducing the data the engine processes.

1
2
3
4

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType != 0
| project TimeGenerated, UserPrincipalName, IPAddress, ResultType, AppDisplayName

You can rename columns in the same step:

1
2
3

SigninLogs
| where TimeGenerated > ago(1d)
| project Timestamp = TimeGenerated, User = UserPrincipalName, IP = IPAddress, ErrorCode = ResultType

extend — Add Calculated Columns

extend adds a new column while keeping all existing columns. Use it for calculations, extractions, and transformations.

1
2
3
4
5

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == 0
| extend HourOfDay = hourofday(TimeGenerated)
| extend Domain = tostring(split(UserPrincipalName, "@")[1])

This adds two columns: HourOfDay (the hour the sign-in occurred) and Domain (extracted from the user’s email). The original columns remain — extend never removes data.

Common extend patterns for security:

1
2
3
4
5

| extend TimeSinceEvent = datetime_diff('minute', now(), TimeGenerated)
| extend Country = tostring(LocationDetails.countryOrRegion)
| extend City = tostring(LocationDetails.city)
| extend Browser = tostring(DeviceDetail.browser)
| extend OS = tostring(DeviceDetail.operatingSystem)

summarize — Aggregate Data

summarize groups rows and computes aggregations. This is how you count things, find unique values, and identify patterns.

1
2
3
4
5

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType != 0
| summarize FailedAttempts = count() by UserPrincipalName
| sort by FailedAttempts desc

Key aggregation functions:

Multiple aggregations in one query:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SigninLogs
| where TimeGenerated > ago(1d)
| summarize
    TotalSignins = count(),
    FailedSignins = countif(ResultType != 0),
    SuccessfulSignins = countif(ResultType == 0),
    UniqueIPs = dcount(IPAddress),
    Countries = make_set(tostring(LocationDetails.countryOrRegion))
    by UserPrincipalName
| where FailedSignins > 10
| sort by FailedSignins desc

This gives you a complete sign-in profile per user in a single query: total sign-ins, failures, successes, how many distinct IPs they used, and which countries they signed in from.

sort and take — Order and Limit

sort by orders results. take limits the number of rows returned.

1
2
3
4
5

SecurityAlert
| where TimeGenerated > ago(7d)
| summarize AlertCount = count() by AlertName
| sort by AlertCount desc
| take 10

This returns the top 10 most frequent alerts in the last week — a quick view of what is generating the most noise in your environment.

1
2
3
4
5
6
7
8

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType != 0
| summarize
    FailedAttempts = count(),
    TargetedUsers = dcount(UserPrincipalName)
    by IPAddress
| top 5 by FailedAttempts desc