topic Re: How do I do a cidr match/not match in a tstats search? in Splunk Search

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

MonkeyK — Tue, 29 Sep 2020 11:34:55 GMT

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?

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

MonkeyK — Tue, 29 Sep 2020 11:39:25 GMT

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.

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

MonkeyK — Mon, 07 Nov 2016 18:19:46 GMT

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

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

dshpritz — Wed, 21 Dec 2016 15:46:13 GMT

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

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

isabel_ycourbe — Tue, 13 Feb 2018 12:20:00 GMT

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.

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

MonkeyK — Tue, 13 Feb 2018 21:50:58 GMT

Good suggestion!

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

woodcock — Tue, 25 Jun 2019 17:38:03 GMT

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

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

dshpritz — Tue, 25 Jun 2019 17:40:25 GMT

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

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

woodcock — Tue, 25 Jun 2019 18:48:08 GMT

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

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

wryanthomas — Thu, 10 Jun 2021 17:32:39 GMT

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?