where command: Overview, syntax, and usage

The SPL2 where command uses <predicate-expressions> to filter search results. A predicate expression, when evaluated, returns either TRUE or FALSE. The where command only returns the results that evaluate to TRUE.

The where command is identical to the WHERE clause in the from command.

Use these links to quickly navigate to the main sections in this topic:

How the SPL2 where command works

The SPL2 where command acts as a filter on your search results. The where command takes the results from your search and removes all of the results that do not match the <predicate-expression> that you specify.

In pipelines

Data that does not match the <predicate-expression> is not sent to the INTO <destination>. The data will either be dropped, or sent to the default destination. For more information:

For Edge Processor, see Partitions in the Use Edge Processor manual.

For Ingest Processor, see Partitions in the Use Ingest Processor manual.

With the where command, you must specify a <predicate-expression> that evaluates to TRUE. This can include an expression such as field=value. The following table shows a few examples:

Example Description
...| where name="maria" In this example, maria is a string literal. All strings must be enclosed in double quotation marks.
...| where ipaddress="198.51.100.1" The IP address is a string value. All strings must be enclosed in double quotation marks.
...| where 'host-name'="buttercup" If the expression references a field name that contains characters other than a-z, A-Z, 0-9, or the underscore ( _ ) character, the field name must be surrounded by single quotation marks.
...| where status in("400", "401", "403", "404") The expression can include a function. This example returns in=TRUE if one of the values in the status field matches one of the values in the list.
$pipeline = | from $source 
| where priority="high" 

| eval index = "main" 

| into $destination
 This pipeline example uses a field-value pair expression in the WHERE command.

In addition to field=value expressions, you can specify a mathematical expression, concatenation expression, comparison expression, as long as the expression evaluates to TRUE.

For more information about expressions, see Types of expressions and Predicate expressions in the SPL2 Search Manual.

Syntax

The required syntax is in bold.

where <predicate-expression>

Required arguments

predicate-expression

Syntax: <predicate-expression>

Description: An expression that, when evaluated, returns either TRUE or FALSE.

The syntax of the <predicate-expression> is checked before running the search, and an exception is returned for an invalid expression.

For more information, see Predicate expressions in the SPL2 Search Manual.

Usage

The where command is identical to the WHERE clause in the from command.

Typically you use the where command when you want to filter the result of an aggregation or a lookup.

Using wildcards

You can use wildcards to match characters in string values. With the where command, you must use the like function.

  • Use the percent ( % ) symbol as a wildcard for matching multiple characters
  • Use the underscore ( _ ) character as a wildcard to match a single character

In this example, the where command returns search results for values in the ipaddress field that start with 198. 

... | where like(ipaddress, "198.%")

See the like (<str>, <pattern>) function in the list of Comparison and Conditional eval functions.

Comparing two fields

One advantage of the where command is that you can use it to compare two different fields. You cannot do that with the search command. Here are some examples:

Command Example Description
Where 
... | where IPAddress=clientip
 This search looks for events where the field IPAddress is equal to the field clientip.
Where 
... | where clientip='ip-address'
 This search looks for events where the field clientip is equal to the field ip-address. Because the field ip-address contains a character that is not a-z, A-Z, 0-9, or and underscore ( _ ), it must be enclosed in single quotation marks.
Search 
search hostname=host
 The search command handles these expressions as a field=value pair. In this example, The host is interpreted as a string value.
Where 
... | where clientip="192.0.2.0"
 This search looks for events where the field client contains the string value 192.0.2.0.

Predicate expressions

When you specify multiple predicate expressions, you must separate each expression with a logical operator.

The where command evaluation order is different than the evaluation order used with the search command. The search command evaluates OR clauses before AND clauses.

The order in which predicate expressions are evaluated with the where command is:

  1. Expressions within parentheses
  2. NOT clauses
  3. AND clauses
  4. OR clauses

Functions

You can use a wide range of functions with the where command. See Overview of SPL2 eval functions.