Splunk Search

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

Path Finder

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.

0 Karma

Splunk Employee
Splunk Employee

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.

0 Karma

Revered Legend

Try something like this

your current search | sort 2 -Occurrences by IPSLA

Explorer

sort 2 was a good trick learnt!!

0 Karma

SplunkTrust
SplunkTrust

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

State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!