Splunk tstats search with IPV6 subnet - WHERE clause is not an exact query

[mfleitma]

Hello,

I want to run a datamodel tstats search, excluding some events with a lookup for src_ip's. In case I fill the lookup with a ipv6 subnet, the search breaks with Error in 'TsidxStats': WHERE clause is not an exact query.

My query in a simple way:

-------------------------
| tstats summariesonly=t allow_old_summaries=t values(sourcetype) as sourcetype count AS eventCount max(_time) as maxtime min(_time) as mintime from datamodel=Intrusion_Detection
where (nodename = IDS_Attacks)

NOT [ | inputlookup ip_test.csv | fields src ]

groupby IDS_Attacks.src, IDS_Attacks.dest, IDS_Attacks.signature, IDS_Attacks.action

------------------------

lookup ip_test.csv:

comment,src
ipv4 net,10.20.30.0/24
ipv4,10.20.30.43
ipv6,1a00:2a60:3000:1:1:43
ipv6 net,1a00:2a60:3000:1::/64

With deleting the "ipv6 net" entry the search is running.

[tscroggins]

Hi @mfleitma,

IPv6 CIDR matching with tstats _should_ work in Splunk Cloud 9.1.2312 and Splunk Enterprise 9.3 or later. Previous versions included this tstats limitation:

CIDR matching support for IPv4 and IPv6 addresses

The tstats command filters events with CIDR match on fields that contain IPv4 addresses, but not IPv6 addresses. Running tstats searches containing IPv6 addresses might result in the following error indicating that the addresses are treated as non-exact queries:

Error in 'TsidxStats': WHERE clause is not an exact query

Newer versions include this limitation:

Limitations of CIDR matching with tstats

As with the search command, you can use the tstats command to filter events with CIDR match on fields that contain IPv4 and IPv6 addresses. However, unlike the search command, the tstats command may not correctly filter strings containing non-numeric wildcard octets. As a result, your searches may return unpredictable results.

However, tstats still returns the "exact query" error in Splunk Enterprise 10.0.

If your environment is optimized, i.e., you're not using source type renaming or other features that defeat search optimization directives, you may be able to use the datamodel command and achieve performance similar to tstats:

| datamodel Intrusion_Detection IDS_Attacks flat summariesonly=true allow_old_summaries=true
| search NOT [ | inputlookup ip_test.csv | fields src ]
| stats values(sourcetype) as sourcetype count as eventcount max(_time) as maxtime min(_time) as mintime

[mfleitma]

Hi tscroggins,

I also saw the notes in the documentation, but couldn't believe that a |tstats search with ipv6 subnet generally doesn't work.
Unfortunately, it seems to be the case.

Your suggestion with the |datamodel search works in connection with an ipv6 subnet, as it is not a |tsats search. However, it takes 8 times longer on my test system.
I haven't tried Splunk Enterprise 10.0 yet, but as you say, it doesn't seem to work there either.

I'll see if I can adjust my search accordingly, but I have the problem that I have many |tstats searches that may have ipv6 subnet addresses in automatically populated lookups.
After a long runtime of the searches, an ipv6 subnet address was entered for the first time.

Thank you for your help.

[tscroggins]

Hi @mfleitma,

If the datamodel command is slower, there's likely something in your environment defeating the READ_SUMMARY directive added by the search optimizer. In my test environment, this:

| datamodel Network_Traffic All_Traffic flat summariesonly=true allow_old_summaries=true
| search NOT src_ip=1a00:2a60:3000:1::/64

translates to this:

| search (index=main NOT src_ip=1a00:2a60:3000:1::/64 tag=communicate tag=network (index=* OR index=_*)) DIRECTIVES(READ_SUMMARY(allow_old_summaries="true" dmid="XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX_DM_Splunk_SA_CIM_Network_Traffic" name="Network_Traffic.All_Traffic" predicate="*" summariesonly="true"),READ_SUMMARY(predicate="NOT \"All_Traffic.src_ip\"=1a00:2a60:3000:1::/64"),REQUIRED_TAGS(intersect="t" tags="cloud,pci"))

tstats remains problematic with respect to all CIDR matches. Beyond the limitations described by the documentation, for example, the following addresses are equivalent:

1a00:2a60:3000:1::1
1a00:2a60:3000:0001::1
1a00:2a60:3000:0001:0000:0000:0000:0001

but the following tstats search will only match 1a00:2a60:3000:1::1:

| tstats summariesonly=true allow_old_summaries=true count from datamodel=Network_Traffic.All_Traffic where All_Traffic.src_ip=1a00:2a60:3000:1::1

If your source normalizes IPv6 addresses to a canonical format, i.e., RFC 5952, that may not be a concern; however, bitwise CIDR matching appears to be defeated by tstats, regardless.

It may be worth the effort to figure out why the datamodel command is slow in your environment.

[richgalloway]

It sounds like some of the data in the lookup file is confusing the SPL parser.  When you run the subsearch by itself with format, does the output look like valid SPL?

| inputlookup ip_test.csv | fields src | format

[mfleitma]

Hi,

running your spl I get:

( ( src="10.20.30.0/24" ) OR ( src="10.20.30.43" ) OR ( src="1a00:2a60:3000:1:1:43" ) OR ( src="1a00:2a60:3000:1::/64" ) )