How to implement "NOT IN" in Splunk

[griffinpair]

1. I have an index that is populated by and extensive, long running query that creates a line like "Client1 Export1 Missed. Expected Time: 06:15:00".

2. I have another index that is populated with fields to be over written and not appear in report. So if this above file needs to not show up I have the information of "Client1" and "Export1"

I am looking for a way to search for all results in point 2 (the ones to not include) and exclude them in point 1. Something like this:

| where "Missed Exports Message Alert" NOT in [ search  sourcetype="si_Export_FileMissed" earliest=-24h@h | eval clearExport = ClientID + " " + ExportType | table clearExport ]

How do you use NOT in as this is not working as I expect.

Another way to ask this question, is how to exclude results from a subsearch from the overall search?

[mstjohn_splunk]

hi @griffinpair ,

Did either of the answers below solve your problem? If so, please resolve this post by approving one of them.

If your problem is still not solved, keep us updated so that someone else can help ya.

Thanks!

[gcusello]

Hi griffinpair,
try something like this:

your_search NOT [ search sourcetype="si_Export_FileMissed" earliest=-24h@h | eval clearExport = ClientID + " " + ExportType | rename clearExport  AS "Missed Exports Message Alert" | fields "Missed Exports Message Alert"]

In othe words: you can use a subsearch if the field/s to compare is/are the same.

If, between your_search and the NOT search there are other things, you can use something like this:

your_search 
| ...
| search NOT [ search sourcetype="si_Export_FileMissed" earliest=-24h@h | eval clearExport = ClientID + " " + ExportType | rename clearExport  AS "Missed Exports Message Alert" | fields "Missed Exports Message Alert"]

but it's better to have the search filters as left as possible.

In addition, in every search it's better to use also the index=my_index rule to have more performant searches.

Bye.
Giuseppe

[kristian_kolb]

your subsearch will generate a set of field-value pairs, effectively giving a search like

... | where "Missed Exports Message Alert" NOT in (clearExport=a OR clearExport=b OR clearExport=c)

This does not work for two reasons;
1) "NOT in" is not valid syntax. At least not to perform what you wish.
2) "clearExport" is probably not a valid field in the first type of event.
on a side-note, I've always used the dot (.) to concatenate strings in eval.

I believe that you can alter the subsearch to return the results as values only, which may come closer to what you want to do, i.e. to rename the field to "search" or "query".

https://docs.splunk.com/Documentation/Splunk/7.1.2/Search/Changetheformatofsubsearchresults

That brings us back to "NOT in" - with the above changes you should probably only need to remove the "in" part.

Also, I'm guessing that your search does not really start with "| where", as that would probably not yield any results