I am trying to get a list of the most common sources and destinations of blocked traffic from the previous day with some exclusions (like not sourced fromauthorized vulnerability scanners). So far I have come up with
Sources:
|tstats summariesonly=t count FROM datamodel=Network_Traffic
WHERE All_Traffic.action!="allowed" All_Traffic.src_ip!=64.39.96.0/20 earliest=-1d@d latest=@d
BY All_Traffic.src_ip
| sort limit=10 -count
But when I do the above, I get results that include an IP address that is in 64.39.96.0/20
I have learned that I can search my results to get rid of the extra source:
|tstats summariesonly=t count FROM datamodel=Network_Traffic
WHERE All_Traffic.action!="allowed" All_Traffic.src_ip!=64.39.96.0/20 earliest=-1d@d latest=@d
BY All_Traffic.src_ip
| search All_Traffic.src_ip!=64.39.96.0/20
| sort limit=10 -count
but this feels inefficient (query something that I don't need and remove it later).
Also, it does not address my next use case: top destination IPs where the source does not include 64.39.96.0/20
|tstats summariesonly=t count FROM datamodel=Network_Traffic.All_Traffic
WHERE All_Traffic.action!="allowed" All_Traffic.src_ip!=64.39.96.0/20 earliest=-1d@d latest=@d
BY All_Traffic.dest_ip
| sort limit=10 -count
is there a way to exclude 64.39.96.0/20 directly in the where clause?
Actually, natural CIDR filters work in tstats
.
Like this:
| tstats count FROM datamodel=Network_Traffic WHERE index=* AND All_Traffic.src="10.0.0.0/8"
And this:
| tstats count WHERE index=* AND host="10.0.0.0/8"
This has been in Splunk for a long time, but maybe not always. It works in all versions of 7.*
Actually, natural CIDR filters work in tstats
.
Like this:
| tstats count FROM datamodel=Network_Traffic WHERE index=* AND All_Traffic.src="10.0.0.0/8"
And this:
| tstats count WHERE index=* AND host="10.0.0.0/8"
This has been in Splunk for a long time, but maybe not always. It works in all versions of 7.*
Just a note that 7.3 adds the ability to have negated CIDR in tstats.
I knew that there was something new in 7.something. Thanks!
We've observed that the cidr match functionality in tstats (as described in answer by woodcock -- thanks!) is working for IPv4 addresses ... but not for IPv6 addresses. Any info on support for cidr match in tstats for IPv6 addresses?
I think that I have a way to do the filtering.
By counting on both source and destination, I can then search my results to remove the cidr range, and follow up with a sum on the destinations before sorting them for my top 10
|tstats summariesonly=t count FROM datamodel=Network_Traffic.All_Traffic
WHERE All_Traffic.action!="allowed" earliest=-1d@d latest=@d
BY All_Traffic.src_ip All_Traffic.dest_ip
| search All_Traffic.src_ip!=64.39.96.0/20
| chart sum(count) as Count over All_Traffic.dest_ip
| sort limit=10 -Count
Still seems inefficient (although faster than without tstats). Maybe Splunk could do something about that.
I just ran into your answer since I had the same issue, to slightly improve performance (I think - didn't measure) I did a pre-filter on the tstat
using wildcards so I give less results to search
, then narrow the results with search
(in my case I needed to filter all private IPs) as you suggested
| tstats summariesonly=T
count
from datamodel=Network_Traffic.All_Traffic
where
(
All_Traffic.dest=10.*
OR All_Traffic.dest=192.168.*
OR All_Traffic.dest=172.*
)
AND All_Traffic.action="allowed"
AND
(
All_Traffic.transport="tcp"
OR All_Traffic.transport="udp"
)
by All_Traffic.dest
| search All_Traffic.dest=10.0.0.0/8
OR All_Traffic.dest=192.168.0.0/16
OR All_Traffic.dest=172.16.0.0/12
In my case the wildcards we almost enough since it works well for /16 and /8, but I still needed to use a CIDR search for 172.16.0.0/12.
Good suggestion!
I feel a little cheesy accepting my own answer, but I don't think that something better is coming along.
You shouldn't this is the only correct answer at the moment. Tstats is not CIDR aware for WHERE clauses.