By Paul Ewing and Jonhnathan Ribeiro
29 April 2025

Exciting news for threat hunters: Elastic has unveiled a groundbreaking feature in its piped query language, ES|QL (Elasticsearch Query Language), allowing users to join data sources seamlessly. This enhancement promises to facilitate robust searches that span from advanced behavior detections to effective alert triage, making it a valuable tool for threat hunting.

Understanding the significance of JOINs in the realm of cybersecurity is crucial. Elastic has long been known for its robust search capabilities that enable users to investigate security events and analyze logs effectively. The introduction of JOINs is a game changer, providing essential context that enhances the quality of investigations. For instance, consider critical questions such as: Which department employs a specific user, and is their role one that necessitates running PowerShell? Is the malware event linked to a high-privilege user, like a Domain Administrator? Or is a phishing campaign aimed at particular roles within the organization? These inquiries depend on data that exists outside of the immediate alerts, and JOINs are the mechanism through which practitioners can bring that vital context into their analyses. By connecting disparate data sources, analysts can make more informed and rapid decisions during their investigations.

The evolution of this feature is best demonstrated through the new LOOKUP JOIN function in ES|QL. Although Elastic had previously supported JOIN-like workflows via enrichment policies, the requirement to manage and re-execute these policies added unnecessary complexity. The LOOKUP JOIN function streamlines this process, allowing analysts to incorporate external data into their queries with ease. This means no more lengthy preprocessing or cumbersome external steps; users can now JOIN exactly what they need, precisely when they need it, within their search workflows.

Security analysts deal with a multitude of responsibilities to safeguard their organization's sensitive data. The application of JOINs can significantly enhance traditional security processes from start to finish:

1. Finding the Threat: The initial step in threat hunting involves establishing a hypothesis, searching for evidence, and correlating findings to determine whether an incident has occurred. This process may combine machine learning with traditional searching methods, but it frequently requires comprehensive data stack analysis. The integration of aggregations and the ability to JOIN data from other sources is critical for identifying anomalous behaviors across various data sets. For example, how does one investigate lateral movement between authentication logs and process execution? Or explore data exfiltration between net flow data and file creation? JOINs facilitate the discovery of these patterns and relationships.
2. Triaging Alerts: After threat hunters and detection engineers identify suspicious activities, security analysts must sift through the generated alerts. Alert fatigue is a well-documented challenge in cybersecurity, exacerbated by an overwhelming number of alerts. Elastic's integration of generative AI and context-rich user experiences aims to remedy this issue, but the ability to prioritize alerts based on external data sources further enhances this process. With JOINs, analysts can cross-reference alert metadata with threat intelligence feeds, allowing them to focus on alerts linked to known malicious indicators. Furthermore, this feature enables analysts to prioritize alerts associated with high-risk assets that frequently engage in external communications, which may be vulnerable to phishing attempts.
3. Responding to Incidents: The incident response phase often requires responders to piece together fragmented information. During investigations, responders gather data from various sources, including authentication logs, endpoint telemetry, network data, and threat intelligence, and must correlate this information in real-time. JOINs are indispensable in this context, as they allow responders to enrich alerts with relevant data, such as asset ownership or sensitivity level, correlate endpoint activities with known malware indicators, or track a user's activities across multiple systems through a single query. This capability significantly expedites the process of understanding the root cause of an incident and coordinating subsequent actions.

The syntax for the LOOKUP JOIN in ES|QL is designed to provide users with an intuitive way to enhance their search queries. ES|QL is a piped query language that allows users to build comprehensive search operations while filtering, transforming, and aggregating data. Previously, joining data required the use of enrichment policies or specific commands, which may not have catered to all user needs. The new LOOKUP JOIN function simplifies this process. Users can establish a lookup index, allowing additional data to be incorporated into search results based on defined join keys. As users construct their queries, they can seamlessly integrate the lookup to augment their current data, effectively joining multiple data sources.

As an example, users can create queries that seek Elastic Security alerts containing indicators from a known threat feed. Imagine a data store named threat-match that holds this crucial information. A sample query would look like:

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

The results generated by this query will not only include standard alert metadata such as rule content, severity, and user information, but will also enrich the findings with valuable insights from the threat feed, including the name, value, type, and description of each indicator.

Additionally, users can leverage Elastic's AI Assistant to navigate the functionality of LOOKUP JOINs and enrich alerts with user risk scores, making the experience even more user-friendly. For instance, analysts could focus on Windows system alerts, cross-referencing them with privileged access data from the Entity Analytics Active Directory integration to hone in on critical user groups.

As threat hunters often seek to identify local processes that could be exploited, the introduction of LOOKUP JOINs allows for more effective searches of 'living off the land' techniques. These processes, which are typically benign, can also be used by malicious actors to evade detection. By creating a lookup index for these processes, analysts can continuously update their findings without the need for reworking their queries.

In conclusion, the introduction of LOOKUP JOINs marks a significant advancement in Elastic's suite of security tools. This feature, akin to a SQL-style LEFT OUTER JOIN, enhances user experience and sets the stage for further developments in joining capabilities, such as INNER joins. Elastic's commitment to improving its offerings through AI technology, machine learning, and advanced analytics tools makes it an indispensable resource for security analysts. For the most seamless experience with Elastic Security, users are encouraged to take advantage of Elastic Cloud Serverless. The release timelines for specific features remain at Elastic's discretion, and users should remain informed about the latest updates and functionalities. As a reminder, when utilizing third-party generative AI tools, its essential to understand their privacy policies and the potential use of submitted data.