Elastics Piped Query Language, ES|QL, Introduces JOINs for Enhanced Data Integration

By Paul Ewing and Jonathan Ribeiro | April 29, 2025

In a groundbreaking development for cybersecurity professionals, Elastic has introduced a robust feature that promises to revolutionize the way threat hunters and security analysts interact with data. The newly added JOIN functionality in Elastic's piped query language, ES|QL (Elasticsearch Query Language), empowers users to seamlessly connect various data sources, enabling advanced searches that span from intricate behavior detections to efficient alert triage and, notably, threat hunting.

Understanding the Importance of JOINs in Security

Context is everything in the realm of cybersecurity. Elastic has long been a leader in providing comprehensive search capabilities that assist users in investigating security incidents, analyzing extensive logs, and exploring vast datasets. The introduction of JOINs enhances these capabilities by allowing analysts, detection engineers, and threat hunters to craft logic that is far more context-aware than conventional condition-based queries. For instance, analysts can now assess critical questions: What department does a user belong to? Is that users role one that typically requires the execution of PowerShell scripts? Does a specific malware event involve a high-profile user, such as a Domain Administrator? Are phishing campaigns targeted at specific business units? All of these inquiries necessitate data that extends beyond the alert itself, and JOINs are the bridge connecting this vital context across diverse data sources, facilitating more intelligent and timely decision-making during investigations.

The Evolution of JOINs: Introducing LOOKUP in ES|QL

While Elastic has previously offered enrichment policies that mimicked JOIN functionalities, these workflows often presented challenges, such as the need to re-execute enrichment jobs following updates. With the new LOOKUP JOIN function in ES|QL, the process has been streamlined significantly. Analysts can now incorporate external data directly into their queries without the cumbersome preprocessing steps or external requirements. This innovation allows practitioners to JOIN necessary data intuitively and quickly, directly in their search workflows.

Supporting Security Analysts Through Every Step of the Process

Security analysts have a multitude of responsibilities when it comes to safeguarding their organizations data. The implementation of JOINs aids in several critical stages of the threat management workflow, namely: finding new suspicious activities, triaging detection alerts, and responding to incidents.

• 1. Identifying Threats: Formulating Hypotheses and Correlating Data

Threat hunters and detection engineers dedicate their efforts to identifying suspicious behaviors that may indicate malicious activity. The threat hunting workflow employs a scientific approach, where practitioners establish hypotheses, review data outputs, and determine whether an actual incident has occurred. Effective threat hunting often involves a combination of machine learning and traditional searches, and the ability to analyze multiple data stacks simultaneously enhances this process significantly.

The Elasticsearch Query Language (ES|QL) has not only introduced aggregations to queries but has now enabled users to enrich results by incorporating data from various external data stores. This capability is crucial for analysts and threat hunters who routinely manage multiple data sources, as it allows them to identify shared parameterssuch as hosts, users, files, or processesand uncover lateral movements or data exfiltration without the hassle of toggling between disparate tools.

• 2. Triage Alerts: Prioritization Made Easier

Once suspicious activity has been identified, security analysts must sift through outputs generated by threat hunters and detection engineers in the form of alerts. Alert triage is a well-known challenge given the overwhelming volume of alerts that analysts face, often leading to alert fatigue. To combat this issue, Elastic is not only leveraging generative AI and Attack Discovery to enhance user experience but also refining the process of prioritization using additional external data sources. The ability to JOIN alert metadata with threat intelligence feeds enables analysts to focus on alerts that contain known malicious indicators or prioritize alerts linked to high-value assets, ensuring that they are addressing the most critical threats first.

• 3. Incident Response: A Complete View for Rapid Action

In the fast-paced world of incident response, teams often find themselves with fragmented information at the outset of an investigation. This is where the JOIN functionality becomes indispensable. During active investigations, responders need to link data from various systemssuch as authentication logs, endpoint telemetry, network data, threat intelligence, and asset inventoriesin real time. Without the ability to JOIN this data, responders would be required to manually pull together insights or switch between different tools, slowing down the response process. However, with the LOOKUP JOINs capability, incident responders can enrich alerts with asset sensitivity levels, correlate endpoint activities with known malware indicators, and track user movements across multiple systems, all from an integrated query. This streamlined approach leads to a quicker understanding of root causes and facilitates more effective coordination of subsequent actions.

Getting Familiar with LOOKUP JOIN Syntax in ES|QL

Before delving into practical security query examples, its essential to understand how to utilize this new ES|QL feature effectively. ES|QL operates as a piped language, allowing users to construct a series of operations within a single search while filtering, transforming, and aggregating data. While earlier methods, such as enrichment policies and the ENRICH command, provided limited solutions for JOINs, the new LOOKUP JOIN function enables users to create a lookup index that adds data to search results based on specified keys. By composing queries using ES|QL pipes, analysts can easily establish lookups and JOIN data from different sources seamlessly.

For instance, consider a scenario where a security analyst is looking for alerts that match known threat feed indicators. In this example, the threat feed data store is labeled as threat-match.

FROM *.alerts-security* | EVAL indicator.id = file.name | LOOKUP JOIN threat-match on indicator.id

The results will not only include standard alert metadatasuch as the rule's content, severity, and the original event detailsbut will also integrate valuable threat feed information, including indicator names, types, and descriptions.

Engaging with the Elastic AI Assistant

For users looking to navigate the capabilities of ES|QL and LOOKUP JOINs, the AI Assistant can offer significant help. Users can query the Assistant for explanations about ES|QL JOINs or seek assistance in enriching alerts based on current user risk scores. Utilizing this resource can enhance the overall security analysis process.

Practical Examples in Alert Triage and Threat Hunting

When security alerts are presented, analysts can prioritize their investigations by focusing on users with privileged access. For instance, analyzing Windows system alerts while performing lookups in the Entity Analytics Active Directory integration can highlight privileged group members of interest.

Moreover, the incorporation of MITRE ATT&CK into the analysis allows analysts to utilize Elastic Entity Risk Scoring to prioritize alerts from critical hosts that encompass multiple tactics, such as Initial Access, Persistence, and Defense Evasion.

Finally, in scenarios where alerts are absent and threat hunters need to proactively search for threats, the openness of Elastics platform provides access to a repository of detection rules, enabling users to explore threat hunting techniques effectively.

For example, a threat hunting query focusing on Windows Persistence via Run Keys can be enriched by joining asset information to bolster the investigation. By expanding a prebuilt query, analysts can refine their search parameters while adding entity lookups to reveal the asset locations of hosts under scrutiny.

Conclusion: JOINs as a Vital Tool in Security Analytics

The introduction of LOOKUP JOINs significantly enhances security workflows, providing a more comprehensive toolset for analysts. While the current JOIN functionality aligns closely with a SQL-style LEFT OUTER JOIN, Elastic is committed to continuously improving its capabilities, with plans to expand to INNER joins and more. As users navigate the evolving landscape of cybersecurity threats, this new feature serves as an invaluable addition to the security analyst's toolbox, complementing various advanced techniques enabled by Elastic's AI technologies, including generative AI, machine learning, and more. To experience the power of Elastic Security firsthand, users can explore the solutions available on Elastic Cloud Serverless. Stay tuned for more updates on these exciting developments!

Disclaimer: The release and timing of any features described in this post are at Elastic's discretion. The use of third-party generative AI tools referenced herein is subject to their respective terms and privacy practices.