where

Filter search results based on conditional expressions. Use after aggregations or transformations to refine your data.

Description

The where command evaluates a boolean expression for each result and keeps only those where the expression is true. Unlike search expressions at the beginning of a query, where operates on the results of previous commands and can reference calculated fields.

This command is essential for filtering aggregated data, applying complex logic, and working with eval-generated fields.

Syntax

... | where <condition>

Required Arguments

condition
A boolean expression that evaluates to true or false. Supports field comparisons, logical operators, and functions.

Operators

Comparison Operators

Operator	Description	Example
`=`	Equal to	`status = 200`
`!=`	Not equal to	`user != "system"`
`>`	Greater than	`bytes > 1000`
`<`	Less than	`response_time < 100`
`>=`	Greater than or equal	`count >= 10`
`<=`	Less than or equal	`failures <= 5`
`LIKE`	Pattern match	`url LIKE "%.php"`
`NOT LIKE`	Negated pattern	`message NOT LIKE "%success%"`
`CONTAINS`	Substring match	`message CONTAINS "error"`
`STARTSWITH`	Prefix match	`process_name STARTSWITH "cmd"`
`ENDSWITH`	Suffix match	`file_name ENDSWITH ".exe"`
`IN`	Value in list	`status IN (400, 401, 403)`
`NOT IN`	Value not in list	`user NOT IN ("admin", "root")`

Logical Operators

Operator	Description	Example
`AND`	Both conditions true	`count > 10 AND user != "system"`
`OR`	Either condition true	`status = 500 OR status = 503`
`NOT`	Negate condition	`NOT (status = 200)`

Regex Matching

Use regex patterns with = or !=:

field=/pattern/
field!=/pattern/

Boolean Functions

You can use standalone boolean functions directly in where conditions without a comparison operator. These evaluate to true or false on their own.

Function	Description	Example
`isnull(field)`	True if field is null/missing	`\| where isnull(user)`
`isnotnull(field)`	True if field exists and is not null	`\| where isnotnull(src_ip)`
`like(field, pattern)`	True if field matches LIKE pattern	`\| where like(url, "%.php%")`
`match(field, regex)`	True if field matches regex	`\| where match(user, "^admin")`
`cidrmatch(cidr, field)`	True if IP field is in CIDR range	`\| where cidrmatch("10.0.0.0/8", src_ip)`

* | where isnull(enriched_country)
* | where isnotnull(file_hash) AND match(process_name, "(?i)powershell")
* | where cidrmatch("192.168.0.0/16", src_ip)

Examples

Filter aggregated results

* | stats count() as failures by src_ip
  | where failures > 5

Shows only IPs with more than 5 events.

Multiple conditions

* | stats count() as events, 
         dc(user) as unique_users 
  by src_ip
  | where events > 100 AND unique_users > 10

Finds IPs with high activity and user diversity.

String matching

* | stats count() by process_name
  | where process_name CONTAINS "powershell"

Filters for PowerShell-related processes.

Pattern matching with LIKE

* | stats count() by url
  | where url LIKE "%.php?%"

Finds PHP URLs with query parameters.

Regex filtering

* | stats count() by src_ip
  | where src_ip=/^192\.168\./

Filters for private IP addresses.

IN list filtering

* | stats avg(response_time) as avg_time by endpoint
  | where endpoint IN ("/api/login", "/api/auth", "/api/token")

Analyzes specific authentication endpoints.

Exclude values

* | stats sum(bytes) as total by user
  | where user NOT IN ("system", "backup", "monitoring")

Excludes service accounts from analysis.

Range filtering

* | stats avg(response_time) as avg_time by endpoint
  | where avg_time >= 500 AND avg_time <= 2000

Finds endpoints with moderate response times.

Filter calculated fields

* | stats sum(bytes_in) as inbound,
         sum(bytes_out) as outbound
  by src_ip
  | eval ratio = outbound / inbound
  | where ratio > 10

Filters based on eval-calculated ratio.

Complex boolean logic

* | stats count() as events,
         dc(dest_port) as unique_ports
  by src_ip
  | where (events > 1000 OR unique_ports > 50) 
      AND src_ip != "192.168.1.1"

Combines multiple conditions with grouping.

Time-based filtering

* | stats min(timestamp) as first_seen,
         max(timestamp) as last_seen
  by file_hash
  | where last_seen > now() - INTERVAL 1 HOUR

Finds recently active file hashes.

Null checking

* | stats count() by enriched_src_country
  | where enriched_src_country != ""

Excludes events without enrichment data.

Detect anomalies

* | stats avg(bytes) as avg_bytes,
         stdev(bytes) as stdev_bytes,
         max(bytes) as max_bytes
  by src_ip
  | where max_bytes > (avg_bytes + (3 * stdev_bytes))

Identifies statistical outliers using standard deviation.

Substring position

* | stats count() by url
  | where url CONTAINS "/admin/" OR url CONTAINS "/config/"

Finds sensitive URL paths.

Case-insensitive matching

* | stats count() by user
  | where lower(user) LIKE "%admin%"

Performs case-insensitive search using functions.

Usage Notes

Placement: Use where after commands that transform data (stats, eval, streamstats). For filtering raw events, use search expressions instead.

Performance: Filtering after aggregation is efficient since data volume is already reduced. However, filtering before aggregation (in the search expression) is even better when possible.

Field availability: where can only reference fields that exist in the pipeline at that point. You cannot reference fields that were removed by previous commands.

Null handling: Comparisons with null values typically evaluate to false. Use field != "" or field != null to explicitly check for nulls.

Type coercion: nano attempts automatic type conversion. String "100" can be compared with number 100, but be explicit when possible.

stats - Aggregate data before filtering
eval - Create calculated fields for filtering
search - Filter raw events before aggregation
dedup - Remove duplicates instead of filtering

where

On this page