# Match Query for 2 Fields from 2 Indices (Like SQL Inner Join)

**URL:** <https://discuss.elastic.co/t/match-query-for-2-fields-from-2-indices-like-sql-inner-join/310990>\
**Category:** Elasticsearch\
**Tags:** eql-elastic-query-language\
**Created:** [July 29, 2022, 1:48pm UTC](https://discuss.elastic.co/t/match-query-for-2-fields-from-2-indices-like-sql-inner-join/310990 "2022-07-29T13:48:25Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![codewriterguy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/codewriterguy/32/113440_2.png) [@codewriterguy](https://discuss.elastic.co/u/codewriterguy)\
**Post date:** [July 29, 2022, 1:48pm UTC](https://discuss.elastic.co/t/match-query-for-2-fields-from-2-indices-like-sql-inner-join/310990/1 "2022-07-29T13:48:25Z")

</div>

Hi,

I am wondering if it is possible to query ES for matches on values in 2 fields from 2 indexes, so that I can use resulting values from both events in visualizations.

I've set up indicator match rules via Kibana and I know this will generate alerts when there is a match between values in the target index and the threat intel index [Create a detection rule | Elastic Security Solution [8.3] | Elastic](https://www.elastic.co/guide/en/security/current/rules-ui-create.html#:~:text=Indicator%20match%3A%20Creates%20an%20alert,a%20value%20in%20the%20index).

I also have set up ingest pipelines using the enrich processor and I know this will do what I am looking for at ingest time. [Set up an enrich processor | Elasticsearch Guide [8.3] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/enrich-setup.html)

Similarly, Logstash has the Elasticsearch filter plugin, and this works similarly to the enrich processor, albeit with generally worse performance [Elasticsearch filter plugin | Logstash Reference [8.3] | Elastic](https://www.elastic.co/guide/en/logstash/current/plugins-filters-elasticsearch.html#plugins-filters-elasticsearch-query)

However, what I am asking is, can I construct an Elasticsearch query to find matches between values in 2 fields from 2 indexes, and yield 1 event for each match, containing the values from both matching events?

I have seen posts like this, but they are old (this one is from 2017) and I know Elasticsearch is changing rapidly [How to fetch data from multiple index using join like sql](https://discuss.elastic.co/t/how-to-fetch-data-from-multiple-index-using-join-like-sql/106131)

Thank you

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [August 4, 2022, 7:05am UTC](https://discuss.elastic.co/t/match-query-for-2-fields-from-2-indices-like-sql-inner-join/310990/2 "2022-08-04T07:05:43Z")

</div>

> [@codewriterguy](#):
>
> However, what I am asking is, can I construct an Elasticsearch query to find matches between values in 2 fields from 2 indexes, and yield 1 event for each match, containing the values from both matching events?

Elasticsearch still does not support joins so the posts you have linbked to are as far as i know still accurate.

---

<div class="post-metadata">

**Author:** ![codewriterguy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/codewriterguy/32/113440_2.png) [@codewriterguy](https://discuss.elastic.co/u/codewriterguy)\
**Post date:** [August 10, 2022, 7:40pm UTC](https://discuss.elastic.co/t/match-query-for-2-fields-from-2-indices-like-sql-inner-join/310990/3 "2022-08-10T19:40:45Z")

</div>

Thank you @Christian_Dahlqvist. That is unfortunate. It seems the best way to do any sort of lookup will be with enrich processor or Kibana rules. Out of curiosity do you know any other Kibana or Elastic Security features that will do this?

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [September 7, 2022, 7:41pm UTC](https://discuss.elastic.co/t/match-query-for-2-fields-from-2-indices-like-sql-inner-join/310990/4 "2022-09-07T19:41:11Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.