Left Outer Join Using NOT clause

[petem3]

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

[to4kawa · Accepted solution]

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?

[petem3]

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.