Splunk Search

stats, eventstats,streamstats which one return max field by second field?

indeed_2000
Builder

hi
how can i show max duration per servername?  

index="my-index"       
| rex "duration\[(?<duration>\d+.\d+)"
| rex "id\[(?<id>\d+)"
| rex "servername\[(?<servername>\w+)"
| stats   max(duration) as MAXduration by servername
| table _time MAXduration id _raw

this spl not show  (_time id _raw) on table! it just show MAXduration.
I search about this and some people suggest use eventstats or streamstats.
but now i have another problem. streamstats show (_time id _raw) correctly but same MAXduration for all servername.

| streamstats max(duration) as MAXduration by servername

_time                       MAXduration     id     _raw
00:12:00.000    1.2323                 921    00:12:00.000 info duration[1.2323]id[921]servername[server1]
00:12:00.000    1.4434                 956    00:12:00.000 info duration[1.4434]id[956]servername[server1]
00:12:00.000    1.9998                  231    00:12:00.000 info duration[1.9998]id[231]servername[server2]
00:12:00.000    1.8873                  543    00:12:00.000 info duration[0.8873]id[543]servername[server2]
...


main goal is show maximum duration for each server.

excpected output:
_time                       MAXduration id     _raw
00:12:00.000    1.2323              921    00:12:00.000 info duration[1.2323]id[921]servername[server1]
00:12:00.000    1.6454              920    00:12:00.000 info duration[1.6454]id[920]servername[server2]
00:12:00.000    1.2545                821    00:12:00.000 info duration[1.2545]id[821]servername[server3]
00:12:00.000    0.1123                321    00:12:00.000 info duration[0.1123]id[321]servername[server4]


any idea?
thanks

Labels (6)
0 Karma
1 Solution

PickleRick
Ultra Champion

It's not obvious what you want to achieve. The stats command shows you exactly what you wanted - the stats. It doesn't show any additional data (i.e. the events).

But I suppose you want to show the event, for which the field value is maximal.

One possible solution is to use eventstats to add a field containing that maximal value and then filter the events to show only the one where it's equal to the actual value

| eventstats max(duration) as maxdur by servername
| where duration=maxdur

More or less.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

After using streamstats to calculate MAX, use stats to select one for each server.

index="my-index"       
| rex "duration\[(?<duration>\d+.\d+)"
| rex "id\[(?<id>\d+)"
| rex "servername\[(?<servername>\w+)"
| streamstats max(duration) as MAXduration by servername
| stats max(Maxduration) as MAXduration, values(*) as * by servername
| table _time MAXduration id _raw

 

---
If this reply helps you, an upvote would be appreciated.

PickleRick
Ultra Champion

It's not obvious what you want to achieve. The stats command shows you exactly what you wanted - the stats. It doesn't show any additional data (i.e. the events).

But I suppose you want to show the event, for which the field value is maximal.

One possible solution is to use eventstats to add a field containing that maximal value and then filter the events to show only the one where it's equal to the actual value

| eventstats max(duration) as maxdur by servername
| where duration=maxdur

More or less.

*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>