elasticsearch
elasticsearch copied to clipboard
elastic
ESQL: Joining with sub-search results
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
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.
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
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!
Hey everyone, thanks for your feedback and patience. This request is aligned with capabilities that we intend to add to the product in the future. However, design and development work have not yet begun, and we are unable to provide an estimated timeframe in which this capability might become available. We've added this feature request to our internal planning process, and we're starting to put some of the foundational pieces in place for various joins with a new Lookup Join feature. Lookup Join is similar to a SQL Left Outer join and can be used against any lookup index (a new index mode).
Please feel free to continue adding example use cases here as it helps us to refine the requirements for this subquery/subsearch feature, to ensure we make something truly useful. Thanks
