I have the following table of data generated by a search:
category a category b count
A E 1
A F 10
B E 2
B E 12
B F 3
B F 5
A E 10
A E 2
A E 15
I want to find the average of the top 3 values (or any other arbitrary number of values) for each grouping.
So for (A, E) the values would be:
1, 2, 10, 15 -> top 3 values are 2,10,15 -> average of 9
The resulting table should look something like this:
E F
A 9 10
B 7 4
How should I do this?
GIve this a try. You would need to update the | dedup N...
to configure how many top values you want to keep.
your current search
| sort 0 category_a category_b -count
| dedup 3 category_a category_b
|chart avg(count) by category_a category_b
I would start with a streamstats.
...base search|sort 0 - count category_a category_b|streamstats count as top by category_a category_b|where top<4|chart avg(count) by category_a category_b
http://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Streamstats
with streamstats and sort, you should be able to filter the top three of each category set and average them with chart.