topic Re: Outer join not working in Splunk Search

Outer join not working

fvarela — Sun, 19 Sep 2021 09:51:01 GMT

It seem that outer join is not working for me and I have no idea why.
I have this two events:

Event 1 (index="faults"):
Id = a8015353-18bf-11ec-8b0a-7c2a311251af
AxesId = a7ba0fd6-18bf-11ec-b369-7c2a311251af
TR = 3

Event 2 (index="axes"):
id = a8015354-18bf-11ec-b3bb-7c2a311251af
parent_id = a8015353-18bf-11ec-8b0a-7c2a311251af
table= 10
couch= 30

My main search retrieves Event 1.
I want to use an outer join to retrieve 'table' and 'couch' from Event2. I have two choices to join the events. I have tried both, didn't work:

Event1 AxesId is Event2 id
Event1 Id is Event2 parent_id

This is my query:

index="faults" Id=a8015353-18bf-11ec-8b0a-7c2a311251af | join type=outer AxesId [search index="axes" | rename id AS AxesId] | table *

And this is the output table.

Id	AxesId	TR	table	couch
a8015353-18bf-11ec-8b0a-7c2a311251af	a8015354-18bf-11ec-b3bb-7c2a311251af	3

Event 2 columns are there but have no information.
Any help would be welcomed.
Thanks

Re: Outer join not working

richgalloway — Sun, 19 Sep 2021 13:11:07 GMT

That looks like it should work so I don't understand why it isn't. Try this alternative.

(index="faults" Id=a8015353-18bf-11ec-8b0a-7c2a311251af) OR (index="axes") | eval Id=coalesce(Id, AxesId] | stats values(*) as * by Id | table *

Re: Outer join not working

fvarela — Sun, 19 Sep 2021 13:27:34 GMT

Thanks for your help richgalloway but no, it is not working yet.
The output table only has elements from event1

If I replace your code with this (Note that event1 has 'Id' field with capital I while event2 has 'id'):

(index="faults" Id=a8015353-18bf-11ec-8b0a-7c2a311251af) OR (index="axes") | eval Id=coalesce(Id, id) | stats values(*) as * by Id | table *

Then it outputs a table with:
index "faults" -> event1
index "axes" -> all the events on this index

Re: Outer join not working

richgalloway — Sun, 19 Sep 2021 14:19:35 GMT

Perhaps I got the fields confused. The field in the by clause of the stats command must be one that contains a value shared by events in both indexes.

Re: Outer join not working

fvarela — Sun, 19 Sep 2021 19:09:52 GMT

That id vs Id capitalization issue was driving me nuts so I fix it so that all the fields are extracted using the same convention (lower case with _ for separating words).
That fixed somehow my issue with the outer join.
Now this query works as expected:

index="faults" id=3bdbced1-1958-11ec-894e-7c2a311251af |join type=outer axes_id [search index="axes" | rename id as axes_id] |table tr, table, couch

So the issue is solved but I don't really know what was wrong with the first query.