Splunk Search

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

demkic
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
1 Solution

gokadroid
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 game_name_sold at store_name
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

sundareshr
Legend

Please share some sample events

0 Karma

gokadroid
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 game_name_sold at store_name
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

demkic
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

gokadroid
Motivator

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...