tstats WHERE clause filtering on CIDR only partially filters results

[stroud_bc]

I have a dashboard which displays some simple "top 15" visualizations based on outbound network traffic. The base search just pulls some basic stats from All_Traffic, filtering in the tstats ... whereclause to include only outbound traffic. I define "outbound" to be any traffic for which the source is an internal IP and the destination is NOT an internal IP.

This worked up until we upgraded from to Splunk 7.3.1 to 8.0.1, but now the clause filtering out All_Traffic.dest_ip!=10.0.0.0/8, etc. are completely ignored (running the same search with and without the condition return the same results without the desired filtering)

Here's the original base search:

| tstats count(All_Traffic.dest_ip) AS ip_count count(All_Traffic.dest_port) AS port_count from datamodel=Network_Traffic where (All_Traffic.src_ip=10.0.0.0/8 OR All_Traffic.src_ip=192.168.0.0/16 OR All_Traffic.src_ip=172.16.0.0/12) AND NOT (All_Traffic.dest_ip=10.0.0.0/8 OR All_Traffic.dest_ip=192.168.0.0/16 OR All_Traffic.dest_ip=172.16.0.0/12) by All_Traffic.dest_ip, All_Traffic.dest_port 
| rename All_Traffic.* AS * 

A simpler version with only one exclusion in the tstats ... where clause which also does not work:

| tstats count(All_Traffic.dest_ip) AS ip_count count(All_Traffic.dest_port) AS port_count from datamodel=Network_Traffic where All_Traffic.dest_ip!=10.0.0.0/8 by All_Traffic.dest_ip, All_Traffic.dest_port 
| rename All_Traffic.* AS *

This seems very similar (but not identical) to the problem described in the release notes for 8.0.1 as fixed:
SPL-179594, SPL-177665 - tstats where clause does not filter as expected when structured like "WHERE * NOT (field1=foo AND field2=bar)"*

Also seems related to the question here: hxxps://answers.splunk.com/answers/760542/why-only-one-condition-works-for-where-clause-in-a.html

Similar to the asker above, I am hoping to do the filtering in the WHERE clause of the tstats for performance. I run this search over the past 24h and it takes a while to run. I'd rather not split the tstats by src_ip and have to reaggregate with another stats, and would prefer to do the filtering BEFORE passing the stats to |search.

I can work around it if I have to (the search below DOES work), but I'd rather go with something a bit more performant.

| tstats count(All_Traffic.dest_ip) AS ip_count count(All_Traffic.dest_port) AS port_count from datamodel=Network_Traffic by All_Traffic.dest_ip, All_Traffic.dest_port, All_Traffic.src_ip
| rename All_Traffic.* AS * 
| where (cidrmatch("10.0.0.0/8",src_ip) OR cidrmatch("172.16.0.0/12",src_ip) OR cidrmatch("192.168.0.0/16",src_ip) OR cidrmatch("169.254.0.0/16",src_ip)) AND NOT (cidrmatch("10.0.0.0/8",dest_ip) OR cidrmatch("172.16.0.0/12",dest_ip) OR cidrmatch("192.168.0.0/16",dest_ip) OR cidrmatch("169.254.0.0/16",dest_ip))

[begleyj1 · Accepted solution]

Yeah, I've been noticing negative look aheads in tstats are hit or miss. The workaround I have been using is to add the exclusions after the tstats statement, but additional if you are excluding private ranges, throw those into a lookup file and add a lookup definition to match the CIDR, then reference the lookup in the tstats where clause.

| tstats `summariesonly` count(All_Traffic.dest_ip) AS ip_count count(All_Traffic.dest_port) AS port_count 
 from datamodel=Network_Traffic.All_Traffic
 where NOT [inputlookup internal_ranges.csv | fields ip | rename ip as All_Traffic.dest]
 by All_Traffic.dest_ip, All_Traffic.dest_port, All_Traffic.src_ip
|`drop_dm_object_name(All_Traffic)`

Or you could try cleaning the performance without using the cidrmatch. The IN function can identify CIDR.

| search NOT dest IN (10.0.0.0/8,172.16.0.0/12, 192.168.0.0/16)

[stroud_bc]

I really like the first approach you mention, but I can't seem to get it to work. I created the lookup and defined a lookup definition for CIDR(ip), but the subsearch with |inputlookup doesn't seem to be filtering on CIDR. Should it? It seems like the lookup definition would apply within the subsearch, but wouldn't help to match IPs against CIDRs when the subsearch is compared to the data. This is mostly guesswork on my part though-- I don't know the intricacies of subsearch filters or lookups, so there may be a different problem.

[begleyj1]

Interesting!! You are referencing the lookup definition name and not the lookup file in the input lookup, correct? Also, try running | search NOT dest_ip IN (10.0.0.0/8,172.16.0.0/12, 192.168.0.0/16)

[stroud_bc]

Yep-- used the lookup name, not the lookup file (they have different names, and I double-checked the permissions!)

This does not work:

| search NOT dest_ip IN (10.0.0.0/8,172.16.0.0/12, 192.168.0.0/16)

This DOES work:

| where NOT cidrmatch("10.0.0.0/8",dest_ip)

I'm noticing that none of the |search based negation filters work. Note-- the affirmative versions of these searches all work. I can say things like dest_ip=10.0.0.0/8 or dest IN (10.0.0.0/8,172.16.0.0/12, 192.168.0.0/16) and it will work fine, but the negation is what isn't working.

This is in Splunk Enterprise 8.0.1.

[stroud_bc]

The same is true of the |tstats ... where NOT [|inputlookup] -- it works for the positive case but not for the negation.

[stroud_bc]

Just heard back from my Splunk support rep:

this is actually a known issue and there is currently an open bug about this.
SPL-179357 is the bug number, which reports this behavior, the workaround is the one you are already tested where NOT cidrmatch("127.0.0.0/8", ip)
either way, the problem has been isolated and fixed in version 8.0.2

I guess we're not crazy! Thanks for the help.

[stroud_bc]

OK, additional mystery-- the second search you proposed doesn't filter these either. And neither does

| search dest_ip!=10.0.0.0/8

or

| search NOT dest_ip=10.0.0.0/8