Threat hunting in Elastic with JOINs!

Elastic has been providing comprehensive search capabilities to help users investigate security events, analyze logs, and explore data at scale. One capability plays a key role in advancing detection and investigation: JOINs — because context is crucial.

JOINs allow analysts, detection engineers, and threat hunters to create more  context-aware logic on top of condition-based queries. Consider the following questions: In which department does this person work, and does their job require them to run PowerShell? Does this malware event involve a critical user, like a Domain Administrator? Is this phishing campaign directed at specific roles or business units? 

All of these questions rely on data outside of the alert. And JOINs are how practitioners bring that context in — connecting the dots across data sources to make smarter, faster decisions while hunting, investigating and responding.

Elastic’s existing enrichment policies have supported JOIN-like workflows for a while, but maintaining enrichment jobs and managing updates added friction, like the requirement to re-execute an enrichment policy after every update. Now, with the introduction of the LOOKUP JOIN function in ES|QL, things got a lot easier. This new function gives analysts a faster, more intuitive way to bring external data into a query. No preprocessing or external steps required — just JOIN what you need, when you need it, right in the search workflow.

Security practitioners focus on many things to protect their organization’s data. Let’s examine how joining assists in a traditional end to end workflow: (1) Finding New Suspicious Activity, (2) Triaging Detection Alerts, and (3) Conducting an Incident Response.

Threat hunters and detection engineers are protecting their systems by finding suspicious behaviors. The threat hunting workflow focuses on a scientific model to find malicious activity. First, establish a hypothesis, review your results, and determine if an incident has occurred. 

Threat hunting can include machine learning and simplistic searching, but often data stack analysis is a part of it. The Elasticsearch Query Language (ES|QL) has brought aggregations to the query alongside the ability to transform your data — and now the ability to add results from a separate data store to your results. This is why joining is so critical. Often analysts and threat hunters are juggling multiple data sources and need to correlate the data. There is likely a shared parameter, like a host, user, file, process, etc. But how do you look for lateral movement between authentication logs and process execution? How do you look for data exfiltration between net flow data and file creation? Joining allows the threat hunter to find anomalous behaviors across a multitude of data.

Next, security analysts are reviewing the output from threat hunters and detection engineers in the form of alerts. Alert triage is another use case for joining. It is well established that analysts suffer from alert fatigue from an abundance of alerts. Yes, Elastic is solving this issue through generative AI and Attack Discovery alongside a context-enriched UX, but sometimes it is necessary to prioritize from an external or additional data source. Joining plays a role here, too. Now, a user can compare alert metadata to threat intelligence feeds, prioritizing those alerts with known malicious indicators. Or what about alerts from a specific asset inventory? Now an analyst can prioritize business elements that deal with a majority of external communication and are targets for phishing attempts.

Incident response is often about filling in the blanks. Early in an investigation, responders are working with fragments of the complete story being told with findings attached to an active Case. That’s where JOINs become essential. During incident response, teams need to connect information across systems — authentication logs, endpoint telemetry, network data, threat intel, asset inventory — and correlate it in real time. Without JOINs, responders are stuck stitching things together manually or jumping between tools. Using LOOKUP JOINs, incident responders can:

• Enrich alerts with asset ownership or sensitivity level

• Correlate endpoint activity with known malware indicators

• Trace a user's movement across multiple systems from a single query

It’s a faster path to understanding root cause and coordinating next steps.

Before diving into some security example queries, let’s first review how to use this new ES|QL function. ES|QL is a piped language that allows the user to build a series of operations within a single search while filtering, transforming, and aggregating data. Joining itself was previously solved using enrichment policies or the ENRICH command with ES|QL. Although these solved some joining use cases, our users needed an easier path to joining data without requiring enrichment configurations.

The new join capability occurs when using the LOOKUP JOIN function. Here you are able to establish a lookup index, which will add data to your search results depending on the join key. As you compose your query using the ES|QL pipes, you can simply establish the lookup to add results to your current query and effectively join two data sources. For more information, please consult our documentation or additional content, Joins are here!

Here’s a simple security use case with LOOKUP JOINs. Let’s look for Elastic Security alerts that contain known threat feed indicators. In this scenario, our threat feed data store will be called “threat-match.”

To truly flex the power of ES|QL and the LOOKUP JOIN, let’s revisit the Security scenarios where joining would prove most effective.

Firstly, using the AI Assistant can always help. We can ask the Assistant to first explain more about ES|QL LOOKUP JOINs and then ask for help enriching alerts with the current user risk score. Let’s check it out!

Let’s continue with the alert triage use case. When viewing security alerts, analysts are always looking for ways to prioritize, so why not look for those users with privileged access? We will look for alerts on Windows systems and then lookup results in the Entity Analytics Active Directory integration to see privileged group members.