topic Re: Search for 5 maximal values per field in Splunk Search

Search for 5 maximal values per field

omend — Mon, 24 Jun 2013 06:59:26 GMT

Hi All,

My data in Splunk contains information about sales from different store branches. More specifically, I have the data in the format - Date, Branch_ID, Sales (Number), that describes for each branch, its sales stats overview for each day (one number).

I would like to run a search that will calculate for each branch, the average of the top 5 best sales day, across the last month. More specifically, I would like to be able to reach results in the format Branch_ID Avg(Max(5)).

For example, if branch_id #1 has the values 0,100,50,100,25,200,75,0,150,25, I would like the query to output #1 (200+150+100+100+75)/5=125.

I tried the stats max, but it only takes the first max value. I tried to sort with limit count, but I couldn't apply the count for each branch.

Any ideas would be appreciated.

Thanks.
Ori.

Re: Search for 5 maximal values per field

kristian_kolb — Mon, 24 Jun 2013 07:27:00 GMT

Please post a few sample events. Also, describe how the sales values are extracted as fields (if they are);
- a single event per branch with separate fields, or
- a single event per branch with a multivalued field, or
- separate events with a single sales field in each.

Re: Search for 5 maximal values per field

kristian_kolb — Mon, 24 Jun 2013 09:11:31 GMT

Assuming the last scenario, where each sale would be in a separate event;

... | sort - sales_field | dedup 5 branch_id | stats avg(sales_field) as "Avg Top5 Sales" by branch_id

This seems somewhat inefficient, but right now I can't think of anything better.

Re: Search for 5 maximal values per field

omend — Mon, 24 Jun 2013 09:30:18 GMT

Each event specifies a single branch sales on a single day.

Re: Search for 5 maximal values per field

omend — Mon, 24 Jun 2013 09:31:01 GMT

That worked like a charm.
Thank you very much!