Splunk Search

Using multiple values in a sub search to filter the main search?

dennywebb
Path Finder

I have an index of data traffic across the network. I am able to select a list of the "top 10" IP addresses by IP and want to show a table of IP/PORT/IP-PORT DATA USAGE for only those top 10.

If I do the stats then try a sort+head i get the top 10 IP-PORT instead of the top 10 IP.

Example:
If I only wanted top 2 (to keep it simple) then from the data:

ip        bytes   port
-----------------------------
1.1.1.1, 1000023, 80
1.1.1.1, 43243,   443
2.2.2.2, 1000025, 3493
3.3.3.3, 1000026, 5542
4.4.4.4, 1000027, 3332

I would get results for 4.4.4.4 and 3.3.3.3.... because stats sum(bytes) by ip, port is not merging the sum of bytes for the two 1.1.1.1 entries.

1 Solution

sideview
SplunkTrust
SplunkTrust

Let me try and restate, to make sure I have it right -- You want to get a table showing IP, Port, and total bytes from that IP+Port combination, but you only want the IP's in the table to be from the 10 IP's that have the highest overall total bytes.

Easiest way is probably as follows, and doesn't need a subsearch:

<your searchterms> | stats sum(bytes) as bytes by ip port | streamstats sum(bytes) as totalBytesForThisIP by ip | sort - totalBytesForThisIP | head 10

If you want to use a subsearch you certainly can, but it's a lot more efficient to do the other version above. The subsearch solution would look like:

<your searchterms> [search <your searchterms> | stats sum(bytes) as bytes by ip | sort - bytes | fields ip] | stats sum(bytes) as bytes by ip port

View solution in original post

sideview
SplunkTrust
SplunkTrust

Let me try and restate, to make sure I have it right -- You want to get a table showing IP, Port, and total bytes from that IP+Port combination, but you only want the IP's in the table to be from the 10 IP's that have the highest overall total bytes.

Easiest way is probably as follows, and doesn't need a subsearch:

<your searchterms> | stats sum(bytes) as bytes by ip port | streamstats sum(bytes) as totalBytesForThisIP by ip | sort - totalBytesForThisIP | head 10

If you want to use a subsearch you certainly can, but it's a lot more efficient to do the other version above. The subsearch solution would look like:

<your searchterms> [search <your searchterms> | stats sum(bytes) as bytes by ip | sort - bytes | fields ip] | stats sum(bytes) as bytes by ip port

dennywebb
Path Finder

streamstats was the key i was looking for, thanks!

however good to know about the subsearch as well, i had assumed using it in that way would only work for single values, not a set of records.

0 Karma
Get Updates on the Splunk Community!

Webinar Recap | Revolutionizing IT Operations: The Transformative Power of AI and ML ...

The Transformative Power of AI and ML in Enhancing Observability   In the realm of IT operations, the ...

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...