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