The first command in a subsearch must be a generating command, such as search, eventcount, inputlookup, and tstats. For a list of generating commands, see Command types in the Search Reference.
One exception is the foreach command, which accepts a subsearch that does begin with a generating command.
You must have a command after the pipe and before the subsearch. In this example, the search command is used for the second and third subsearches. The first subsearch is used as a predicate to the main search.
These examples use the sample data from the Search Tutorial but should work with any format of Apache web access log. To try this example on your own Splunk instance, you must download the sample data and follow the instructions to get the tutorial data into Splunk. Use the time range All time when you run the search.
You want to find the single most frequent shopper on the Buttercup Games online store and what that shopper has purchased. Use the top command to return the most frequent shopper.
This search returns one clientip value, 87.194.216.51, which you will use to identify the VIP shopper.
You now need to run another search to determine how many different products the VIP shopper has purchased. Use the stats command to count the purchases by this VIP customer.
This search uses the count() function to return the total count of the purchases for the VIP shopper. The dc() function is the distinct_count function. Use this function to count the number of different, or unique, products that the shopper bought. The values function is used to display the distinct product IDs as a multivalue field.
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.
Let's start with our first requirement, to identify the single most frequent shopper on the Buttercup Games online store.
Copy and paste the following search into the Search bar and run the search. Make sure the time range is All time.
This search returns the clientip for the most frequent shopper, clientip=87.194.216.51. This search is almost identical to the search in Example 1 Step 1. The difference is the last piped command, | table clientip, which displays the clientip information in a table.
To find what this shopper has purchased, you run a search on the same data. You provide the result of the most frequent shopper search as one of the criteria for the purchases search.
The most frequent shopper search becomes the subsearch for the purchases search. The purchases search is referred to as the outer or primary search. Because you are searching the same data, the beginning of the outer search is identical to the beginning of the subsearch.
Because the top command returns the count and percent fields, the table command is used to keep only the clientip value.
These results should match the result of the two searches in Example 1, if you run it on the same time range. If you change the time range, you might see different results because the top purchasing customer will be different.
The performance of this subsearch depends on how many distinct IP addresses match status=200 action=purchase. If there are thousands of distinct IP addresses, the top command has to keep track of all of those addresses before the top 1 is returned, impacting performance. By default, subsearches return a maximum of 10,000 results and have a maximum runtime of 60 seconds. In large production environments, it is possible that the subsearch in this example will timeout before it completes. The best option is to rewrite the query to limit the number of events that the subsearch must process. Alternatively, you can increase the maximum results and maximum runtime parameters.
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 "Product IDs" by clientip | rename clientip AS "VIP Customer"