# What is the replacement of SQL UNION in KQL?

**URL:** <https://discuss.elastic.co/t/what-is-the-replacement-of-sql-union-in-kql/275407>\
**Category:** Kibana\
**Tags:** kql-kibana-query-language\
**Created:** [June 9, 2021, 8:57am UTC](https://discuss.elastic.co/t/what-is-the-replacement-of-sql-union-in-kql/275407 "2021-06-09T08:57:23Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![VinodKumarPotta](https://avatars.discourse-cdn.com/v4/letter/v/bbce88/32.png) [@VinodKumarPotta](https://discuss.elastic.co/u/VinodKumarPotta)\
**Post date:** [June 9, 2021, 8:57am UTC](https://discuss.elastic.co/t/what-is-the-replacement-of-sql-union-in-kql/275407/1 "2021-06-09T08:57:24Z")

</div>

Hi Team,

Would like to check in this forum, Is there anyway that we can achieve UNION by using KQL?  
I have a requirement where I would need to build a Kibana dashboard with UNION of multiple sub-queries.

Thank you!

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [June 9, 2021, 9:25am UTC](https://discuss.elastic.co/t/what-is-the-replacement-of-sql-union-in-kql/275407/2 "2021-06-09T09:25:51Z")

</div>

I don't think KQL is the right answer here - it's a rough comparison, but it's basically just the `WHERE` part of a SQL query (The concepts of `SELECT`, `GROUP BY` and so on are managed by the individual visualizations).

Can you explain what you want to do exactly?

---

<div class="post-metadata">

**Author:** ![matriv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matriv/32/43656_2.png) [@matriv](https://discuss.elastic.co/u/matriv)\
**Post date:** [June 10, 2021, 1:16pm UTC](https://discuss.elastic.co/t/what-is-the-replacement-of-sql-union-in-kql/275407/3 "2021-06-10T13:16:16Z")

</div>

There is no support for `UNION` or `UNION ALL` in ES-SQL at the moment.  
The only workaround is to query multiple indices with a pattern, e.g.:

```auto
SELECT * FROM "idx-*" WHERE ...

```

But this means that you have indices with compatible fields (same name and compatible data types), and that you apply the exact same WHERE clause, ORDER BY, etc. There is currently no support of UNIONing subqueries.

---

<div class="post-metadata">

**Author:** ![VinodKumarPotta](https://avatars.discourse-cdn.com/v4/letter/v/bbce88/32.png) [@VinodKumarPotta](https://discuss.elastic.co/u/VinodKumarPotta)\
**Post date:** [June 15, 2021, 6:48am UTC](https://discuss.elastic.co/t/what-is-the-replacement-of-sql-union-in-kql/275407/4 "2021-06-15T06:48:19Z")

</div>

> [@VinodKumarPotta](#):
>
> UNION

I have a single metricbeat index in which I have two records one record contains CPU metrics and another record contains memory metrics.  
We want to show the server with more than 60% of memory or CPU utilization in a single data table.  
Is this possible to achieve?

```auto
Timestamp HostName CPU Memory
15-06-2021 15:20:00 <HOSTNAME> 70% 80%

```

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [June 15, 2021, 7:07am UTC](https://discuss.elastic.co/t/what-is-the-replacement-of-sql-union-in-kql/275407/5 "2021-06-15T07:07:48Z")

</div>

This should work with the basic tools available in Kibana:

- Create an index pattern which includes the indices in which CPU and memory metrics are stored
- Create a new Lens visualization and switch to data table
- For rows, use a date histogram on your time field and top values of the host name
- For metrics, use average of CPU and memory fields

Even if it's different documents containing cpu and memory data it's still possible to select them - the average calculation will simply ignore the empty fields

---

<div class="post-metadata">

**Author:** ![VinodKumarPotta](https://avatars.discourse-cdn.com/v4/letter/v/bbce88/32.png) [@VinodKumarPotta](https://discuss.elastic.co/u/VinodKumarPotta)\
**Post date:** [June 15, 2021, 7:39am UTC](https://discuss.elastic.co/t/what-is-the-replacement-of-sql-union-in-kql/275407/6 "2021-06-15T07:39:32Z")

</div>

Noted with thanks for prompt reply Joe.  
Will try this and get back to you incase of any help.

---

<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:** [July 13, 2021, 7:40am UTC](https://discuss.elastic.co/t/what-is-the-replacement-of-sql-union-in-kql/275407/7 "2021-07-13T07:40:09Z")

</div>

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