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!
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
Please share some sample events
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
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?
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.