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!

Tags (5)
1 Solution
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
``````
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?

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.

Highlighted

Legend