Esql - Applying if condition to output of two esql queries

[atamarnath (Amar)]

Hi,

I have the below code in expression edit that seems to be working fine.

filters

| essql "SELECT RAG.keyword AS RAG FROM "logstash-bam*" where sourcetype.keyword = 'current_status_A' AND Step = 'A' AND time_stamp > TODAY() order by time_stamp DESC limit 1"

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

| render containerStyle={containerStyle} css=".canvasRenderEl { }"

Now I have another query that need to be added to the above code

essql "SELECT RAG.keyword AS RAG FROM "logstash-bam*" where sourcetype.keyword = 'current_status_B' AND Step = 'A' AND time_stamp > TODAY() order by time_stamp DESC limit 1"

Set the color of shape to GREEN if both the output of both the sqls return a RAG of GREEN. Even if one the query returns RED the color of the shape need to be set to RED

[tims (Tim Schnell)]

Hi @atamarnath, the problem you are going to encounter is that you can't pass 2 query contexts into an if statement. Is there a way to make 1 query return the count of sourcetype.keyword where it equals 'current_status_A' or 'current_status_B'?

[atamarnath (Amar)]

Thank you for your response. Is there a way to store the result of the query to a temporary variable and then use it in the if statement.

[tims (Tim Schnell)]

There is no way to create a variable in the expression language but you could maybe store a value to the datatable using mapColumn and then reference it using getCell.

[atamarnath (Amar)]

Thanks Tim. We ended up converting the RAG to a number instead of string and sorting the rows added in last 1 minute based RAG and pick up the top record. We then used if condition to identify the color. Basically RAG was made to store 1, 2 or 3 where 1 = RED, 2 = Orange (Amber is not supported by Canvas), 3 = GREEN.