where command: Examples

The following are examples for using the SPL2 where command. To learn more about the where command, see How the SPL2 where command works.

The where command expects a predicate expression. See Predicate expressions in the SPL2 Search Manual.

In most cases you can use the WHERE clause in the from command instead of using the where command separately.

1. Specify wildcards

You can only specify a wildcard with the where command by using the like function. The percent ( % ) symbol is the wildcard you must use with the like function.

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

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

The like function supports several syntaxes, see Comparison and Conditional functions.

2. Match IP addresses or a subnet using the where command

Return events that match the IP or is in the specified subnet. This example uses both the like function and the cidrmatch function.

...| where like(src, "10.9.165.%") OR cidrmatch("10.9.165.0/25", dst)

3. Specify a calculation in the where command expression

Return events with a speed is greater than 100.

... | where distance/time > 100

4. Pipeline examples

These examples show how to use the where command in a pipeline.

Filtering data in a pipeline

You can use the where command to filter data in a pipeline by specifying a field-value pair.

If the data values that you want to filter aren't stored in event fields, you can extract those values into fields by using the rex command.

The following example shows how to extract the type of payment method, either Credit Card or Game Card, and place those values into a field named card_type. Then the pipeline filters to return only the events where the card_type is Credit Card. The pipeline then replaces the credit card number with the string "<redacted>".

$pipeline = | from $source
| rex field=_raw /(?P<card_type>(Credit Card|Game Card))/
| where card_type = "Credit Card"
| eval _raw=replace(_raw, /[1-5][0-9]{15}/i, "<redacted>")
| into $destination

Filter incoming pipeline events based on a field-value pair

The following example filters the incoming pipeline events using a specific field value:

$pipeline = | from $source 
| where priority = "high" 
| eval index = "main" 
| into $destination

Apply multiple filters to data in a pipeline

Suppose you want to filter data in Linux audit logs so that only audit logs that indicate failed login attempts remain. You must first extract the record types and result values from the logs with the rex command. Then filter extracted fields using the where command. Use the fields command to drop the RecordType and Result fields from the events before the data is sent to the destination.

$pipeline = | from $source 
| rex field=_raw /type=(?P<RecordType>[A-Z_]+).*res=(?P<Result>\w+)/
| where RecordType = "USER_LOGIN"
| where Result = "failed"
| fields - RecordType, Result
| into $destination

Documentation