# Canvas Chained Elasticsearch SQL Queries

**URL:** <https://discuss.elastic.co/t/canvas-chained-elasticsearch-sql-queries/199274>\
**Category:** Elasticsearch\
**Created:** [September 12, 2019, 2:36pm UTC](https://discuss.elastic.co/t/canvas-chained-elasticsearch-sql-queries/199274 "2019-09-12T14:36:01Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![wmcleod](https://avatars.discourse-cdn.com/v4/letter/w/ebca7d/32.png) [@wmcleod](https://discuss.elastic.co/u/wmcleod)\
**Post date:** [September 12, 2019, 2:36pm UTC](https://discuss.elastic.co/t/canvas-chained-elasticsearch-sql-queries/199274/1 "2019-09-12T14:36:01Z")

</div>

Hi,

I am trying to create a canvas visualisation that displays a line chart with data for the last 1 hour containing CPU metrics only from servers that have breached a CPU threshold at least once in the last 1 hour.

I am using the following essql query to get a list of the threshold breaching servers:

**POST \_sql?format=txt**  
**{**  
\*\* "query":"SELECT agent.hostname FROM "metricbeat\*" WHERE system.cpu.total.norm.pct \> 0.85 AND "@timestamp" \> CURRENT\_TIMESTAMP - INTERVAL 1 HOUR GROUP BY agent.hostname" \*\*  
**}**

It returns a list of the breaching servers (in the last hour):

\*\*agent.hostname \*\*  
**---------------**  
**server1**  
**server3**  
**server8**

This was my attempt at returning the CPU metric data for these servers using an IN statement:

**POST \_sql?format=txt**  
**{**  
\*\* "query":"SELECT "@timestamp" timestamp,agent.hostname hostname,system.cpu.total.norm.pct cpu\_pct FROM "metricbeat\*" WHERE system.cpu.total.norm.pct \> 0 AND "@timestamp" \> CURRENT\_TIMESTAMP - INTERVAL 1 HOUR AND hostname IN(SELECT agent.hostname FROM "metricbeat\*" WHERE system.cpu.total.norm.pct \> 0.85 AND "@timestamp" \> CURRENT\_TIMESTAMP - INTERVAL 1 HOUR GROUP BY agent.hostname)"\*\*  
**}**

This returns the not supported yet error:

**{**  
\*\* "error": {\*\*  
\*\* "root\_cause": [\*\*  
\*\* {\*\*  
\*\* "type": "parsing\_exception",\*\*  
\*\* "reason": "line 1:204: IN query not supported yet"\*\*  
\*\* }\*\*  
\*\* ],\*\*  
\*\* "type": "parsing\_exception",\*\*  
\*\* "reason": "line 1:204: IN query not supported yet"\*\*  
\*\* },\*\*  
\*\* "status": 400\*\*  
**}**

Is there any other way to achieve what I am looking to do maybe by chaining multiple essql queries in the expression editor in canvas?

Regards,

William

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [September 13, 2019, 5:32am UTC](https://discuss.elastic.co/t/canvas-chained-elasticsearch-sql-queries/199274/2 "2019-09-13T05:32:24Z")

</div>

`IN` was added in ES-SQL in 6.5.0 and 7.0.0, but the query you are attempting will not probably work even if `IN` was supported. The supported `IN` is for a static list of values: `IN(123,456,null,10)`.

---

<div class="post-metadata">

**Author:** ![wmcleod](https://avatars.discourse-cdn.com/v4/letter/w/ebca7d/32.png) [@wmcleod](https://discuss.elastic.co/u/wmcleod)\
**Post date:** [September 13, 2019, 7:04am UTC](https://discuss.elastic.co/t/canvas-chained-elasticsearch-sql-queries/199274/3 "2019-09-13T07:04:54Z")

</div>

Hi Andrei,

Thanks for your reply.

Is there any other way to construct a search that will return all documents in the last hour for servers that have breached a CPU threshold in the last hour?

Regards,

William

---

<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:** [October 11, 2019, 7:08am UTC](https://discuss.elastic.co/t/canvas-chained-elasticsearch-sql-queries/199274/4 "2019-10-11T07:08:12Z")

</div>

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