Splunk® Enterprise

User Manual

Download manual as PDF

Splunk version 4.x reached its End of Life on October 1, 2013. Please see the migration information.
This documentation does not apply to the most recent version of Splunk. Click here for the latest version.
Download topic as PDF

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

Limit 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.


Subsearch pt1.1 4.3.png


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


Subsearch pt1.2 4.3.png


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 clientip

This adds a column to the table that lists what he bought by product ID.


Subsearch pt1.3 4.3.png


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 clientip

Because 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"


Subsearch pt2 4.3.png


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.

PREVIOUS
Use Splunk's search language
  NEXT
Use field lookups

This documentation applies to the following versions of Splunk® Enterprise: 4.3, 4.3.1, 4.3.2, 4.3.3, 4.3.4, 4.3.5, 4.3.6, 4.3.7


Comments

One thing to note (or improve the docs) is that a subsearch can only be used where the explicit action deals with search vs. transformation of data at the search language. Example: One cannot take a search like "sourcetype=top | multikv" and place a subsearch at the end of it, as multikv isn't expecting a subsearch as an argument. one can however "pipe to append" as in "| append [search some stuff|fields some field]" or "join". it is not obvious when you can and cannot use a subsearch within a command.

Michaelwilde, Splunker
January 29, 2012

Was this documentation topic helpful?

Enter your email address, and someone from the documentation team will respond to you:

Please provide your comments here. Ask a question or make a suggestion.

You must be logged into splunk.com in order to post comments. Log in now.

Please try to keep this discussion focused on the content covered in this documentation topic. If you have a more general question about Splunk functionality or are experiencing a difficulty with Splunk, consider posting a question to Splunkbase Answers.

0 out of 1000 Characters