Use a subsearch
This documentation does not apply to the most recent version of Splunk. Click here for the latest version.
Use a subsearch
The last topic introduced search commands, the search pipeline, and drilldown actions. If you're not familiar with them, review more ways to search.
This topic walks you through another search example and shows you two approaches to getting the results that you want.
Back at the Flower & Gift shop, your boss asks you to put together a report that shows the customer who bought the most items yesterday and what he or she bought.
Part 1: Break the search down.
Let's see which customer accessed the online shop the most yesterday.
1. Use the
top command and limit the search to Yesterday:
sourcetype=access_* action=purchase | top limit=1 clientip
top command to return only one result for the
clientip. If you wanted to see more than one "top purchasing customer", change this limit value. For more information about usage and syntax, refer to the the "top" command's page in the Search Reference Manual.
This search returns one
clientip value that you will now use to complete your search.
2. Use the
stats command to count this VIP customer's purchases:
sourcetype=access_* action=purchase clientip=10.192.1.39 | stats count by clientip
This search used the
count() function which only returns the count of purchases for the clientip. You also want to know what he bought, so let's use another
3. One way to do this is to use the
sourcetype=access_* action=purchase clientip=10.192.1.39 | stats count, values(product_id) by clientip
This adds a column to the table that lists what he bought by product ID.
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.
|For more information about usage and syntax, refer to the the "stats" command's page in the Search Reference Manual. Also, for the list of other |
Part 2: Let's use a subsearch instead.
|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. Read more about "How subsearches work" in the User manual.|
1. Use a subsearch to run the searches from Part 1 inline. Type or copy/paste in:
sourcetype=access_* action=purchase [search sourcetype=access_* action=purchase | top limit=1 clientip | table clientip] | stats count, values(product_id) by clientip
top command returns
percent fields as well, you use the
table command 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. Reformat the results so that it's easier to read:
sourcetype=access_* action=purchase [search sourcetype=access_* action=purchase | top limit=1 clientip | table clientip] | stats count, values(product_id) as product_id by clientip | rename count AS "How much did he buy?", product_id AS "What did he buy?", clientip AS "VIP Customer"
While this report is perfectly acceptable, you want to make it better. For example, you don't expect your boss to know the shop items by their product ID numbers. You want to display the VIP customer's purchases by the product names, rather than the cryptic product ID. When you're ready continue on to the next topic to learn about adding more information to your events using field lookups.