Find average for multiple hosts

[aking76]

I'm trying to create a search that will show the average connections per host and then the current connections. The goal is to be able to see the deviation between the average and what's actually happening.

I've tried several searches to get the average per each host and it's failing miserably. Here's my last attempt-

index=network_index_name (src_ip = 10.0.0.0/8 OR src_ip=172.16.0.0/12 OR src_ip=192.168.0.0/16) AND (dest_ip=10.0.0.0/8 OR dest_ip=172.16.0.0/12 OR dest_ip=192.168.0.0/16) dest_port=* #Show internal connections only
|stats count by src_ip,dest_ip #Count the dest connections via the source ip
|stats avg(count) as Average_connections #Create an average

My end goal is to get something like this-

Source_IP Destination_IP Current_Connections Average_Connections

[rbechtold · Accepted solution]

Hello Aking!

This sounds like an excellent opportunity to make use of eventstats!

eventstats works very similarly to the stats command, except that it is a dataset processing command instead of a transforming command. Basically what this means is that you won't lose any fields when running the command (dest_ip, src_ip, and count will be preserved)

Give this a try for me:

index=network_index_name (src_ip = 10.0.0.0/8 OR src_ip=172.16.0.0/12 OR src_ip=192.168.0.0/16) AND (dest_ip=10.0.0.0/8 OR dest_ip=172.16.0.0/12 OR dest_ip=192.168.0.0/16) dest_port=*
| stats count by src_ip, dest_ip
| eventstats avg(count) as Average_Connections
| table src_ip dest_ip count Average_Connections
| rename src_ip AS "Source_IP", dest_ip AS "Destination_IP", count AS "Current_Connections"

If you would like to know more information about the eventstats command, check the Splunk documenation here: https://docs.splunk.com/Documentation/Splunk/7.3.0/SearchReference/Eventstats

Also, Splunk documentation on command types can be found here: https://docs.splunk.com/Documentation/Splunk/7.3.0/SearchReference/Commandsbytype

Let me know if you have any other questions or the solution doesn't work for you and I'll try to help!

[aking76]

Thank you soooo much! I was searching and searching, trying to figure this out and you solved it in a blink of an eye! You rock! Thank you!

[aking76]

Oh so I do have one follow up question. It looks like it's pulling an average for all the connections and putting them in the last column.
If you have a quick thought on this, it would be much appreciated, if not, no worries, I'll continue searching.

[rbechtold]

Apologies for the delay, I would love to help!

Let's first start by identifying what we want out of the average connections field:
Are we trying to find an average amount of connections by the source ip?
Are we trying to find an average amount of connections by the destination ip?
Are we trying to find the average amount of connections by both source ip and destination ip over a certain time frame and comparing it to our current time frame? (i.e today's connections vs last weeks connections)

If it's one of the first two questions, that should be relatively simple.

For average by source ip:

 index=network_index_name (src_ip = 10.0.0.0/8 OR src_ip=172.16.0.0/12 OR src_ip=192.168.0.0/16) AND (dest_ip=10.0.0.0/8 OR dest_ip=172.16.0.0/12 OR dest_ip=192.168.0.0/16) dest_port=*
| stats count by src_ip, dest_ip 
| eventstats avg(count) as Average_Connections by src_ip 
| table src_ip dest_ip count Average_Connections 
| rename src_ip AS "Source_IP", dest_ip AS "Destination_IP", count AS "Current_Connections"

For average by destination ip:

 index=network_index_name (src_ip = 10.0.0.0/8 OR src_ip=172.16.0.0/12 OR src_ip=192.168.0.0/16) AND (dest_ip=10.0.0.0/8 OR dest_ip=172.16.0.0/12 OR dest_ip=192.168.0.0/16) dest_port=*
| stats count by src_ip, dest_ip 
| eventstats avg(count) as Average_Connections by dest_ip
| table src_ip dest_ip count Average_Connections 
| rename src_ip AS "Source_IP", dest_ip AS "Destination_IP", count AS "Current_Connections"

If it's the third question, it might be a bit more complicated and time consuming depending on both the time range, and the unique source ip/destination ip pairs we're working with.

In any event, I hope this helps. let me know how this works for you!