More search examples
In the last topic, you added two new fields to the online shop event data using a lookup table. If you didn't add those fields, go back and review how to use field lookups and follow the procedure to add the fields. Without them, the searches below will not return the correct results.
Back at the Flower & Gift shop, you're asked to gather information to build a report for your boss about yesterday's purchase records:
- How many page views were requested?
- What was the difference between page views and purchases made?
- What was purchased and how much was made?
- How many purchase attempts failed?
This topic uses what you learned from previous topics to write the searches to answers these questions.
The search reference manual
These examples use only a handful of the search commands and functions available to you. For complete syntax and descriptions of usage of all the search commands, see the Search reference manual.
- The complete list of search commands
- The list of functions for the eval command
- The list of functions for the stats command
Example 1 - How many page views were requested?
How many times did someone view a page on the website, yesterday?
1. Start with a search for all page views. Select the time range, Other > Yesterday:
Next you want to count the number of page views (characterized by the
2. Use the stats command:
sourcetype=access_* method=GET | stats count AS Views
Here, you use the
count() function to count the number of "GET" events in your Web access logs. This is the total number of events returned by the search, so it should match the count of retrieved events. This search essentially captures that count and saves it into a field that you can use.
Here, renaming the
count field as
Views isn't necessary, but you're going to use it again later and this helps to avoid confusion.
3. Save this search as Pageviews (Yesterday).
Example 2 - What was the difference between page views and purchases made?
From Example 1, you have the total number of views. How many visitors who viewed the site purchased an item? What is the percentage difference between views and purchases?
1. Start with the search from Example 1. Select the Other > Yesterday from the time range picker:
sourcetype=access_* method=GET | stats count AS views
2. Use stats to count the number of purchases (characterized by the
sourcetype=access_* method=GET | stats count AS Views, count(eval(action="purchase")) AS Purchases
You also use the count() function again, this time with an
eval() function, to count the number of purchase actions and rename the field as
Here, the renaming is required--the syntax for using an
eval() function with the
stats command requires that you rename the field.
Now you just need to calculate the percentage, using the total views and the purchases.
3. Use the
eval command and pipe the results to
sourcetype=access_* method=GET | stats count AS Views, count(eval(action="purchase")) as Purchases | eval percentage=round(100-(Purchases/Views*100)) | rename percentage AS "% Difference"
eval command enables you to evaluate an expression and save the result into a field. Here, you use the
round() function to round the calculated percentage of
Views to the nearest integer.
5. Save your search as "% Difference Purchases/Views".
Example 3 - What was purchased and how much was made?
This example requires the two fields,
price, added in the fields lookup example. If you didn't add them, refer to that example and follow the procedure.
Build a table to show what products were purchased yesterday, how many of each item was bought, and the calculated revenue for each product.
1. Start with a search for all purchases by the product name. Change the time range to Other > Yesterday:
sourcetype=access_* action=purchase | stats count by product_name
stats functions to include the count of products purchased, price of each product, and the total revenue made for each product.
sourcetype=access_* action=purchase | stats count, values(price), sum(price) by product_name
count() function counts the number of events. The
values() function returns the value of
price for each
product_name. And the
sum() function adds together all the values of
price for each
3. Now, you just need to rename the fields to make the table more readable:
sourcetype=access_* action=purchase | stats count AS "# Purchased", values(price) AS Price, sum(price) AS Total by product_name | eval Total="$ ".tostring(Total, "commas")
Here, 'AS' is used to rename the table headers. Also, you used the
tostring() function to convert the calculated total price values to a string and reformat them to include a dollar sign "$" and commas. (The dot '.' is a shortcut notation for string concatenation.)
5. Save your search as Purchases and Revenue (Yesterday).
Example 4 - How many purchase attempts failed?
In the previous examples you searched for successful purchases, but you also want to know the count of purchase attempts that failed!
1. Run the search for failed purchase attempts, selecting Yesterday from the time range picker:
sourcetype=access_* action=purchase status=503
(You should recognize this search from the "Start searching" topic, earlier in this tutorial.)
This search returns the events list, so let's count the number of results.
2. Use the
sourcetype=access_* action=purchase status=503 | stats count
This returns a single value:
This means that there were no failed purchases yesterday!
3. Save this search as Failed purchases (Yesterday).
Now you should be comfortable using the search language and search commands. When you're ready, proceed to the next topic to learn about reports and dashboards.
Use field lookups
About reports and dashboards
This documentation applies to the following versions of Splunk® Enterprise: 5.0, 5.0.1, 5.0.2, 5.0.3, 5.0.4, 5.0.5, 5.0.6, 5.0.7, 5.0.8, 5.0.9, 5.0.10, 5.0.11, 5.0.12, 5.0.13, 5.0.14, 5.0.15, 5.0.16, 5.0.17, 5.0.18