Use a subsearch
This topic walks you through examples of correlating events with subsearches.
A subsearch is a search with a search pipeline as an argument. Subsearches are contained in square brackets and evaluated first. The result of the subsearch is then used as an argument to the primary, or outer, search. See "About subsearches" in the Search manual.
Example 1: Without a subsearch
Let's try to find the single most frequent shopper on the Buttercup Games online store and what this customer has purchased.
To do this, search for the customer who accessed the online shop the most.
1. Use the
sourcetype=access_* status=200 action=purchase | top limit=1 clientip
top command to return only one result for the
clientip. To see more than one "top purchasing customer", change this limit value. For more information about usage and syntax, see the "top" command's page in the Search Reference manual.
This search returns one
clientip value, which we'll use to identify our VIP customer.
2. Use the
stats command to count this VIP customer's purchases:
sourcetype=access_* status=200 action=purchase clientip=188.8.131.52 | stats count, dc(productId) by clientip
This search used the
count() function which only returns the total count of purchases for the customer. The dc() function is used to count how many different products he buys.
The drawback to this approach is that you have to run two searches each time you want to build this table. The top purchaser is not likely to be the same person at any given time range.
Example 2: With a subsearch
1. Type or copy/paste the following into the search bar.
sourcetype=access_* status=200 action=purchase [search sourcetype=access_* status=200 action=purchase | top limit=1 clientip | table clientip] | stats count, dc(productId), values(productId) by clientip
Here, the subsearch is the segment that is enclosed in square brackets, . This search,
search sourcetype=access_* status=200 action=purchase | top limit=1 clientip | table clientip is the same as Example 1 Step 1, except for the last piped command,
| table clientip
top command returns
percent fields as well, the
table command is used to keep only the
These results should match the previous result, if you run it on the same time range. But, if you change the time range, you might see different results because the top purchasing customer will be different.
2. Run the search again, and restrict the search to the time range Yesterday.
3. Rename the columns to make the information more understandable.
sourcetype=access_* status=200 action=purchase [search sourcetype=access_* status=200 action=purchase | top limit=1 clientip | table clientip] | stats count AS "Total Purchased", dc(productId) AS "Total Products", values(productId) AS "Products ID" by clientip | rename clientip AS "VIP Customer"
In the next topic, you'll learn about adding new information to your events using field lookups.
Use the search language
Use field lookups
This documentation applies to the following versions of Splunk® Enterprise: 6.1, 6.1.1, 6.1.2, 6.1.3, 6.1.4, 6.1.5, 6.1.6, 6.1.7, 6.1.8, 6.1.9, 6.1.10, 6.1.11, 6.1.12