where operator

Applies to: ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel

Filters a table to the subset of rows that satisfy a predicate.

The where and filter operators are equivalent

Syntax

T | where Predicate

Learn more about syntax conventions.

Parameters

Returns

Rows in T for which Predicate is true.

Performance tips

• Use simple comparisons between column names and constants. ('Constant' means constant over the table - so now() and ago() are OK, and so are scalar values assigned using a let statement.)

  For example, prefer where Timestamp >= ago(1d) to where bin(Timestamp, 1d) == ago(1d).

• Simplest terms first: If you have multiple clauses conjoined with and, put first the clauses that involve just one column. So Timestamp > ago(1d) and OpId == EventId is better than the other way around.

For more information, see the summary of available String operators and the summary of available Numerical operators.

Examples

Order comparisons by complexity

The following query returns storm records that report damaged property, are floods, and start and end in different places.

Notice that we put the comparison between two columns last, as the where operator can't use the index and forces a scan.

StormEvents
| project DamageProperty, EventType, BeginLocation, EndLocation
| where DamageProperty > 0
    and EventType == "Flood"
    and BeginLocation != EndLocation 

The following table only shows the top 10 results. To see the full output, run the query.

Check if column contains string

The following query returns the rows in which the word "cow" appears in any column.

StormEvents
| where * has "cow"