How to use Not Exists function in Splunk when 2 indexes are involved ?

[Real_captain]

Hi 

Can someone help to explain how we can use Not-exists in Splunk. 

Example is attached below for which i need to use this  function in Splunk. 

1) Search1 generates a set of results. 

2) Search2 also generated a set of results. 

There is a common field between the 2 Searches. I want to add a search in splunk as below : 

Results of Search1 (Not exists (results of Search2 )) common field = Field1 

Search1

`eoc_stp_events_indexes` host=p* OR host=azure_srt_prd_0001  
| table timestampOfReception, messageOriginIdentifier, messageType, status, messageBusinessIdentifier, originPlatform, direction, sourcePlatform, currentPlatform, targetPlatform, senderIdentifier, receiverIdentifier, currentPlatform

Search2 :

(index=events_prod_srt_shareholders_esa OR index=eoc_srt) seev.047 Name="Created Disclosure Response Status Advice Accepted"
| table  messageBusinessIdentifier

Field1

messageBusinessIdentifier

[PickleRick]

There are several possible approaches to this problem.

One is to use a subsearch. https://docs.splunk.com/Documentation/Splunk/9.1.2/SearchTutorial/Useasubsearch

Long story short - if you have search1 returning a list of values for field1, you use

NOT ([your search | returining| values | table field1])

as part of your main search.

This however has its drawbacks - the typical limits of a subsearch - the number of returned results and the execution time limit. That means that your search can be silently finalized without you even realizing that you're getting incomplete/invalid results.

Another approach would be to append both of those resultsets adding a field which describes which search they come from and then do stats over the common field and filter out those that do have the identifier.

Like

<search1> | eval wherefrom=1 | append 
    [ <search2> | eval wherefrom=2 ]
| stats values(*) as * by common_field
| where NOT wherefrom=2

Append though is also subject to subsearch limitations

 If both your searches are streaming searches, you can use multisearch instead of appending results - multisearch is _not_ subject to subsearch limitations.

[Real_captain]

Hi 

Can we use below to fetch only those events which are extracted by Search1 and not extracted by Search2 based on the field messageBusinessIdentifier.

`eoc_stp_events_indexes` host=p* OR host=azure_srt_prd_0001  
NOT [ search (index=events_prod_srt_shareholders_esa OR index=eoc_srt) seev.047 Name="Created Disclosure Response Status Advice Accepted"
| fields messageBusinessIdentifier ]
| fillnull timestampOfReception , messageOriginIdentifier, messageBusinessIdentifier, direction, messageType, currentPlatform, sAAUserReference value="-"
| sort timestampOfReception
| table timestampOfReception, messageOriginIdentifier, messageType, status, messageBusinessIdentifier, originPlatform, direction, sourcePlatform, currentPlatform, targetPlatform, senderIdentifier, receiverIdentifier, currentPlatform,
| rename timestampOfReception AS "Timestamp of reception", originPlatform AS "Origin platform", sourcePlatform AS "Source platform", targetPlatform AS "Target platform", senderIdentifier AS "Sender identifier", receiverIdentifier AS "Receiver identifier",
messageOriginIdentifier AS "Origin identifier", messageBusinessIdentifier AS "Business identifier", direction AS Direction, currentPlatform AS "Current platform", sAAUserReference AS "SAA user reference", messageType AS "Message type"

[gcusello]

Hi @Real_captain ,

sorry, if you want to exclude results from search 2 you have to use the NOT operator:

`eoc_stp_events_indexes` host=p* OR host=azure_srt_prd_0001 NOT [ search (index=events_prod_srt_shareholders_esa OR index=eoc_srt) seev.047 Name="Created Disclosure Response Status Advice Accepted"
| fields messageBusinessIdentifier ]
| table timestampOfReception, messageOriginIdentifier, messageType, status, messageBusinessIdentifier, originPlatform, direction, sourcePlatform, currentPlatform, targetPlatform, senderIdentifier, receiverIdentifier, currentPlatform

Ciao.

Giuseppe

[Real_captain]

Hi gcusello 

This is working fine when we have a single field in common. 

If we have more than 1 field as a key between the 2 searches , then is it possible to Exclude results from search 2 based on the 2 fields instead of 1 ?? 

[PickleRick]

You can return more than one field from the subsearch.

If you have a subsearch returning a set of values

field1=val1 field2=val2 field3=val3
field1=val4 field2=val5 field3=val6
field1=val7 field2=val8 field3=val9
...

It is rendered as sets of condition in the outer search

(field1=val1 AND field2=val2 AND field3=val3) OR (field1=val4 AND field2=val5 AND field3=val6) OR (field1=val7 ANR field2=val8 AND field3=val9) ...

So you can filter by any set of fields.

[gcusello]

Hi @Real_captain ,

Please try something like this:

`eoc_stp_events_indexes` host=p* OR host=azure_srt_prd_0001  [ search (index=events_prod_srt_shareholders_esa OR index=eoc_srt) seev.047 Name="Created Disclosure Response Status Advice Accepted"
| fields messageBusinessIdentifier ]
| table timestampOfReception, messageOriginIdentifier, messageType, status, messageBusinessIdentifier, originPlatform, direction, sourcePlatform, currentPlatform, targetPlatform, senderIdentifier, receiverIdentifier, currentPlatform

in few words: you have to use a subsearch putting attention that the fields at the end of the subsearch are only the ones you want to use as key and that the field names are exactly the same.

Ciao.

Giuseppe

[Real_captain]

Hi 

I want to extract only those events of Search1 for which there does not exists the result in Search2. 

is it possible in SPLUNK ?? 

Similar to NOT EXISTS in DB2. 

[isoutamo]

Hi

here is explained how to emulate joins in  SQL in SPL. https://community.splunk.com/t5/Splunk-Search/What-is-the-relation-between-the-Splunk-inner-left-joi...

There is also several conf presentations how to join datasets without command join e.g. https://conf.splunk.com/files/2019/slides/FNC2751.pdf

r. Ismo