Splunk Search

use of NOT operator

pjmenon
Explorer

I have the following search to search file1 & file2 who have MY_ID as common field.

(source="file11" keyword1 ) OR (source="file2")| transaction fields=MY_ID| search source="file1" source ="file2"

Now, I need to find find events in file1 that excludes item in search above. the following did not yield correct results. what am I doing wrong:

(source="file11" keyword1 ) NOT [search (source="file11" keyword1 ) OR (source="file2")| transaction fields=MY_ID| search source="file1" source ="file2"]
Tags (1)

woodcock
Esteemed Legend

I believe you are trying to find events in "file1" that do not share the same "MY_ID" field value with events in file2. If so, use “eventstats” instead of subsearches because the "*stats" commands have no event/size limit:

 (source="file1" keyword1 ) OR (source="file2") |
 eventstats dc(souce) AS numSources, values(source) AS sources BY MY_ID |
 where sources="file1"
0 Karma

sideview
SplunkTrust
SplunkTrust

some assumptions - i assume by 'file11' you actually mean 'file1'?

Also I assume that you are trying to get the 'file1' events but filter out all the 'file1' events that are actually end up being a part of transactions with 'file2'.

I think your problem is that you dont have a a fields clause in your subsearch to restrict the NOT from applying to just the MY_ID values. Without a fields clause to quiet it down, what will come out of a subsearch is an ANDed and ORed expression about every field in every row. So if the subsearch results have 2 rows and 3 fields, you end up with

`NOT ( (row1_field1="foo" row1_field2="bar" row1_field3="baz") OR (row2_field1="baz" row2_field2="bat" row2_field3="cux") )

And that is probably such a specific NOT that it ends up having no filtering effect on your outer events.

Anyway, this should work:

(source="file1" keyword1 ) NOT [search (source="file1" keyword1 ) OR (source="file2") | transaction MY_ID | search source="file1" source ="file2" | fields MY_ID]

If the transaction command outputs say 3 rows, then you'll end up with an effective NOT of

NOT (MY_ID="value1" OR MY_ID="value2" OR MY_ID="value3")

When debugging subsearches, it's helpful to run the search and then do 'Actions > Inspect Search', and then scroll down and look at the 'rtlitsearch' key in there. It'll look really strange but look past the weirdness and you'll see the final boolean logic that came out of the subsearch which can help immensely.

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...