Splunk Search

Left Outer Join Using NOT clause

petem3
New Member

I'm trying to identify arrangement's in one data source that do not exist in another data source. One of the sources has multiple indexes, but assume this should not cause a problem. I'm trying to use the NOT clause instead of a JOIN clause. The query returns values in both sources; any ideas?

index=A
| RENAME AR_ID as arrangementId
| dedup arrangementId
| fields arrangementId
NOT
[search index=B OR index=C OR index=D
| dedup arrangementId
| return 10000 arrangementId ]
| table arrangementId

Tags (2)
0 Karma
1 Solution

to4kawa
Ultra Champion
index=A OR index=B OR index=C OR index=D
| eval arrangementId=coalesce(AR_ID,arrangementId)
| stats dc(index) as index_count values(index) as index by arrangementId
| where dc=1 AND index="A"
| table arrangementId

or

index=A | fields AR_ID | dedup AR_ID | rename arrangementId
| append [search index=B OR index=C OR index=D
| fields arrangementId
| dedup arrangementId | eval flag="others"]
| stats values(others) as others by arrangementId
| where flag!="others"
| table arrangementId

Which is faster?

View solution in original post

0 Karma

to4kawa
Ultra Champion
index=A OR index=B OR index=C OR index=D
| eval arrangementId=coalesce(AR_ID,arrangementId)
| stats dc(index) as index_count values(index) as index by arrangementId
| where dc=1 AND index="A"
| table arrangementId

or

index=A | fields AR_ID | dedup AR_ID | rename arrangementId
| append [search index=B OR index=C OR index=D
| fields arrangementId
| dedup arrangementId | eval flag="others"]
| stats values(others) as others by arrangementId
| where flag!="others"
| table arrangementId

Which is faster?

0 Karma

petem3
New Member

The first solution was fastest; just a minor correction

where dc=1 AND index="A"

should be

where index_count=1 AND index="A".

It worked perfectly, many thanks for your help.

0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...