Canvas Chained Elasticsearch SQL Queries

[wmcleod (William McLeod)]

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

[Andrei_Stefan]

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).

[wmcleod (William McLeod)]

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