Splunk Search
Highlighted

How to group fields using top OR use top inside of stats

Explorer

I'm playing with the Splunk tutorial data and I have this query that shows the top 5 customer per purchased product and how many the customer bought as such

sourcetype="access_combined_wcookie" action="purchase" | top clientip limit=5 by product_name

alt text

However, this is repeating the product_name 5 times. How do I group this so that product_name to only appears once?

Actually what I also want to know is how much that customer has spent on that particular product total. So far I tried

sourcetype="access_combined_wcookie" action="purchase" | stats values(price) as Price, values(clientip) by product_name

alt text

But this lists all the customers (not just the top 5). I also don't know how many purchase count per customer. I know the purchase count when I used top, but at the same time I couldn't extract the product price.

Tags (3)
0 Karma
Highlighted

Re: How to group fields using top OR use top inside of stats

Explorer

I figured it out with this query

sourcetype="access_combined_wcookie" action="purchase"
| top clientip, price by product_name limit=5
| eval pair=clientip." (".count.")"
| eval total="$".tostring(count*price, "commas")
| stats avg(price) as price, list(pair), list(total) by product_name
| fieldformat price="$".tostring(round(price), "commas")

alt text

View solution in original post