Smooth operator | Searching for multiple field values

Searching for different values in the same field has been made easier. Thank you Splunk!

For example, suppose in the "error_code" field that you want to locate only the codes 400, 402, 404, and 406. 

It is really tedious to have to type field-value pair after field-value pair just to search for a list of values in the same field. But that's exactly what you had to do before version 6.6.0. You had to specify each field-value pair as a separate OR condition. 

    ...error_code=400 OR error_code=402 OR error_code=404 OR error_code=406...

Using IN with the search command

One of the best improvements made to the search command is the IN operator.  With the IN operator, you can specify the field and a list of values. For example:

    ... error_code IN (400, 402, 404, 406) | ...

Because the search command is implied at the beginning of a search string, all you need to specify is the field name and a list of values. The syntax is simple: 

    field IN (value1, value2, ...)

Note: The IN operator must be in uppercase.

You can also use a wildcard in the value list to search for similar values. For example: 

    ... error_code IN (40*) | ...

This search looks at the error_code field in your events and returns any event with a code that begins with 40.

How cool is that !

With the search command this capability is referred to as the "IN operator". With the eval and where commands, it is implemented as the "IN function".

Using IN with the eval and where commands

To use IN with the eval and where commands, you must use IN as an eval function. The Splunk documentation calls it the "in function".

And the syntax and usage are slightly different than with the search command.

• The IN function returns TRUE if one of the values in the list matches a value in the field you specify.
• String values must be enclosed in quotation marks. 
• You cannot specify wildcard characters to search for similar values, such as HTTP error codes or CIDR IP address ranges.

Here are the supported syntax options:

    ...| eval new_field=if(IN(field,"value1","value2", ...), "value_if_true","value_if_false")
    ...| where field IN("value1","value2", ...)
    ...| where IN(field,"value1","value2", ...)

Note: The IN function, unlike the IN operator, can be specified in upper or lowercase. The IN function is shown in this blog in uppercase in the syntax and examples for clarity.

Let's start with the where command because it is fairly straight-forward.

The following example uses the where command to return IN=TRUE if one of the values in the status field matches one of the values in the list. The values in the status field are HTTP status codes. Because the codes are string values (not numeric values), you must enclose each value in quotation marks.  

    ... | where status IN("400", "401", "403", "404", "406")

You could also specify this example as:

    ... | where IN(status,"400", "401", "403", "404", "406")

Using the IN function with the eval command is different than using IN with the where command. The eval command cannot accept Boolean values, you must use the IN function inside another function that can process the Boolean values returned by the IN function.

Let's go through an example where you can use the IN function as the first parameter for the IF function.  We'll use the access.log file that is included with the Search Tutorial data.

In the following example, the IN function is used with the IF function to evaluate the action field. Then the stats command performs a calculation. 

    sourcetype=access_combined_wcookie 
    | eval activity=if(IN(action, "addtocart","purchase"),"Purchase Related","Other") 
    | stats count by activity

Here is what this search is doing:

• The eval command creates a new field called activity.
• If the action field in an event contains the value addtocart or purchase, the value Purchase Related is placed in the activity field.
• If the action field in an event contains any other value, the value Other is placed in the activity field.
• The stats command counts the Purchase Related and Other values in the activity field. 

The results appear on the Statistics tab and show the counts for how many events have Purchase Related activity and how many have Other types of activity. 

This results table is great. You can also show the results in a chart. Switch to the Visualization tab and change the chart type to Pie Chart.

You can save this search as a dashboard panel or a report.

Resources

See the following Splunk documentation for more information:

• Search Tutorial data files
• Search command
• IN function and IF function (as part of the set of eval functions) 
• Eval command
• Where command