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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...