topic Re: tstats returns a count of zero when using an OR clause in Splunk Search

tstats returns a count of zero when using an OR clause

chrislkt — Mon, 05 Aug 2024 19:15:18 GMT

For some reason my |tstats count query is returning a result of 0 when I add an OR condition in my where clause if the field doesn't exist in the dataset, or if the OR condition specifies a string value when the value for the field in the data is always an integer.

For example:

This query returns the correct event count (or at least it's non-zero):

|tstats count where index="my_index" eventOrigin="api" (accountId="8674756857")

Adding this OR condition returns a count of zero -- why? Note that for this time range there are no events with a serviceType field, but for other time ranges there are events with a serviceType field.

|tstats count where index="my_index" eventOrigin="api" (accountId="8674756857" OR serviceType="unmanaged")

Adding this OR condition also returns zero -- why? It's true that accountId should normally be an integer, but it's an OR, so I still expect it to count those events.

|tstats count where index="my_index" eventOrigin="api" (accountId="19783038942" OR accountId="aaa")

Using a * results in the same non-zero count as the first query, which is expected, even though there are no events with a serviceType field:

|tstats count where index="my_index" eventOrigin="api" (accountId="8674756857" OR serviceType="unmana*")

Why would adding an OR condition in tstats cause the count to be zero? The same problem does not occur with a regular search query.

I am on Splunk 9.1.0.2.

Re: tstats returns a count of zero when using an OR clause

KendallW — Tue, 06 Aug 2024 12:26:42 GMT

Hi @chrislkt

| tstats only searches the indexed fields in tsidx files. All fields searched with tstats must exist in the tsidx files. Most likely the field from one of the sides of your OR condition cannot be found within the time range specified, so tstats can't evaluate the OR condition properly because of the non-existent field, leading to 0 results.

To get around this, you can just append another tstats command to replace the other side of the OR condition, e.g.

| tstats count where index="my_index" eventOrigin="api" accountId="8674756857" | append [| tstats count where index="my_index" eventOrigin="api" serviceType="unmanaged"]

Re: tstats returns a count of zero when using an OR clause...

scelikok — Tue, 06 Aug 2024 04:51:51 GMT

Hi @ciphercloudops

tstats command works only with indexed fields. Default indexed fields for indexes are "host, source and sourcetype fields are indexed fields. Adding some fields as indexed is possible if you regularly need faster searches based on those fields. But this addition needs to be done while indexing.

According to your tests, it seems the accountId and eventOrigin fields are indexed fields, which is why you can use them in tstats query with no problems.

There is an option for querying fields unindexed fields if your raw data is suitable. If your serviceType field value is like serviceType=unmanaged (without spaces or quotes/double quotes) you can try below;

| tstats count where index="my_index" eventOrigin="api" ( accountId="8674756857" OR TERM(serviceType=unmana*) )

Re: tstats returns a count of zero when using an OR clause...

chrislkt — Tue, 06 Aug 2024 20:57:53 GMT

Thanks for your response @scelikok . We are using a custom sourcetype for our events which is configured in inputs.conf and props.conf to extract the fields from the events as JSON, so based on my reading of this documentation I think all the fields from the JSON are getting indexed, including serviceType:

https://docs.splunk.com/Documentation/Splunk/9.3.0/Data/Extractfieldsfromfileswithstructureddata#Use_configuration_files_to_enable_automatic_header-based_field_extraction

We are able to use tstats to query on any of our JSON fields that I've tried, for example returns the correct non-zero count:

|tstats count where index="my_index" serviceType="unmanaged"

I tried your tip about using TERM(), but I don't think it applies here. As I mentioned, I can use a wildcard on the serviceType value even without TERM() and it works as expected, even when there are no events containing serviceType:

|tstats count where index="my_index" eventOrigin="api" 
(accountId="8674756857" OR serviceType="unmana*")

Re: tstats returns a count of zero when using an OR clause

chrislkt — Tue, 06 Aug 2024 21:16:53 GMT

Thanks @KendallW

We are using automatic header-based field extraction for our JSON documents as described here:

https://docs.splunk.com/Documentation/Splunk/9.3.0/Data/Extractfieldsfromfileswithstructureddata#Use_configuration_files_to_enable_automatic_header-based_field_extraction

So when fields like serviceType are present in the events I believe they are getting indexed.

You are correct that serviceType is completely missing from the events for time ranges where my tstats query returns 0, but I don't understand why it would work this way, especially since adding a wildcard to either OR value returns the correct non-zero result:

|tstats count where index="my_index" eventOrigin="api" 
(accountId="8674756857*" OR serviceType="unmanaged")

|tstats count where index="my_index" eventOrigin="api" 
(accountId="8674756857" OR serviceType="unmanaged*")

This really seems like a bug.

Thanks for the tip about |append, though this is very undesirable and possibly infeasible for us considering the complexity of our queries and how many we have.

Re: tstats returns a count of zero when using an OR clause

yuanliu — Wed, 07 Aug 2024 05:49:18 GMT

First, OR operator is certainly usable in tstats. Try this:

| tstats count values(sourcetype) values(source) where index = _introspection (sourcetype = kvstore OR source="/Applications/Splunk/var/log/introspection/disk_objects.log")

On my laptop, this gives

count	values(sourcetype)	values(source)
3059	kvstore splunk_disk_objects	/Applications/Splunk/var/log/introspection/disk_objects.log /Applications/Splunk/var/log/introspection/kvstore.log /Applications/Splunk/var/log/introspection/kvstore.log.1

So, the problem is elsewhere. To troubleshoot, you need to examine data very closely. For example,

|tstats count values(serviceType) where index="my_index" eventOrigin="api" (accountId="8674756857")

|tstats count values(accountId) where index="my_index" eventOrigin="api" (serviceType="unmanaged")

and so on. Also explore your index search output. Without seeing actual data, it is very difficult to perform haircut on the phone line, but there doesn't appear to be a bug in this aspect.

Re: tstats returns a count of zero when using an OR clause

isoutamo — Wed, 07 Aug 2024 13:25:05 GMT

There some limits how many fields and how many characters events can be to get automatic indexing extraction to work. I cannot recall the length, but those are not so big than someone can expect. Probably those was defined on limits.conf or some other conf file.
If I recall right you can also find some discussions on answers about this issue?

Also this conf presentation can help you with unindexed fields. https://conf.splunk.com/files/2023/slides/PLA1258C.pdf

r. Ismo

Re: tstats returns a count of zero when using an OR clause

chrislkt — Fri, 09 Aug 2024 14:40:05 GMT

I can't find any evidence that this is a simple data issue. We have many customers using queries like this, and it was only noticed a couple weeks ago. One guess is that the behavior changed when we upgraded from Splunk 8.x to 9.x a couple months ago.

To further illustrate:

|tstats count where index="my_index" eventOrigin="api" 
(accountId="8674756857")

Result: 6618

|tstats count where index="my_index" eventOrigin="api" 
(accountId="8674756857" OR serviceType="unmanaged")

Result: 0

|tstats count where index="my_index" eventOrigin="api" 
(accountId="8674756857" OR serviceType="unmanaged" OR noSuchField="noSuchValue*")

Result: 6618

So adding a bogus OR term with an asterisk in the value returns the correct result, but without it the result is 0. I can't imagine this is correct behavior, and we have submitted a support request to Splunk.

Re: tstats returns a count of zero when using an OR clause

chrislkt — Fri, 09 Aug 2024 14:45:53 GMT

Thanks for the idea about the limits. I checked them and it doesn't look like the cause in this case, though we have run into that before where events with too much data didn't get indexed.

Also, that looks like a very useful presentation.

Re: tstats returns a count of zero when using an OR clause

yuanliu — Fri, 09 Aug 2024 16:46:48 GMT

My laptop runs Splunk 9.2.2. So the version is not causing a problem.

As to asterisk in field name, this has come up in previous discussions several times. Each time, it turns out that the name of the field contains some invisible characters like trailing white space. You mentioned your raw events are JSON. That makes this type of problem less likely. But still, check the original JSON documents as well as ingested JSON events.

Re: tstats returns a count of zero when using an OR clause

PickleRick — Sat, 10 Aug 2024 19:39:22 GMT

If all else fails, it's always useful to check job log and see the lispy search. Might not solve the problem but can give valuable insight.

Re: tstats returns a count of zero when using an OR clause

chrislkt — Wed, 11 Sep 2024 00:04:57 GMT

Splunk support confirmed this is a bug in 9.1.0.2. Based on the SPL, it has been resolved in Beryllium 9.1.4 and Cobalt 9.2.1.

As a workaround until we upgrade, I have appended a bogus OR condition with a wildcard, e.g.:

 OR noSuchField=noSuchValue*

to the other OR conditions in our WHERE clauses, and this causes Splunk to return the correct result.