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 clientipLimit the 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 stats function.
3. One way to do this is to use the values() function:
sourcetype=access_* action=purchase clientip=10.192.1.39 | stats count, values(product_id) by clientipThis 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 stats functions, refer to the "List of stats functions" in the the Search Reference Manual.
|
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 clientipBecause the top command returns count and percent fields as well, you use the table command to keep only the clientip value.
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.
This documentation applies to the following versions of Splunk: 4.2 , 4.2.1 , 4.2.2 , 4.2.3 , 4.2.4 , 4.2.5 View the Article History for its revisions.
Comments
For Part 2, section 2:
The string:
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?", values(product_id) AS "What did he buy?", clientip AS "VIP Customer"
is incorrectly trying to name the product_id twice. The rename function is not applying correctly.
An alternative search string could be:
sourcetype=access_* action=purchase [ search sourcetype=access_* action=purchase | top clientip limit=1 | table clientip ] | stats count, values(product_id) as "What did he buy?" by clientip | rename count AS "How much did he buy?", clientip as "VIP Customer"
or
sourcetype=access_* action=purchase [search sourcetype=access_* action=purchase | top limit=1 clientip | table clientip] | stats count, values(product_id) by clientip | rename count AS "How much did he buy?", values(product_id) AS "What did he buy?", clientip AS "VIP
Monitex, thanks for catching the error! Both of your suggestions are correct and I've updated the search.
Sophy, Splunk Documentation Team