I'm using this query:
|top limit=5 bytes_in,bytes_out | sort src_ip
With the goal of showing top bytes in and out by src_ip. How would I make it show src ip on the left side like:
src_ip , bytes_in bytes_out?
Here's what I'd do. I would take the sum of all bytes_in and the sum of all bytes_out per src_ip, add those together to get total_bandwidth per src_ip, sort descending by total_bandwidth, and limit to 5. That would look like this:
| stats sum(bytes_in) AS bytes_in, sum(bytes_out) AS bytes_out BY src_ip
| eval total_bandwidth=bytes_in+bytes_out
| sort 5 - total_bandwidth
Try this
your base search| stats count by src_ip,bytes_out,bytes_in | sort 5 -count | sort src_ip
Here's what I'd do. I would take the sum of all bytes_in and the sum of all bytes_out per src_ip, add those together to get total_bandwidth per src_ip, sort descending by total_bandwidth, and limit to 5. That would look like this:
| stats sum(bytes_in) AS bytes_in, sum(bytes_out) AS bytes_out BY src_ip
| eval total_bandwidth=bytes_in+bytes_out
| sort 5 - total_bandwidth
@ellotproebstel
Thanks that works well.
Why don't you put that in the answer so I can give you answer credit?
Great! Glad we got it working. I've converted it to an answer.
This makes it show the data as I want but it doesn't limit the results to 5 which is what I'm trying to do.
|top limit=5 bytes_in,bytes_out by src_ip
What is your actual goal? This query |top limit=5 bytes_in,bytes_out | sort src_ip
reads to me as: "Find the five tuples of [bytes_in,bytes_out] that occur most frequently in my data, and then sort by src_ip." So putting aside the fact that the src_ip field is not propagating through the top
command, I just want to make sure that's even matching your expectations.
I read this query |top limit=5 bytes_in,bytes_out by src_ip
as: "Find the five tuples of [bytes_in,bytes_out] that occur most frequently for each src_ip value in my data" - so I would expect a maximum of five results PER src_ip.
Do either of these describe what you actually want?
So my actual goal is to show top 5 bandwith by IP........ which I could be attacking completely wrong.
will this work?
| stats count by src_ip,bytes_out,bytes_in | sort bytes_out,bytes_in desc
And can I limit the results to 5?
Thanks for your help.
This is my full query:
index=smt_fortigate earliest=-10m latest=now | stats count by src_ip,bytes_out,bytes_in | sort bytes_out,bytes_in desc
Just to be really explicit, I'll translate this SPL to English:
| stats count by src_ip,bytes_out,bytes_in
That says: "For every tuple of [src_ip, bytes_in, bytes_out] - keep a running total of the number of times that tuple was seen."
If your data looked something like this:
src_ip=1.1.1.1 bytes_in=5 bytes_out=10
src_ip=1.1.1.1 bytes_in=50 bytes_out=100
src_ip=2.2.2.2 bytes_in=2 bytes_out=2
src_ip=2.2.2.2 bytes_in=2 bytes_out=2
src_ip=1.1.1.1 bytes_in=5 bytes_out=10
src_ip=1.1.1.1 bytes_in=5 bytes_out=10
src_ip=1.1.1.1 bytes_in=5 bytes_out=10
Here's what you'd get from that query:
src_ip=1.1.1.1 bytes_in=5 bytes_out=10 count=4
src_ip=1.1.1.1 bytes_in=50 bytes_out=100 count=1
src_ip=2.2.2.2 bytes_in=2 bytes_out=2 count=2
You could use that to calculate total bandwidth, but it would be less efficient than the method I'm suggesting in the comment below.