ES|QL Joins are here! Yes, Joins!

Elasticsearch 8.18 includes our first SQL-style JOIN: ES|QL’s LOOKUP JOIN command. It is now available in tech preview and enables data correlation and enrichment with easily updatable lookup datasets. Need to add host and asset info to your events? No problem. Want to check which IP addresses or URLs are on threat intelligence lists? Sure! Make updates to the lookup dataset and use them immediately? Go for it!

Lookup Join is a SQL-style LEFT OUTER JOIN that relies on a new index mode called lookup for the right side. The lookup index could be assets, threat intel data like known-bad IPs, order info, employee or customer info — there are infinite possibilities.

Historically Elasticsearch lacked join capability, although over time a few attempts were made in that direction, like nested and _parent and join field types, and enrich. We typically encouraged the denormalization of data by putting the reference data alongside the events in the same index, or client side joins. These solutions hamper scalability for larger data volumes and can be limiting when there's a high variety of data and use cases. It’s time for Elasticsearch to have joins. And now it’s possible: ES|QL isn’t just a language — it’s built on top of a new compute engine. The ES|QL engine makes it possible to do advanced features like join.

To enable Lookup Joins, we created a new index mode: lookup. A lookup index is mostly like a regular index (mode:standard is the default), which means it’s directly updateable. The main difference is a lookup index is only one shard. Thus, it is limited to 2 billion documents, a Lucene limit for one shard. That should be more than enough to hold the “right side” data for quite a few of the many lookup join use cases. Imposing this limit helps us avoid some of the problems associated with fully distributed joins by reducing the source cardinality of the right side of the join. This also tells us which datasets you’re planning to use as lookup references so we can find ways to optimize them in the future. 

Other than the lookup index mode, there are no restrictions on the source data nor any data preparation required to perform a join. That means it's easy to add lookup datasets, and it’s easy to keep them up-to-date. 

Some of this has already been possible with the ENRICH command in ES|QL. ENRICH was optimized for ingest time lookups in ingest pipelines, and small datasets that don’t change often, so it was convenient but not ideal. Lookup Join is easier to set up and manage than enrich: 

• No enrich policies to create

• No policy execution

• Lookup indices are writeable

• There are fewer copies of the data

• Better handling of multiple matches — whereas enrich creates multi-valued fields when there are multiple matches, lookup join creates multiple rows. This makes it easier to do further analytics like grouping and aggregating, such as bytes transferred per user or order totals.

• Lookup Join allows on-the-fly specification of the match field

Here are just a few of the ways you might use a LOOKUP JOIN: 

• Join security events with threat intelligence to further enrich an event

• Lookup my current set of hosts vs. latest threat intel or internal metadata

• Correlate IOT readings with static or dynamic metadata (manufacturer, unit of volume)

• Join security events with host information for more context and filtering

• Add some facts like asset criticality (this user is an executive)

• Add in CrowdStrike AID's to a source that has hostnames

• Check if certain ip addresses are in threat intel feeds

• Add employee information to events

The possibilities are endless.

Now add the LOOKUP JOIN on the environments lookup index (mine is called envs_lkp):

There are errors in Production and Quality Assurance (QA). We can join against a list of which team owns each web server to see who we should contact:

Security analysts love their joins! Maybe they’ll want to:

• Identify client IPs that are requesting known bad URLs

• Correlate with asset information to group events and assign priority or risk

You could download a list of malicious URLs from URLhaus at Abuse.ch and upload it to a lookup index.

You’ll need at least one lookup index to use lookup join. We’re designing ways to make it easy and convenient to create and update lookups in Kibana. For now, you can start by creating a lookup index in a few ways — the key detail is that the index mode must be “lookup.”

Provide an index name, and select “Lookup” from the Index mode dropdown:

Click Create to make an empty lookup index. 

In the left navigation pane of Kibana, click Machine learning, and then File data visualizer (or type “upload” in Kibana’s search bar).

Select or drag and drop your file — CSV works well for this.

After you click Import, provide a name for the index, and click Advanced to reveal Index settings. Here, you’ll want to add the index.mode: lookup.

Complete the File upload by clicking Import. An index and a data view will be created. 

In all cases, you can now reference the lookup index in your ES|QL queries.

Or, create a data view. 

Q: Can I send data from Logstash or agent?
Yes! Just keep in mind that a lookup index is not a data stream and it’s just one shard, so you can “only” send up to 2 billion documents to each lookup index.

And lookup joins are just the beginning. We’d like to offer other useful types of joins, like INNER joins or subqueries, and also allow joining against any index (not just lookup indices).