SQL "WHERE" query in Kibana

Elastic Stack Kibana
1

Hi,
I have a sample CSV data of the form below:

ID, related ID, Event name, Date
1, 1001;1002, ABC,2016/01/01
1001, 1;1002, DEF,2016/01/02
1002, 1;1001, XYZ,2016/01/03

What I want as Output is:

ID, Event Name, Related Events, Date
1,ABC,DEF;XYZ,2016/01/01;2016/01/02;2016/01/03

So I want to fetch the records of "related ID" and display its corresponding Event name. This can be easily done in SQL. Any way to do the same in ES/Kibana?

Thanks,
Meenal

2

Hi Meenal,

I don't understand your CSV data. It looks like the first "parent" row already has a reference to the related "child" rows, and the "child" rows have a reference to the "parent" and each other? In that case you don't need to do any kind of SQL join. You can just filter to get the rows where relatedID:1?

Kibana doesn't have join capabilities. And in fact, Elasticsearch guides explain why you shouldn't try to use it for that kind of relationship data. There are some join type functions in Elasticsearch but they are not used by Kibana.

I would suggest reviewing this;
https://www.elastic.co/guide/en/elasticsearch/guide/current/relations.html
(has a few linked articles)

and
https://www.elastic.co/guide/en/elasticsearch/reference/current/joining-queries.html

Regards,
Lee