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
|
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 |
|
This search looks for events where the field IPAddress is equal to the field clientip .
|
Where |
|
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 |
|
The search command handles these expressions as a field=value pair. In this example, The host is interpreted as a string value.
|
Where |
|
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:
- Expressions within parentheses
- NOT clauses
- AND clauses
- OR clauses
Functions
You can use a wide range of functions with the where
command. See Overview of SPL2 eval functions.
See also
- where command
- where command: Examples
- Other commands
- from command: Overview
- search command: Overview and syntax
- Functions
- Overview of SPL2 eval functions
union command: Examples | where command: Examples |
This documentation applies to the following versions of Splunk® Cloud Services: current
Feedback submitted, thanks!