Splunk Search
Highlighted

How to find the top 5 results from one field (x) per another field (y) and display results in a graph?

Explorer

Suppose I am interested in finding out the top 5 videogames bought (in the last 24 hours) per top 10 stores and would like to display this in a nice graph. I would like to be able to list out which videogames (by title) were the top 5 bought Per store. The results can very well vary for every store.

Note: When I say "top 10 stores" I mean as measured in terms of "highest revenue". So ideally I would like to display the top 10 stores on the x-axis and then have the top 5 video games displayed by title in the y axis per store.

I hope this makes sense!
I am having trouble coming up with the search query in Splunk - any help is greatly appreciated. Thank you!

0 Karma
Highlighted

Re: How to find the top 5 results from one field (x) per another field (y) and display results in a graph?

Motivator

Assuming that top 5 video games means top 5 video games sale count and you have data as follows:
index=A which has events that describe gamenamesold at storename
index=B which has events that describe store
name and its revenue(which can be summed to give total revenue)

then u can try below:

outerQuery that returns events with fields store_name, game_name_sold 
[ inner query that has events store_name and revenue(which can be summed)  to determine top 10 stores with highest revenue ]
| chart count(game_name_sold) over store_name by game_name_sold limit=5 useother=f

Example:

index=A 
[ search index=B| stats sum(store_revenue) as sum by store_name | sort -sum | head 10 | fields store_name ]
| chart count(game_name_sold) over store_name by game_name_sold limit=5 useother=false

View solution in original post

Highlighted

Re: How to find the top 5 results from one field (x) per another field (y) and display results in a graph?

Explorer

Thank you - this worked. Could you just please expand on why the command line |fields store_name was necessary? I am not understanding correctly how the fields command differs from the table command. It seems that they are displaying the same result?

0 Karma
Highlighted

Re: How to find the top 5 results from one field (x) per another field (y) and display results in a graph?

Motivator

I did fields to ensure that out of sum and storename fields we only get storename field values and not the sum values. In general table <fieldA> and fields <fieldA> would work the same way.

0 Karma
Highlighted

Re: How to find the top 5 results from one field (x) per another field (y) and display results in a graph?

Legend

Please share some sample events

0 Karma