nano SIEM
Search Commands

where

Supported in:Self-hosted

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.

The text before the first | (and | search) uses search syntax. where uses expression syntax. The two differ in three ways:

Base search / | search| where
PrecedenceNOT > OR > ANDNOT > AND > OR
field=val*Wildcard matchLiteral *. Use LIKE "val%" or STARTSWITH "val".
field=10.0.0.0/8CIDR match on IP fieldsLiteral 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

OperatorExampleNotes
=, !=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 LIKEhttp_request.url.path LIKE "%.php"% any run, _ one character; case-insensitive
CONTAINS, NOT CONTAINSprocess.cmd_line CONTAINS "mimikatz"Case-insensitive substring
STARTSWITH, ENDSWITH (and NOT …)file.name ENDSWITH ".ps1"Case-insensitive
AND / &&, OR / ||, NOTNOT (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)) > 10

Use date_diff(a, b) to express a timestamp difference in seconds.

Missing values

  • field != "x" drops rows where field is 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 > 100

Server 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_method

Outbound 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 -count

Connections 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.port

Shells 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_line

Unusually long command lines:

class_uid=1007 | where len(process.cmd_line) > 1000 | table time, device.hostname, process.name, process.cmd_line

Non-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.name

Users 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) >= 300

Network 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.ip

Notes

  • Filter in the base search when you can (class_uid=3002 status_id=2). Use where for computed fields, aggregated columns, and operators the base search doesn't have.
  • where runs 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

What's next

  • search: filter with search syntax mid-pipeline
  • regex: shorthand for a regex filter
  • eval: compute fields to filter on
  • Functions: every function

On this page