Splunk Search

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

djconroy
Path Finder

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.

Tags (3)
0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

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

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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
Path Finder

Great, thank you!

0 Karma

djconroy
Path Finder

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?

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...