Splunk Search
Highlighted

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

Communicator

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:

srcip , bytesin bytes_out?

alt text

0 Karma
Highlighted

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

Communicator

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 bytesin,bytesout by src_ip

0 Karma
Highlighted

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

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 [bytesin,bytesout] that occur most frequently in my data, and then sort by srcip." So putting aside the fact that the srcip 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 [bytesin,bytesout] that occur most frequently for each srcip value in my data" - so I would expect a maximum of five results PER srcip.

Do either of these describe what you actually want?

0 Karma
Highlighted

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

Communicator

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 srcip,bytesout,bytesin | sort bytesout,bytes_in desc

And can I limit the results to 5?

Thanks for your help.

0 Karma
Highlighted

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

Communicator

This is my full query:

index=smtfortigate earliest=-10m latest=now | stats count by srcip,bytesout,bytesin | sort bytesout,bytesin desc

0 Karma
Highlighted

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

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 [srcip, bytesin, 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.

0 Karma
Highlighted

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

Here's what I'd do. I would take the sum of all bytesin and the sum of all bytesout per srcip, add those together to get totalbandwidth per srcip, sort descending by totalbandwidth, 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 

View solution in original post

Highlighted

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

Communicator

@ellotproebstel

Thanks that works well.

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

0 Karma
Highlighted

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

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

Try this

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