Splunk Search

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

jebabin
Engager

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,

0 Karma

lguinn2
Legend

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.

Get Updates on the Splunk Community!

Demo Day: Strengthen Your SOC with Splunk Enterprise Security 8.1

Today’s threat landscape is more complex than ever. Security operation centers (SOCs) are overwhelmed with ...

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...