topic Re: How to subtract results from inner search and then from outer search in Splunk Search

How to subtract results from inner search and then from outer search

qman — Fri, 14 Feb 2020 15:27:49 GMT

Hi everybody,

I need to find out all the servers on which the Windows EventID=XYZ is not logged.
Therefore I run a search for all servers in my index (to have all the servers) and then I do an inner search where I only search for servers where at least one single time the EventID=XYZ was logged.
When I now subtract this result from the "all servers" result only those should remain which didn't log the EventID=XYZ.

But how is this done?

index=servers
[search index=servers EventID=XYZ
    | stats values(host) as not_wanted_servers
    | fields not_wanted_servers]
| stats values(host) as target_servers
|where target_servers NOT in not_wanted_servers

The last line doesn't work but should show what I want to do.

Re: How to subtract results from inner search and then from outer search

efavreau — Wed, 30 Sep 2020 04:06:56 GMT

There are a few ways to potentially solve this, but realize that looking for something that doesn't exist is not a great situation.
I think I would evaluate it with a case statement, because later on, it allows you at add additional conditions if needed.
I might go with something similar to:

index=servers
| eval ServerType=case(
     EventID = "XYZ","not_wanted_servers",
     1=1, "target_servers"
     )
| where ServerType="target_servers "

We are evaluating (eval command) your servers into Server types: ones not wanted and ones targeted.
EventID="XYZ" is to be called not_wanted_servers, and everything else is to be called target_servers.
Then we use the where, so only look for the targeted ServerType, target_servers.