Outer join not working

[fvarela]

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

[fvarela · Accepted solution]

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.

[fvarela]

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

[richgalloway]

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.

[richgalloway]

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 *