How do I do a cidr match/not match in a tstats search?

[MonkeyK]

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?

[woodcock · Accepted solution]

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.*

[dshpritz]

Just a note that 7.3 adds the ability to have negated CIDR in tstats.

[woodcock]

I knew that there was something new in 7.something. Thanks!

[wryanthomas]

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?

[MonkeyK]

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.

[isabel_ycourbe]

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.

[MonkeyK]

Good suggestion!

[MonkeyK]

I feel a little cheesy accepting my own answer, but I don't think that something better is coming along.

[dshpritz]

You shouldn't this is the only correct answer at the moment. Tstats is not CIDR aware for WHERE clauses.