How to combine the stats sum() and top functionality in my search?

jebabin — Tue, 10 May 2016 17:55:04 GMT

Hello,

I'm trying to do something which looks like a basic need to me, but I'm stuck at finding a simple way to do it.
I have data of TCP session statistics with the following fields: Client IP, Server IP, Volume of data exchanged (bytes)
For each Client IP, I want to get the list of top 5 servers with which the Client has exchanged the most bytes (including the sum) and remaining servers as Others.

So if my data set is
1.2.3.4;2.3.4.5;7000
1.2.3.4;2.3.4.5;7000
1.2.3.4;2.3.4.6;5000
1.2.3.4;2.3.4.7;4000
1.2.3.4;2.3.4.8;3000
1.2.3.4;2.3.4.9;2000
1.2.3.4;2.3.4.10;1000
1.2.3.4;2.3.4.11;500

I want:
1.2.3.4;2.3.4.5;14000
1.2.3.4;2.3.4.6;5000
1.2.3.4;2.3.4.7;4000
1.2.3.4;2.3.4.8;3000
1.2.3.4;2.3.4.9;2000
1.2.3.4;OTHER;1500

With stats, I could do stats sum(bytes) by Client, Server, but I'll get the whole statistics (my result will not be limited to the top 5 + other)
With Top, I would not be able to sum bytes.

Regards,

Re: How to combine the stats sum() and top functionality in my search?

lguinn2 — Tue, 10 May 2016 18:12:37 GMT

Easy peasy

yoursearchhere
| stats sum(bytes) as totalBytes by Client, Server
| sort 10 -totalBytes

will return a list of the "top 10" Client-Server combinations based on the sum.

If you want the top 5 plus "other", try this

yoursearchhere
| stats sum(bytes) as totalBytes by Client, Server
| eventstats sum(totalBytes) as grandTotal
| sort 5 -totalBytes
| appendpipe [ stats sum(totalBytes) as top5 avg(grandTotal) as grandTotal 
     | eval Client="Other" | eval Server="Other" | eval totalBytes = grandTotal - top5  ]
| eval percent = round(totalBytes*100/grandTotal,1)
| fields - top5 grandTotal

Add the "Other" is clearly a little more tricky.

topic How to combine the stats sum() and top functionality in my search? in Splunk Search

How to combine the stats sum() and top functionality in my search?

Re: How to combine the stats sum() and top functionality in my search?