Splunk Search

Find X largest values of field by another field

bowesmana
SplunkTrust
SplunkTrust

I have a set of events that are racing results. One result/month for 10 months of the year. Points are awarded to runners for each race during the year. In order to find the annual winner, the monthly points are added and the winner is the person with the most points. Easy...

index="bbr" sourcetype="bbr*" source="BBR*csv" Event=10km Year=2015 
| stats sum(Points) as Total count as Runs count(eval(Place==1)) as Wins by Name, Gender 
| sort - Total

However, the catch is that only the top 8 races for a runner count to the total, so if the runner runs 9 or 10 runs, then the lowest two points are not counted, so the sum(Points) above can't be used.

I tried to do a sort by points for all runners and the idea was to then take only the first 8 records of each Name field, but I can't make this work.

Any idea how to do this?

Tags (2)
0 Karma
1 Solution

javiergn
Super Champion

You could try something like this by using streamstats:

index="bbr" sourcetype="bbr*" source="BBR*csv" Event=10km Year=2015
| sort limit=0 Name, Gender, - Points
| streamstats count as id by Name, Gender, Points
| where id <= 8
| stats sum(Points) as Total count as Runs count(eval(Place==1)) as Wins by Name, Gender 
| sort - Total

View solution in original post

0 Karma

javiergn
Super Champion

You could try something like this by using streamstats:

index="bbr" sourcetype="bbr*" source="BBR*csv" Event=10km Year=2015
| sort limit=0 Name, Gender, - Points
| streamstats count as id by Name, Gender, Points
| where id <= 8
| stats sum(Points) as Total count as Runs count(eval(Place==1)) as Wins by Name, Gender 
| sort - Total
0 Karma

bowesmana
SplunkTrust
SplunkTrust

Thanks javiergn, that almost worked, the key change needed was to the initial streamstats as it didn't need to group by Gender or Points as id would count the number of different points values, hence never get to 8.

In order to keep the total Runs and Wins correct in case they had 9 or 10, I put an eventstats in before the where clause, so it made the final search

index="bbr" sourcetype="bbr*" source="BBR*csv" Event=10km Year=2015
| eventstats count as Runs count(eval(Place==1)) as Wins by Name
| sort limit=0 Name, Gender, - Points
| streamstats count as id by Name
| where id <= 8
| stats sum(Points) as Total values(Runs) as Runs values(Wins) as Wins by Name, Gender 
| sort - Total 

Cheers

0 Karma
Get Updates on the Splunk Community!

Announcing Scheduled Export GA for Dashboard Studio

We're excited to announce the general availability of Scheduled Export for Dashboard Studio. Starting in ...

Extending Observability Content to Splunk Cloud

Watch Now!   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to leverage ...

More Control Over Your Monitoring Costs with Archived Metrics GA in US-AWS!

What if there was a way you could keep all the metrics data you need while saving on storage costs?This is now ...