ESQL: Joining with sub-search results

[getkub]

Description

ESQL needs ability to join with other set of data at search-time

So the functionalites similar to join command in Splunk But better to follow the standards of SQL (like inner join, outer join, left outer join etc) concept to combine two sets of data

An example would be

FROM employees
| WHERE emp.salary > 50000
| join type=left_join ON manager.id [| from managers | keep manager.id , manager.name, manager.salary]
| keep employee.name, manager.name, manager.salary

[elasticsearchmachine]

Pinging @elastic/es-ql (Team:QL)

[elasticsearchmachine]

Pinging @elastic/elasticsearch-esql (:Query Languages/ES|QL)

[brett-fitz]

This would be extremely useful in cases where you can't setup an enrich policy (volatile data).

Example User Stories from a Security Analyst:

As a security analyst, I want to join windows process logs with zeek logs to identify where traffic came from. As a security analyst, I want to join network security events with firewall logs to determine if the traffic was blocked. As a security analyst, I want to join windows security logs with security events to further enrich an event. As a security analyst, I want to join security events with host information to further enrich an event. As a security analyst, I want to join security events with threat intelligence to further enrich an event.

[brett-fitz]

@JVerwolf any status update here?

[elasticsearchmachine]

Pinging @elastic/es-analytics-geo (Team:Analytics)

[brienpacholec]

Are there any status updates on this yet?

[getkub]

Are there any status updates on this yet?

I also felt the same. I've put in quite few priority items useful, but seems none of them are worked upon. its a shame. https://github.com/elastic/elasticsearch/issues/created_by/getkub - none of them worked upon .. i feel there is no point in raising feature request here

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[wchaparro]

Hey there @getkub we hear ya! The team is working hard to get ES|QL to GA and so we've had our heads down. We've got these items in our backlog for review. Thanks for your interest in ES|QL!

[nicpenning]

👀