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

Search with field lookups

Now that you have defined the prices_lookup, you can see the fields from that lookup in your search results.

Show the lookup fields in your search results

Because the prices_lookup is an automatic lookup, the fields from the lookup table will automatically appear in your search results.

  1. From the Automatic Lookups window. In the Apps menu, click Search & Reporting to return to the Search summary view.
  2. Change the time range to All time.
  3. Run the following search to locate all of the web access activity.
  4. sourcetype=access_*

  5. Scroll through the list of Interesting Fields in the Fields sidebar, and find the price field.
  6. Click price to open the summary dialog box for that field.
  7. This screen image shows the Field summary dialog box for the price field.
  8. Next to Selected, click Yes. This moves the prices field from the list of Interesting Fields to the list of Selected Fields in the Fields sidebar.
  9. Close the dialog box.
  10. Scroll through the list of Interesting Fields in the Fields sidebar, and find the productName field.
  11. Click productName to open the summary dialog box for the field.
  12. Next to Selected, click Yes.
  13. Close the dialog box.
  14. Both the price field and the productName field appear in the Selected Fields list and in the search results.
    Notice that not every event shows the price and the productName fields.
    This screen image shows the lookup fields in the Selected Fields list and in the search results. The third event in the list is highlighted. The lookup fields do not appear in every event.

Search with the new lookup fields

When you setup the automatic lookup, you specified that the productId field in your indexed events corresponds to the productId field in the prices.csv file.

When you run a search, the Splunk software uses that relationship to retrieve, or lookup, data from the prices.csv file.

This enables you to specify the productName and price fields directly in your search. The product name and price information does not exist in your indexed fields. This information exists in the lookup file, prices.csv.

Example: Display the product names and prices

You can show a list of the Buttercup Games product names and the corresponding prices by using the stats command to output a table that lists the prices by product. The search also uses the AS keyword and the rename command.

  1. Run the following search.

sourcetype=access_* |stats values(price) AS Price BY productName |rename productName AS "Product Name"

This screen image shows the search results. There are two columns in the results. The first column lists the product names. The second column lists the prices for the products.

Example: Display the VIP client purchases

In the previous section about subsearches, you created a search that returned the product IDs of the products that a VIP client purchased.

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 shows the client IP address. The second column shows that the customer made 134 purchases. The third column shows that 14 different products were purchased.

The events return the product IDs because that is the only data in your events about the product. However, now that you have defined the automatic lookup, you can return the actual product names.

  1. Make sure that the time range is set to All time.
  2. Using the same search, for the values parameter, replace the productId field with the productName field.

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(productName) AS "Product Names" BY clientip | rename clientip AS "VIP Customer"

The results are the same as in the previous search, showing the purchases by the VIP customer. However, the results are more meaningful because the product names, which are coming from the lookup table, appear instead of the more cryptic product IDs.
This screen image shows the search results. This is the same as the previous image with one important change. The last column in the previous image showed the Product IDs. In this image the last column shows the names of the products that were purchased.

Next step

This completes Part 5 of the Search Tutorial.

You have learned how to use field lookups in your searches. As you run more searches, you want to be able to save those searches, or share the searches with other people. Continue to Part 6: Creating reports and charts.

PREVIOUS
Enabling field lookups
  NEXT
Save and share your reports

This documentation applies to the following versions of Splunk® Enterprise: 7.0.0, 7.0.1, 7.0.2, 7.0.3, 7.0.4, 7.0.5, 7.0.6, 7.0.7, 7.0.8, 7.0.9, 7.0.10, 7.0.11


Comments

George and Sharkie (Peter)
You are correct, there was a problem with 2 of the product IDs in the prices.csv file. As Peter mentioned, there were missing values for the World of Cheese Tee and and Manganiello Bros. Tee. The product IDs have been corrected, so if you download a new prices.csv file, and deleted and recreate the lookup definition, you should get the correct results.

I will update the screen shots very soon.

Lstewart splunk, Splunker
April 19, 2018

@George - very good catch. The reason for the discrepancy between 14 product IDs as 12 product Names is because two of the product IDs in the lookup table are missing last numbers: MB-AG-T0 and WC-SH-T0. If you correct them to MB-AG-T01 and WC-SH-T02 respectively the numbers will match.

Now, why the actual values don't match when you print them together I would like to know as well. Seems to be some type of side effect of stats which aggregates the values and messes up the lookup. If you run the search without stats the lookup matches:

sourcetype=access_* status=200 action=purchase [search sourcetype=access_* status=200 action=purchase | top limit=1 clientip | table clientip] | table productId productName | dedup productId productName

Peter

Sharkie
April 15, 2018

in the last screenshot, there are only 12 product names, while there are 14 product IDs, can you explain that? I aslo created a new search, adding the product ID to the example, and can see the product IDs and product names don't match. Can you explain that?
my 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 "Product ID", values(productName) AS "Product Names" BY clientip | rename clientip AS "VIP Customer"

Regards,
George

Georgeysplunk
March 19, 2018

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