Splunk Search

Conditional Join/Subsearch

SplunkMonster
Engager

So I have two log sources-- one that stores values X and Y together in the same index, and the second which stores value X in one index and value Y in another. I need to figure out a way to have Splunk get both X and Y together for both sources in a search so I can work with that data further.

So for example, sourcetype ABC has the following data in myindex1:

x=1 y=dog

x=2 y=cat

x=3 y=pig

sourcetype BCD has the following data in myindex2:

x=1

x=2

x=3

and the following data in myindex3:

y=dog

y=cat

y=pig

Both sourcetypes are the same data, they're just coming from two different places and being stored differently.

I'm sure the first question that might come up is "why are you storing the data in a way that makes no sense," but unfortunately, that is well outside the scope of this and something that cannot be controlled in this particular situation. I need to find a way to pull the data from both sourcetypes together in one search so I have values X and Y for both, together, and can manipulate the data from there.

I tried doing a conditional join inside an eval statement, but every way I wrote it seemed to produce an error.

I'd love to hear any suggestions anyone has on other ways of attacking this problem-- I've seen some other threads on conditional searching and the like, but I haven't been able to find syntax that would work for this particular situation.

Tags (2)
0 Karma

SplunkMonster
Engager

Wow, I did an even worse job of explaining this than I originally thought, but that's ok, and I'm glad you mentioned that. So the biggest part that I failed to mention is that for sourcetype BCD, X exists in both myindex2 AND myindex3. That's what I'm trying to do the conditional join for, but I haven't found a way that I can write "pull fields X and Y from sourcetype ABC in myindex1, and where you see value X in myindex2 for sourcetype BCD, join that with myindex3 on value X to grab value Y. Sorry, it's late in the day and my brain isn't totally functional. I hope that makes more sense.

0 Karma

lukejadamec
Super Champion

Sometimes Splunk will auto-generate sourcetypes to match sources that are similar but slightly different. It happens all the time with iis logs.

To join two sourcetypes that are similar but different (perhaps one has more fields than the other), then use the OR statement to examine fields that are contained in each, same goes with indexes.

index=myindex1 OR index=myindex2 OR index=myindex3 sourcetype=ABC OR sourcetype=BCD x=1 y=dog

0 Karma

vkari
New Member

index=myindex1
eventName=5000 --->have b(starting time and ending time ) and reference id
eventName=5001 --->have (starting time and ending time ) and reference id

here my condition if both event names - reference id are same then,
event 5000 stating time and event 5001 ending time and total duration of time and average time I need to show in dashboards
can you please provide query

0 Karma

somesoni2
Revered Legend

When Splunk indexes any data, it can not automatically decide on the relationship among two sources (source1 which logs X and source2 which logs Y values). There has to be a defined relationship/rule using which you can related X events with Y events. E.g. of such relationship could be like, both events happens at same time (means timestamp for source1 -> X=1 is same as source2 -> Y=dog). Another example will be like they are related in the order in which they appear (First record of source1 is related to first record of source2). Unless such relationship is determined, you can't related them.

0 Karma

SplunkMonster
Engager

Sorry, I should have explained that more clearly-- X always uniquely corresponds to Y (consider X a unique code for Y) for both sources. X and Y always exist in a pair like that, however, each pair does not always necessarily exist in both sources (sometimes it does, sometimes it doesn't). In no case should a value of X in the one source correspond to a different value for Y in the other (or vice-versa), it should always be consistent.

0 Karma

Ayn
Legend

How do you know x=1 for sourcetype BCD in myindex2 corresponds to y=dog in myindex3? What common field or similar do you have to go on?

Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.