Splunk Search

How to implement "NOT IN" in Splunk

griffinpair
Path Finder
  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?

0 Karma

mstjohn_splunk
Splunk Employee
Splunk Employee

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!

0 Karma

gcusello
SplunkTrust
SplunkTrust

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
Ultra Champion

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

Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...