Subsearch in tstats causing issues

[GindiKhangura]

I am encountering an issue when using a subsearch in a tstats query. Specifically, I am seeing the count of events increase as well as taking much longer to run than a query without the subsearch (1.5s vs 85s). Note that in my case the subsearch is only returning one result, so I wouldn't expect such a pronounced performance impact.

The examples below use Splunk's own data model that searches over the _audit index, so the performance issue is not as apparent since there is not as many events as in my use-case.

| tstats count FROM  datamodel=internal_audit_logs WHERE Audit.action="add"

 Returns a count of 33.

| tstats count FROM  datamodel=internal_audit_logs WHERE [ | makeresults annotate=f | fields -_time | eval Audit.action="add" ]

Returns a count of 46.

This issue is not reproducible with index queries.

[ldongradi_splun]

I can't say why I understand the difference in count between your 2 searches.

But trying a similar syntax as yours in my test environment gives me an execution time of 0.37s with the subsearch vs 0.075s without it, while I don't have any discrepancy in the results.

Based on these performance, I would probably not investigate that syntax any further if it leads to an execution time 5 times worse.

[ldongradi_splun]

Hi. I'm not sure why you try to use that syntax. The subsearch is executed before the outer search, and adds up to the execution. In term of performance, this is not nice.

But let's say you have your reasons ...:

It seems to me there is no space here: fields -_time while it should be: fields - _time but you don't even need to get rid of that field.

If you want to return only 1 value, you can use [ | makeresults | eval Audit.action="add" | return Audit.action] though I'm still unsure of what you try to achieve.

[GindiKhangura]

@ldongradi_splun 

I am trying to modify the tstats query with a dynamic value without having to rely on SimpleXML, tokens, or JS (should strictly be done via search).

Regardless which method I use, the issue still exists where the count is incorrect.

| tstats count FROM  datamodel=internal_audit_logs WHERE Audit.action=[ | makeresults annotate=f | eval Audit.action="\"add\"" | return $Audit.action ]

creates a reportSearch of

| tstats  count FROM  datamodel=internal_audit_logs WHERE Audit.action= "add"                                 

If I run the reportSearch manually in the search app, it runs fine and returns a count of 393 results. Running the query with the subsearch returns an incorrect count of 657.

To answer your question why I am using a subsearch this way: in my case, the subsearch is a fast search that returns just one value based on some criteria, so I wouldn't expect a performance impact. Correct me if I'm wrong, but the subsearch only runs once. It allows me to create a search that dynamically adapts based on some criteria.