Splunk® Enterprise

Search Tutorial

Download manual as PDF

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

In this section you will learn how to correlate events by using subsearches.

Think of a subsearch as a search within a search. In a search string, 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.

Let's find the single most frequent shopper on the Buttercup Games online store, and what that shopper has purchased.

The following examples show why a subsearch is useful. Example 1 shows how to find the most frequent shopper without a subsearch. Example 2 shows how to find the most frequent shopper with a subsearch.

Example 1: Search without a subsearch

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.

1. To find the shopper who accessed the online shop the most, use this search.

sourcetype=access_* status=200 action=purchase | top limit=1 clientip

The limit=1 argument specifies to return 1 value. The clientip argument specifies the field to return.
This screen image shows the results of the search. The results  appear on the Statistics tab in a table. The first columns displays the clientip. The second column displays the count. The third column displays the percent.
This search returns one clientip value, 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.

2. Use the stats command to count the purchases by this VIP customer.

sourcetype=access_* status=200 action=purchase clientip=87.194.216.51 | stats count, dc(productId), values(productId) by clientip

This screen image shows the results of the search. The results  appear on the Statistics tab in a table. The first columns displays the clientip. The second column displays the count of the number of products purchased, which is 134. The third column displays the number of unique products purchased, which is 14. The fourth column displays the IDs for the products, for example BS-AG-G09 and CU-PG-G06.
This search uses the count() function to return the total count of the purchases for the 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 argument is used to display the actual product IDs in the results.

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: Search with a subsearch

A subsearch is enclosed in square brackets [ ] and processed first when the search is parsed.

1. Copy and paste the following search into the Search bar and run the search.

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

The subsearch is the portion of the search in the square brackets.
search sourcetype=access_* status=200 action=purchase | top limit=1 clientip | table clientip
This subsearch is almost identical to the search in Example 1 Step 1. The difference is the last piped command, | table clientip.
Because the top command returns the count and percent fields, the table command is used to keep only the clientip value.
This screen image shows the results of the search. The results appear on the Statistics tab in a table. The first column displays the clientip. The second column displays the count. The third column displays the distinct count of product IDs. The fourth column displays the values of the distinct product IDs.
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.
Note: 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.

You can make the information more understandable by renaming the columns.

Column Rename
count Total Purchased
dc(productId) Total Products
values(productId) Products ID
clientip VIP Customer

You rename columns by using the AS operator on the fields in your search. If the rename that you want to use contains a space, you must enclose the rename in quotation marks.

2. To rename the fields, copy and paste the following search into the Search bar and run the search.

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"

This screen image shows the results on the Statistics tab. The first column is VIP Customer with a value of 87.194.216.51. The second column is Total Purchased with a value of 134. The third column is Total Products with a value of 14. The last column is Products ID and lists the IDs of the products purchased by the VIP customer.

3. Experiment with this search. What happens when you run the search over different time periods? What if you wanted to find the top product sold and how many people bought it?

Next step

This completes Part 4 of the Search Tutorial.

You have learned how to use fields, the Splunk search language, and subsearches to search your data. Continue to Part 5: Enriching events with lookups.

See also

About subsearches in the Search Manual
The top command in the Search Reference
The stats command in the Search Reference

PREVIOUS
Use the search language
  NEXT
Enabling field lookups

This documentation applies to the following versions of Splunk® Enterprise: 6.4.0, 6.4.1, 6.4.2, 6.4.3, 6.4.4, 6.4.5, 6.4.6, 6.4.7, 6.4.8, 6.4.9, 6.4.10, 6.4.11


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