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. See our previous blog on the search command – Basic Guide to Splunk Search.
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
This SPL query will return a time chart with a 15-minute span, showing the count of events for Android and iOS platforms. The chart will only display data points where the count of Android events is higher than the count of iOS events.
The output will have the following columns:
_time: The timestamp for each 15-minute interval.android: The count of events where the platform is “android” for each interval.iOS: The count of events where the platform is “iOS” for each interval.
The time chart will visualize the comparison between the number of Android and iOS events over time, highlighting the intervals where Android events outnumber iOS events.
Let’s a try a slight variation of the search. This version will show the times that iOS was utilized more than Android because we are going to us the less than indicator in the where clause.
index=* sourcetype="mint:network"
| timechart span=15m count(eval(platform="android")) AS android, count(eval(platform="iOS")) AS iOS
| where android < iOS
In summary, this SPL query will return a time chart with a 15-minute span, showing the count of events for Android and iOS platforms. The chart will only display data points where the count of Android events is lower than the count of iOS events.
The output will have the following columns:
_time: The timestamp for each 15-minute interval.android: The count of events where the platform is “android” for each interval.iOS: The count of events where the platform is “iOS” for each interval.
The time chart will visualize the comparison between the number of Android and iOS events over time, highlighting the intervals where iOS events outnumber Android events.
The key difference between this query and the previous one is the condition in the where clause. In the previous query, it filtered for data points where Android events were greater than iOS events, while in this query, it filters for data points where Android events are less than iOS events.
Use Case 2: “isnotnull” / “isnull”
This is useful for event result sets that only contain records that are not empty (null). For example, lets say you want to examine the results where a description is present in events from your Windows data.
index=windows | where isnotnull(Description)
This command will return all events in the windows index where the Description field is not null. This is accomplished by using the isnotnull comparison operator in the where clause.
Alternatively, we might want to do a slight variation of that and examine results where there is no description present for the event in the data returned from our search.
index=windows | where isnull(Desciption)
This command will return all events in the windows index where the Description field is not null. This is accomplished by using the isnull comparison operator in the where clause.
What have we done?
We’ve saved compute resources by only showing events that are populated with a description. We also accomplished filtering out events without a description.
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 similar to the following:
But wait, lets look at one more example…
Use Case 4: Dashboard Base Search (Show the count of transaction times from a mysql server)
Let’s look at a BASE search in a dashboard knowledge object. You can pull all of the transactions in the base search:
index=database 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=database 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
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
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 > 5500 AND cpu_util > 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.
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 > 5500 AND cpu_util < 50| stats count
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.
To access more Splunk searches, check out Atlas Search Library, which is part of the Atlas Platform. Specifically, Atlas Search Library offers a curated list of optimized searches. These searches empower Splunk users without requiring SPL knowledge. Furthermore, you can create, customize, and maintain your own search library. By doing so, you ensure your users get the most from using Splunk.
