Looking for field values present in one index missing from another index?

[djconroy]

I have one index with a field "MessageId" that is common with another index.

I need to got through all the values of MessageId in index1, and then search index2 to make sure they are also there.

I was trying to use an outer join to do this with the following syntax:

search index=index1
sourcetype=sourcetype1 Function=SubscriberFunction ClockTypeId=1 OR ClockTypeId=2 earliest=-5d@d latest=-4d@d
| eval ClockPunchID=MessageId
| eval Status1="Received"
| join ClockPunchID type=outer [search index=index2 * | eval ClockPunchID=MessageId | eval Status2 = "Received" ]
| eval Status = if(match(Status1,Status2), "Complete", "Incomplete")
| table ClockPunchID ClockTypeId Status1 Status2 Status

The problem I am getting is I get no results at all from my index2 search. All values of Status2 are blank. If I manually copy and paste the subsearch syntax into a separate search window I get exactly what I would expect, but as part of a join I get nothing, only the index1 results.

[martin_mueller · Accepted solution]

So, you have the field MessageId extracted in both indexes and want to know what values are in only one index and what values are in both? Try this:

  (index=index1 sourcetype=sourcetype1 Function=SubscriberFunction ClockTypeId=1 OR ClockTypeId=2) OR (index=index2)
| stats values(index) as indexes values(ClockTypeId) as ClockTypeId by MessageId
| eval Status = if(mvcount(indexes)==2, "Complete", "Incomplete")
| rename MessageId as ClockPunchID

[martin_mueller]

Append this to only keep events that exist in index1 or both:

... | search indexes="index1"

That's translated into "the multi-valued field indexes contains the value index1".

[djconroy]

Great, thank you!

[djconroy]

That is getting me close... but there is a delay in the Messages getting to Index2, which can skew results. I'm not really concerned if they are in index2 but not in index1. Is there a way to filter out occurrences where the MessageId exists in index2 but not index1?