Conditional colouring using kibana canvas expression editor

Elastic Stack Kibana
1

Hi,

I have an metric element called "Memory%", now would like to display the metric value in different colours based on the value. If the value is greater than 75% and less than 90% want to display the value in amber colour and if it is greater than 90% want to display in red colour.
Need a help on this please

2

@alexf can we please get some help here? I know we definitely can change it. But I am failing at getting examples and docs to help the user here.

Thanks,
Bhavya

3

Below is the query from expression editor in canvas dashboard

filters
| essql
query="SELECT "system.load.1" AS LOAD FROM "metricbeat*" WHERE metricset.module = 'system' AND metricset.name = 'load' AND beat.hostname LIKE 'XXXXXX%' AND "@timestamp" <= NOW () LIMIT 1"
|metric Load
|getCell "LOAD"
|if {lte 10}
then={markdown "**" font={font family="'Open Sans', Helvetica, Arial, sans-serif" size=14 align="left" color="#dd0a73" weight="bold" underline=false italic=false}}
| render

Getting error "Expression failed with [math] > Can not cast 'render' to any of 'number, datatable' "

4

Hi,

Can anyone help me on this

5

Try this expression and see if the element looks right:

filters
| essql
query="SELECT "system.load.1" AS LOAD FROM "metricbeat*" WHERE metricset.module = 'system' AND metricset.name = 'load' AND beat.hostname LIKE 'XXXXXX%' AND "@timestamp" <= NOW () LIMIT 1"
| metric LOAD 
  metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color={if {lt 10} then="red" else="green"} align="center" lHeight=48} 
  labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"}
| render
1 Like
6

@ksunil Actually try this expression instead:

filters
| essql 
  query="SELECT \"system.load.1\" AS LOAD FROM \"metricbeat*\" WHERE metricset.module = 'system' AND metricset.name = 'load' AND beat.hostname LIKE 'XXXXXX%' AND \"@timestamp\" <= NOW () LIMIT 1"
| math "LOAD"
| metric "Load" 
  metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color={switch case={case if={all {gt 0.75} {lt 0.9}} then="yellow"} case={case if={gte 0.9} then="red"} default="green"} align="center" lHeight=48} 
  labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"}
| render
7

@Catherine_Liu I have basically the same problem not with the font color but with the background color

I have just one condition instead of two my approach is quite like the fist you gave to ksunil.

I do a select count(*) and if it is greater than 0 the backround should be green otherwise it should be red.

The expression editor looks like below

filters | essql query="SELECT * FROM uacuser WHERE logintime > NOW() - INTERVAL 1 MONTH AND disabled = 0" | math "size(userid)" | metric "Users logged in this month" metricFont={font family="'Open Sans', Helvetica, Arial, sans-serif" size=48 align="center" color="#FFFFFF" weight="normal" underline=false italic=false} labelFont={font family="'Open Sans', Helvetica, Arial, sans-serif" size=14 align="center" color="#FFFFFF" weight="normal" underline=false italic=false} | render **containerStyle={containerStyle backgroundColor={if {gt 0} then="red" else="green"}}** // this
is the line that matters

It seems that the condition works not like intended , it is always else and showing a green background. Did I do something wrong? Is the condition always related to the value in the metric? Or is it possibly a bug?
P.S How can you do that code snipet in a post?

8

Hi,

Thanks Catherine Liu, now able to display the metric font in colours based on the condition.
Thanks, it helped and works for me

9

Hi Catherine,

When i user FormatNumber "0.0a" for metric ,coloring of metric is not working though colors will change based on metric value.
Please help me regarding this

10

I have the same issue. But if I remove the following part, I obtain a conditionnal background without being able to modify the metric parameters.
| metric "Users logged in this month" metricFont={font family="'Open Sans', Helvetica, Arial, sans-serif" size=48 align="center" color="#FFFFFF" weight="normal" underline=false italic=false} labelFont={font family="'Open Sans', Helvetica, Arial, sans-serif" size=14 align="center" color="#FFFFFF" weight="normal" underline=false italic=false}
This is a strange behaviour.
Here is what I what I use to have this "strange" conditionnal background:
filters | essql query="SELECT * FROM uacuser WHERE logintime > NOW() - INTERVAL 1 MONTH AND disabled = 0" | math "size(userid)" | render **containerStyle={containerStyle backgroundColor={if {gt 0} then="red" else="green"}}

11

Hi I am trying to change the colour of the service_availability based on the procentage. If 99.5 or higher will be green, if is lower will be red.
The problem is the conditional is not working, it's taking the default value. Any idea how I can fix it? Please find the code bellow

filters

| essql
query="SELECT "@timestamp", env, ROUND((100 -(sla.timeout + 0.0) / (sla.searches + 0.0) * 100.0),1) as service_availability from "service*" where sla.searches is not null and env='parpr1' order by "@timestamp" desc LIMIT 1"
| markdown "
{{#each rows}}
{{service_availability}}%
{{/each}}

"
font={font family="'Open Sans', Helvetica, Arial, sans-serif" size=24 align="center" color={switch case={case if={gte 99.5} then="green"} case={case if={lt 99.5} then="red"} default="red"} weight="normal" underline=false italic=false}
| render

12

@ksunil Hmm that's odd. What does your expression look like?

Did you add it between your math and metric like this? This example should work and retain the conditional coloring.

filters
| essql
query="SELECT "system.load.1" AS LOAD FROM "metricbeat*" WHERE metricset.module = 'system' AND metricset.name = 'load' AND beat.hostname LIKE 'XXXXXX%' AND "@timestamp" <= NOW () LIMIT 1"
| math "LOAD"
| formatNumber format="0.0a"
| metric "Load"
metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color={switch case={case if={all {gt 0.75} {lt 0.9}} then="yellow"} case={case if={gte 0.9} then="red"} default="green"} align="center" lHeight=48}
labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"}
| render

13

@Cosma_Silviu since you only have the two cases, you can simply use the if function instead of the switch function which is used for multiple branching conditions.

The issue is your conditional subexpressions, (i.e. {gte 99.5},{lt 99.5}) are receiving the same input or context as the metric function they're nested within, which is the entire output of your essql datasource, so it's comparing the number 0.9 against an entire datatable. What you need to do is extract the value from the datatable using a function like getCell and pipe that value into gte 99.5

Something like this should work:

filters
| essql 
  query="SELECT \"@timestamp\", env, ROUND((100 -(sla.timeout + 0.0) / (sla.searches + 0.0) * 100.0),1) as service_availability from \"service*\" where sla.searches is not null and env='parpr1' order by \"@timestamp\" desc LIMIT 1"
| markdown "{{#each rows}}
{{service_availability}}%
{{/each}}" 
  font={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color={if {getCell "service_availability" | get 99.5} then="green" else="red"} align="left"}
14

Thank you, it worked

15

I've not been able to get the background colour to fall into the "then" outcome of an if statement in the containerStyle. Is it something to do with the current context being after the render??!?!?

I'd really like to resolve this so we can stop using DataDog for our dashboards!!!

16

Hey Scott, can you post a code snippet of what you are trying when the conditional doesn't work as expected?

17

Hi tims,

I have the same problem as Scott , trying to change background color by condition and it doesnt work. Here is the code snipet:

filters
| essql
query="SELECT
count("eai_sts_proc") AS cnt
FROM
"logit-exampleindex-*"
WHERE
app_name = 'EXAMPLEAPP'
AND
command = 'EXAMPLESERVICE'
AND
eai_sts_proc= 'OK'
AND
"@timestamp" > NOW() - INTERVAL 5 MINUTES"
| math "cnt"
| metric ""
metricFont={font family="'Open Sans', Helvetica, Arial, sans-serif" size=24 align="center" color="#000000" weight="bold" underline=false italic=false}
labelFont={font family="'Open Sans', Helvetica, Arial, sans-serif" size=14 align="center" color="#000000" weight="normal" underline=false italic=false}
| render
containerStyle={containerStyle backgroundColor={if {lt 1} then="red" else="green"}}

it is always else and showing a green background
Thanks

18

@ScottBarnes @sharonva You can't conditionally change the color of the background, because sub-expressions in the render function receive a render object as context and tries to compare your conditional expressions to the render object.

The simple way around this is to add a separate shape element that serves as a background element hooked up to the same datasource where you have access to your datasource to conditionally render the color of the shape.

Another solution would be to pipe the same datasource into the beginning of your conditional sub-expression.

For example:

...
| render containerStyle={
     containerStyle backgroundColor={
       filters | demodata | math "sum(price)" | if {gt 50} then="red" else="green"
     } 
  }
2 Likes
19

Thanks Catherine, It works.

20

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