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
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.
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.
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
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.
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 *