topic Re: Why does the subsearch not exclude the results from main search? in Splunk Search

Why does the subsearch not exclude the results from main search?

mmarinov — Fri, 18 Oct 2019 09:29:21 GMT

I have the following search:

index=my_index asset_type="Workstation" asset_atp="false" asset_status="ACTIVE" earliest=-1d@d latest=-0d@d
| search NOT
    [search index=my_index asset_type="Workstation"  asset_status="ACTIVE" earliest=-1d@d latest=-0d@d
| eval nexttime=strftime(relative_time(now(),"-w@w") ,"%Y-%m-%d %H:%M:%S")
| where ad_date_created>nexttime]

When i run the searches separately, the first one returns 1841 events and the second one returns 487 events.
But the combined search does not exclude the results from the second search so I get the full 1841 events again.

When I use those searches with stats, just to get the count of the events, everything looks fine and I get the deisred result of 1354 with the following search:

index=my_index asset_type="Workstation" asset_atp="false" asset_status="ACTIVE" earliest=-1d@d latest=-0d@d
| stats count as TotalA
| appendcols 
[search index=my_index asset_type="Workstation"  asset_status="ACTIVE" earliest=-1d@d latest=-0d@d
| eval nexttime=strftime(relative_time(now(),"-w@w") ,"%Y-%m-%d %H:%M:%S")
| where ad_date_created>nexttime
| stats count as TotalB]
| eval Total=TotalA-TotalB
| fields Total

What am I doing wrong?

Thanks in advance for the help!

Re: Why does the subsearch not exclude the results from main search?

kartm2020 — Wed, 30 Sep 2020 02:39:32 GMT

Hi nmarinov,

Can you pleae try the below query and let me know whether it is working as expected.
index=my_index asset_atp="false" earliest=-1d@d latest=-0d@d | eval ExceptionType=if(searchmatch ("Workstation"), ("ACTIVE"),("false")
| eventstats dc(ExceptionType) AS numExceptionTypes values(ExceptionType) AS ExceptionTypes | search numExceptionTypes=1 AND ExceptionType="false"

I have tried my level best based on your inputs. Lets c 🙂

Re: Why does the subsearch not exclude the results from main search?

mmarinov — Fri, 18 Oct 2019 10:31:39 GMT

Hi kartm,

Unfortunately, yout query does not return any results.

Best regards.

Re: Why does the subsearch not exclude the results from main search?

kmaron — Fri, 18 Oct 2019 14:26:43 GMT

You should be able to just flip your greater than to a less than or equal. The only other difference in your queries is the asset_atp="false" and it just excludes more things.

index=my_index asset_type="Workstation" asset_status="ACTIVE" asset_atp="false" earliest=-1d@d latest=-0d@d 
| eval nexttime=strftime(relative_time(now(),"-w@w") ,"%Y-%m-%d %H:%M:%S") 
| where ad_date_created<=nexttime