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
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?
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?
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.