tstats returns a count of zero when using an OR clause

[chrislkt]

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.

[chrislkt · Accepted solution]

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.

[yuanliu]

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.

[chrislkt]

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.

[PickleRick]

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.

[yuanliu]

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.  

[scelikok]

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

[chrislkt]

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

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*")

[KendallW]

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"]

[chrislkt]

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

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.

[isoutamo]

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

[chrislkt]

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.