Why doesn't sort show the field I'm trying to sort by?

[summitsplunk]

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?

[somesoni2]

Try this

your base search| stats count by src_ip,bytes_out,bytes_in | sort 5 -count | sort src_ip

[elliotproebstel · Accepted solution]

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 

[summitsplunk]

@ellotproebstel

Thanks that works well.

Why don't you put that in the answer so I can give you answer credit?

[elliotproebstel]

Great! Glad we got it working. I've converted it to an answer.

[summitsplunk]

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

[elliotproebstel]

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?

[summitsplunk]

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.

[summitsplunk]

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

[elliotproebstel]

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.