SPL // Splunk

Using the where Command

Written by: Steve Bowser | Last Updated:

March 4, 2024

Originally Published:

May 5, 2023

What is the Splunk where Command?

The Splunk where command is one of several options used to filter search results. It uses eval-expressions that return a Boolean result (true or false), and only returns results for which the eval expression is true.

You can use the where command to:

Search a case-sensitive field
Detect when an event field is not null
Increase efficiency of dashboards via extensions of base searches
Find events with wildcards

Benefits of the Splunk where Command

An advantage of using the where command is that it will compare two different fields, which you cannot do with the search command.

For example: … | where foo=bar returns events where the contents of the field ‘foo’ is equal to the contents of the field ‘bar’.

In comparison: search foo=bar return events where the field foo contains the string ‘bar’.

See the difference? However, the where command will return results like the search command if you put quotes around the value to match: … | where foo=”bar”

How to Use the Splunk where Command?

When using the where command, there are several notes to keep in mind:

You can do a wildcard search on multiple characters (%) or just one character(_) using the “like” operator with wildcards.
The where command supports functions such as isnotnull()
The where command uses the same expressions as “eval” to evaluate field values
Field values are case-sensitive

For example: the following is NOT a case-sensitive search:

				
					sourcetype=access_combined action= "Purchase"

This will return all variations of purchase: Purchase, PURCHASE, pUrChAsE

Conversely, “where” performs a case-sensitive search – note the use of the “pipe” ( | ) symbol before the where command:

				
					sourcetype=access_combined | where action= "Purchase "

This will only return items that are in the exact form of “Purchase “

Splunk where Command Use Cases

Use Case 1: greater than / less than

In this example, we want to review the last 24 hours of Cellular platform usage for your online sales, Android vs. IOS, in 15-minute intervals.

Here’s the search:

				
					index=* sourcetype="mint:network"
| timechart span=15m count(eval(platform="android")) AS android, count(eval(platform="iOS")) AS iOS
| where android > iOS

There are 87-time samples from the past 24 hours that show Android utilization as being higher than for the iOS platform.

The following search shows the times that iOS was utilized more than Android:

				
					index=* sourcetype="mint:network"
| timechart span=15m count(eval(platform="android")) AS android, count(eval(platform="iOS")) AS iOS
| where android < iOS

There are 10-time samples from the past 24 hours that show iOS usage higher than Android’s platform.

What have we done?

When the android usage is greater than iOS (i.e. – when it equals “true”), we see that there is a pattern of higher usage. When it is less than iOS (also equals “true” in the 2nd example), we see that there are much fewer times that iOS is more utilized.

Assuming there are nearly equal numbers of potential users for each platform visiting the sales site, this reveals that we have more iOS products to attract iOS users to the mobile sales website.

Use Case 2: “isnotnull” / “isnull”

This is useful for event result sets that only contain records that are not empty (null).

For example: examine results where a description is present.

				
					index=windows | where isnotnull(Description)

Examine results where there is no description present

				
					index=windows | where isnull(Desciption)

What have we done?

We’ve saved compute resources by only showing events that are populated with a description. And we filtered out events without a description – A question to ask would be if that is an issue that will need addressing.

Use Case 3: Using wildcard characters

The where command is very useful when used with the “like” operator for wildcard searches.

The _ (underscore) finds a single character, and can be repeated.

The % (percentage) will find multiple characters.

Example: Find all events with the field “counter” that contains the word “Disk”

				
					index=perfmon counter=* | where counter like “%Disk%”

Example: Display a list of user accounts that are like admin (_dm%)

				
					sourcetype=linux_secure
| where user like “_dm%
| dedup user
| table user, host
| sort user

This will give you a table of results:

But wait…

Use Case 4: Dashboard Base Search (Show the count of transaction times from a mysql server that are greater than 5500 ms)

Dashboard Base Search use case:

				
					index=* sourcetype=mysql_perf
| stats count BY transaction_speed 
| where transaction_speed > 5500

So, why not simply use the following search:

index=* sourcetype=mysql_perf transaction_speed>5500 ???

Here’s why…

Let’s look at it from the viewpoint of a BASE search in a dashboard knowledge object. You can pull all of the transactions in the base search:

				
					index=* sourcetype=mysql_perf transaction_speed=* transaction_speed=* 
|fields host _time cpu_util transaction_speed

The xml would look something like the following:

				
					<dashboard version="1.1">
<label>Transaction Metrics</label>
<search id="baseSearch1">
    <query>
        index=* sourcetype=mysql_perf transaction_speed=* transaction_speed=* 
        |fields host _time cpu_util transaction_speed
    </query>
    <earliest>-60m@m</earliest>
    <latest>now</latest>
</search>

Then create 2 panels:

Panel 1: Above acceptable threshold transaction speed

				
					“where transaction_speed > 5500”

Panel 2: Acceptable threshold transaction speed

				
					where transaction_speed < 5501

The XML would be:

				
					<row>
    <panel>
        <title>Above acceptable transaction speed transactions</title>
        <single>
            <search base="baseSearch1">
                <query>
                    where transaction_speed &gt; 5500
                    | stats count
                </query>
            </search>
            <option name="drilldown">none</option>
            <option name="refresh.display">progressbar</option>
        </single>
    </panel>
    <panel>
        <title>Acceptable transaction speed transactions</title>
        <single>
            <search base="baseSearch1">
                <query>
                    where transaction_speed &lt; 5501
                    | stats count
                </query>
            </search>
            <option name="drilldown">none</option>
            <option name="refresh.display">progressbar</option>
        </single>
    </panel>
</row>

What does this mean?

By utilizing the | where clause, only one search is used to power different panels in the dashboard, conserving a CPU core when executing the dashboard. Different operators can be added to the “where” command by using the “AND” operator.

To further the dashboard’s utility, the user wants to monitor the transaction speed acceptability by seeing if the CPU is above 50% utilization when this happens. The additional search command for this addition panel would be:

				
					where transaction_speed &gt; 5500 AND cpu_util &gt; 50 
| stats count

An advantage of using the where command is that it will compare two different fields, which you cannot do with the search command.

For example: … | where foo=bar returns events where the contents of the field ‘foo’ is equal to the contents of the field ‘bar’.

In comparison: search foo=bar return events where the field foo contains the string ‘bar’.

See the difference? However, the where command will return results like the search command if you put quotes around the value to match: … | where foo=”bar”

Also, if they want to test this theory by seeing if acceptable transaction speeds are possible with the CPU at greater than 50% utilization, the additional search command for this panel would be:

				
					where transaction_speed &gt; 5500 AND cpu_util &lt; 50| stats count

The resulting XML is:

				
					<row>
    <panel>
        <title>Above acceptable transaction speed transactions where CPU Utilization &gt; 50%</title>
        <single>
            <search base="baseSearch1">
                <query>
                    where transaction_speed &gt; 5500 AND cpu_util &gt; 50
                    | stats count
                </query>
            </search>
            <option name="drilldown">none</option>
            <option name="refresh.display">progressbar</option>
        </single>
    </panel>
    <panel>
        <title>Acceptable transaction speed transactions where CPU Utilization &gt; 50%</title>
        <single>
            <search base="baseSearch1">
                <query>
                    where transaction_speed &lt; 5501 AND cpu_util &gt; 50
                    | stats count
                </query>
            </search>
            <option name="drilldown">none</option>
            <option name="refresh.display">progressbar</option>
        </single>
    </panel>
</row>

This results in the following dashboard:

This demonstrates that unacceptable transaction speeds might not be tied directly to CPU utilization. Other factors will warrant consideration, such as memory utilization. If you have a requirement that may relate to this situation, try it out!

So, what have we done?

We powered 4 searches with 1 base search instead of 4 separate searches.
We locked 1 CPU core versus 4 and used the “where” command to obtain results.
We expanded the basic use of the “where” command with an operator (“AND” in this case)

Conclusion

In conclusion, the Splunk “where” command can be used to enhance the efficiency of your dashboard by extending base searches. It can also help you find events with wildcards, detect when an event field is not null or is not null, and search a case-sensitive field. So why not try it out for yourself? Once you do, you’ll likely find yourself using it frequently to improve your Splunk searches.