Canvas - essql failed to fetch

[atamarnath (Amar)]

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

[lukeelmers]

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:

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

[atamarnath (Amar)]

@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

[sathish8 (Sathish)]

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" }
}