# Canvas - essql failed to fetch

**URL:** <https://discuss.elastic.co/t/canvas-essql-failed-to-fetch/197508>\
**Category:** Kibana\
**Tags:** canvas\
**Created:** [August 30, 2019, 11:38am UTC](https://discuss.elastic.co/t/canvas-essql-failed-to-fetch/197508 "2019-08-30T11:38:55Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![atamarnath](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/atamarnath/32/57083_2.png) [@atamarnath](https://discuss.elastic.co/u/atamarnath)\
**Post date:** [August 30, 2019, 11:38am UTC](https://discuss.elastic.co/t/canvas-essql-failed-to-fetch/197508/1 "2019-08-30T11:38:55Z")

</div>

Hi, I am trying to change color of shape dynamically based on the RAG returned by my query. When the query does not return any record matching the where clause, I see an exclamation mark on the screen with error "essql failed to fetch". When no records are fetched i want the shape to default to GREY color.

code in expression editor

filters  
| essql "SELECT RAG.keyword AS RAG FROM "gpm-hvp\_uk\_bam\_watcher-dev\*" where Step = '4105' order by @timestamp DESC limit 1"  
| getCell column=RAG row=0  
| shape "circle" fill={ switch case={case if={eq "Red"} then="RED"} case={case if={eq "Green"} then="Green"} default="Grey"} border="rgba(255,255,255,0)" borderWidth=0 maintainAspect=true

---

<div class="post-metadata">

**Author:** ![lukeelmers](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/lukeelmers/32/35230_2.png) [@lukeelmers](https://discuss.elastic.co/u/lukeelmers)\
**Post date:** [August 30, 2019, 4:38pm UTC](https://discuss.elastic.co/t/canvas-essql-failed-to-fetch/197508/2 "2019-08-30T16:38:07Z")

</div>

Hi @atamarnath,

I'm not sure why you'd be getting `essql failed to fetch as your error` -- but one potential issue I see is that `getCell column=RAG row=0` will fail if you have no results from `essql`.

The way around this would be to check the row count and get the cell inside the `shape` function itself. Something like this:

```auto
| shape "circle" fill={if {rowCount | lt 1} then="grey" else={getCell column=RAG}} border="rgba(255,255,255,0)" borderWidth=0 maintainAspect=true

```

Maybe give this a shot and see if it helps? Note that I'm making the assumption that the values of RAG can safely be passed along as colors -- if they can't, you'll need to continue using a `switch` function as you had done originally.

---

<div class="post-metadata">

**Author:** ![atamarnath](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/atamarnath/32/57083_2.png) [@atamarnath](https://discuss.elastic.co/u/atamarnath)\
**Post date:** [September 2, 2019, 8:27am UTC](https://discuss.elastic.co/t/canvas-essql-failed-to-fetch/197508/3 "2019-09-02T08:27:39Z")

</div>

> [@lukeelmers](#):
>
> | shape "circle" fill={if {rowCount | lt 1} then="grey" else={getCell column=RAG}} border="rgba(255,255,255,0)" borderWidth=0 maintainAspect=true

It works. Thank you very much

---

<div class="post-metadata">

**Author:** ![sathish8](https://avatars.discourse-cdn.com/v4/letter/s/bc79bd/32.png) [@sathish8](https://discuss.elastic.co/u/sathish8)\
**Post date:** [September 19, 2019, 2:05pm UTC](https://discuss.elastic.co/t/canvas-essql-failed-to-fetch/197508/4 "2019-09-19T14:05:50Z")

</div>

Hi @lukeelmers,

I am also facing the same issue.

As you have suggested i tired with the below switch case i am facing the error "[shape] \> [switch] \> [case] \> [getCell] \> Column not found: 'Warning'"

Can you please help!! Thanks

{switch case={case if={rowCount | lt 1} then="green"} case={case if={getCell column=LOAD eq "Warning"} then="yellow"} case={case if={getCell column=LOAD eq "Critical"} then="red"} default="green"}

Below is the code which i used before got "essql failed to fetch",

filters  
| essql  
query="SELECT "hits.hits.fields.criticality.keyword" AS LOAD FROM "watcher-alert-index-canvas" WHERE hits.hits.\_source.application.keyword = 'xxx' AND hits.hits.\_source.beat.hostname.keyword ='xxxx'"  
|getCell column= LOAD | metric "xxxxx"  
metricFont={font size=5 family="'Open Sans', Helvetica, Arial, sans-serif" color="black" align="center" lHeight=15}  
labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"}  
| render containerStyle={  
containerStyle backgroundColor={  
filters | essql query= "SELECT "hits.hits.fields.criticality.keyword" AS LOAD FROM "watcher-alert-index-canvas" WHERE hits.hits.\_source.application.keyword = 'xxx' AND hits.hits.\_source.beat.hostname.keyword ='xxxxxx'"| getCell column= LOAD | switch case={case if={all {eq 'Warning'}} then="#ffbf00"} case={case if={eq 'Critical'} then="red"} default="green" }  
}

---

<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 17, 2019, 2:06pm UTC](https://discuss.elastic.co/t/canvas-essql-failed-to-fetch/197508/5 "2019-10-17T14:06:00Z")

</div>

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