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.
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.
Try something like this
your current search | sort 2 -Occurrences by IPSLA
sort 2 was a good trick learnt!!
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