How to edit my search to obtain the top N values for each source?

[kiril123]

I have indexed about 100 files in Splunk. Each file contains two columns - unix timestamp and a latency value as below:

1483226100,3 
1483225800,1 
1483225500,1 
1483225200,2 
1483224900,2 
1483224600,1 
1483224300,1 

I want to calculate the following values for each field: unique latency values, occurrences, percentage, time_of_first_occurance, time_of_last_occurance.

unique latency values, occurrences,percentage, time_of_first_occurance, time_of_last_occurance 
1 4 57.15% 1483225800 1483224300 
2 2 28.5% 1483225200 1483224900 
3 1 0.14% 1483226100 1483226100 

I am able to produce these stats by running the following search:

index="ipsla_rtt" | eval source_list = split (source,"/") | eval IPSLA = mvindex(source_list,5) | stats count(Value_IPSLA) as rtt_values, min(Value_Time) as First_occurence, max(Value_Time) as Last_occurence by IPSLA,Value_IPSLA | eventstats sum(rtt_values) as total | eval Percent = rtt_values/total*100 | fieldformat Percent=round(Percent, 2) | eval First_occurence=strftime(First_occurence, "%d-%m-%Y") | eval Last_occurence=strftime(Last_occurence, "%d-%m-%Y") | rename rtt_values AS "Occurrences" | rename Value_IPSLA AS "Unique RTT Values Removing Duplicates" | fields - total 

As a next step, I also want to display only top 2 rows based on the number of occurrences for every file. If I use the following command, it is only going to display top 2 occurrences across all the files:

| sort - Occurrences | head 2 

What command should i use to display top occurrences for each file? I've tried top but in that case time_of_first_occurance, time_of_last_occurance fields are not displayed.

[aaraneta_splunk]

Hi @kiril123 - Did one of the answers below help provide a solution your question? If yes, please click “Accept” below the best answer to resolve this post. If no, please leave a comment with more feedback. Thanks.

[somesoni2]

Try something like this

your current search | sort 2 -Occurrences by IPSLA

[kabSplunk]

sort 2 was a good trick learnt!!

[javiergn]

You can use streamstats to calculate the top 2 based on the fields you want and then filter by that.

For example:

your base search
| sort -limit=0 - Occurrences, source
| streamstats count by Occurrences, source
| where count <= 2

Is that what you are looking for?

Thanks,
J