Search for 5 maximal values per field

[omend]

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.

[omend]

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

[kristian_kolb · Accepted solution]

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.

/K

[omend]

That worked like a charm.
Thank you very much!

[kristian_kolb]

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.