where
Filter rows with a boolean expression: comparisons, regex, CIDR ranges, lists, functions and arithmetic, including on fields computed by eval and stats.
Use where to keep rows for which your expression is true. You can filter events, computed fields, or aggregation results.
Syntax
... | where <expression>where works on whatever the previous command produced: raw events, fields added by eval or rename, or the output columns of stats.
How it differs from the base search
The text before the first | (and | search) uses search syntax. where uses expression syntax. The two differ in three ways:
Base search / | search | | where | |
|---|---|---|
| Precedence | NOT > OR > AND | NOT > AND > OR |
field=val* | Wildcard match | Literal *. Use LIKE "val%" or STARTSWITH "val". |
field=10.0.0.0/8 | CIDR match on IP fields | Literal string compare. Use IN CIDR("10.0.0.0/8"). |
So a b OR c in a base search means a AND (b OR c), while where a AND b OR c means (a AND b) OR c. Use parentheses when you mix AND and OR. See Syntax for the search side.
Operators
| Operator | Example | Notes |
|---|---|---|
=, != | user.name="admin" | String compares are case-insensitive. |
>, <, >=, <= | http_response.code >= 500 | |
field="*" | actor.user.name="*" | True when the field is non-null and non-empty |
IN (…), NOT IN (…) | http_response.code IN (401, 403) | |
IN CIDR(…), NOT IN CIDR(…) | dst_endpoint.ip IN CIDR("10.0.0.0/8", "192.168.0.0/16") | Non-IP values never match |
=~, !~ | actor.user.name =~ "^svc_" | Regex, unanchored, case-insensitive |
field=/re/, field!=/re/ | process.name=/^(cmd|powershell)\.exe$/ | Same as =~ / !~ |
LIKE, NOT LIKE | http_request.url.path LIKE "%.php" | % any run, _ one character; case-insensitive |
CONTAINS, NOT CONTAINS | process.cmd_line CONTAINS "mimikatz" | Case-insensitive substring |
STARTSWITH, ENDSWITH (and NOT …) | file.name ENDSWITH ".ps1" | Case-insensitive |
AND / &&, OR / ||, NOT | NOT (status_id=1 OR status_id=99) | |
IN [ <subsearch> ] | src_endpoint.ip IN [search class_uid=3002 status_id=2 | return src_endpoint.ip] | The subsearch may only be a search with an optional return. |
Functions
Any scalar function can appear in a comparison, for example len(process.cmd_line) > 500 or extract_root_domain(http_request.url.hostname)="example.com". These also work on their own as true/false tests:
isnull(f), isnotnull(f), like(f, pattern), match(f, re), cidr_match(ip, cidr, …), is_private_ip(f), is_public_ip(f), has(array, value), in(f, v1, v2, …), isnum(f), isint(f), isstr(f), isbool(f), searchmatch("search terms")
match() is case-sensitive unless the pattern starts with (?i).
Use arithmetic in comparisons
Use parentheses to make arithmetic grouping explicit:
class_uid=4001 | where (traffic.bytes_out / (traffic.bytes_in + 1)) > 10Use date_diff(a, b) to express a timestamp difference in seconds.
Missing values
field != "x"drops rows wherefieldis null.NOT field="x"keeps them.
Use isnull() / isnotnull() when you need to be explicit.
Examples
Accounts with more than 100 failed logons:
class_uid=3002 status_id=2 | stats count by user.name | where count > 100Server errors, or client errors on POST:
class_uid=4002 | where http_response.code >= 500 OR (http_response.code >= 400 AND http_request.http_method="POST") | stats count by http_response.code, http_request.http_methodOutbound connections from internal hosts to the internet:
class_uid=4001 | where is_private_ip(src_endpoint.ip) AND NOT is_private_ip(dst_endpoint.ip) | stats count by dst_endpoint.ip | sort -countConnections into RFC 1918 ranges:
class_uid=4001 | where dst_endpoint.ip IN CIDR("10.0.0.0/8", "172.16.0.0/12", "192.168.0.0/16") | stats count by dst_endpoint.portShells launched by anything other than Explorer:
class_uid=1007 | where process.name =~ "^(cmd|powershell|pwsh)\.exe$" AND actor.process.name !~ "^explorer\.exe$" | table time, device.hostname, actor.process.name, process.cmd_lineUnusually long command lines:
class_uid=1007 | where len(process.cmd_line) > 1000 | table time, device.hostname, process.name, process.cmd_lineNon-service accounts authenticating:
class_uid=3002 | where user.name NOT LIKE "%$" AND user.name NOT IN ("SYSTEM", "LOCAL SERVICE", "NETWORK SERVICE") | stats count by user.nameUsers active for more than five minutes:
class_uid=3002 | stats earliest(time) as first_seen, latest(time) as last_seen by user.name | where date_diff(last_seen, first_seen) >= 300Network activity from hosts that also failed a logon:
class_uid=4001 | where src_endpoint.ip IN [search class_uid=3002 status_id=2 | return src_endpoint.ip] | stats count by src_endpoint.ipNotes
- Filter in the base search when you can (
class_uid=3002 status_id=2). Usewherefor computed fields, aggregated columns, and operators the base search doesn't have. whereruns after a nano-side stage (lookup,prevalence,inputlookup,resolve_identity,ai), but only with the functions marked "yes" on the Functions page.
More references
- Syntax: search terms and precedence