Canvas: Error empty database from essql query

[esther.k (Esther Kim)]

Hi,
I saw something weird in my Canvas.

There is a markdown element represents alert count.
I wrote Elasticsearch sql to query alert count like this:

filters
| essql 
  query="SELECT \"@timestamp\", prometheus.query.alert_warning_count as warn FROM \"metricbeat-*\" WHERE prometheus.query.alert_warning_count IS NOT NULL AND \"@timestamp\" >= NOW()-INTERVAL 5 MINUTES ORDER BY \"@timestamp\" DESC LIMIT 1"
| math "warn"
| markdown "{{warn}}" | render

This query gave an "Empty Database" error to me in Canvas.
However, it works well when I tried with same query in Kibana Dev Tool Console.
(FYI, I used "GET _sql?format=txt" API)

Please advice me.

[Marius_Dragomir]

Hello,
Here are some examples that you can use as format for the essql queries: https://github.com/alexfrancoeur/kibana_canvas_examples/tree/master/presentations/apm_metrics_logs
I'm not that knowledgeable in SQL, but these should be able to help.