ES|QL IN Subquery: Keep only the rows that have a field matching a reference dataset

Inner Join / Subquery WHERE IN / NOT IN

Value Proposition

A common use case is to filter a left dataset (logs, events) by a reference dataset that is either a stored list (Value List or Lookup Index of known bad IPs, for example) or a dynamic list resulting from another query. The first is currently possible with ES|QL but is cumbersome - lookup indices need to have at least two fields even if the reference dataset only has one. Empower security analysts with direct and intuitive ways to correlate security events and enforce dynamic filtering policies using inner joins against custom lists of entities (e.g., malicious IPs, known good hosts) directly within Elasticsearch queries. This will streamline threat hunting, enhance detection rules, and unify data management, eliminating the need for cumbersome workarounds and improving analyst efficiency.

Expected outcomes

- Enhanced Ad-hoc Threat Hunting & Detection Rules: Security users will be able to perform highly specific filtering and correlation (e.g., FROM logs | WHERE source.ip NOT IN ()) for ad-hoc threat hunting and within scheduled detection rules, exceptions, and rule actions. This provides powerful new capabilities beyond single-value list lookups.
- Intuitive "IN/NOT IN" Functionality in ES|QL: Introduce native IN and NOT IN functions within ES|QL for efficient inner joins with single and multi-column lookup indexes. This will simplify common filtering patterns, making queries more readable and easier to construct.
- Streamlined Migration Path for Value Lists: Enable the seamless migration of existing "Value Lists" functionality to leverage lookup indexes behind the scenes. This unified approach will simplify the underlying architecture and provide a more powerful, flexible solution without negatively impacting the user experience.
- Unified Management of Reference Data: Provide a consolidated experience for managing reference data (e.g., malicious entity lists, shared exception lists) through APIs and the UI.
- Simplified Data Models and Queries: Reduce the need for complex data denormalization or application-side logic to achieve relational filtering. Analysts will be able to directly join event data with dynamic entity lists, leading to simpler, more performant queries and a cleaner data architecture.
- Broader Security Use Cases: Unlock new possibilities for enriching and filtering security data, enabling more sophisticated threat detection strategies and faster incident response by easily correlating events with dynamic intelligence or organizational context.