# Metric background colour based on query
**URL:** https://discuss.elastic.co/t/metric-background-colour-based-on-query/262636
**Category:** Kibana
**Tags:** canvas
**Created:** [January 29, 2021, 11:38am UTC](https://discuss.elastic.co/t/metric-background-colour-based-on-query/262636 "2021-01-29T11:38:25Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@ian\_k](https://discuss.elastic.co/u/ian_k)
#### Post date: [January 29, 2021, 11:38am UTC](https://discuss.elastic.co/t/metric-background-colour-based-on-query/262636/1 "2021-01-29T11:38:25Z")
Hello, I've been reading some similar posts and solutions here, but I can't get them to work. I'd like to change the colour of the background of a metric based upon the number of items returned by a query, but I'm very new to ELK and the coding. please can you help me get the logic right.
I've followed a previous post answered by Catherine Liu, I think there must be a problem in my logic because I think the data I'm trying to get is text, and I don't know how to get the render to evaluate the returned value as a number? I might be wrong in this assumption.
here is my query, and the one I've tried based upon the post.
show's the correct number.
```auto
filters
| essql
query="SELECT \"vulnerability.category\" FROM \"ecs-servicehealth-o365\" WHERE \"event.outcome\"='Activated' AND \"event.category\"='Alerts' AND \"event.dataset\"='office365.servicehealth' AND \"service.state\"='ServiceDegradation' AND \"vulnerability.category\" IS NOT NULL"
| math "size(vulnerability.category)"
| metric "vulnerabilities detected"
metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center" lHeight=48}
labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"} metricFormat="0,0.[000]"
| render containerStyle={containerStyle}
```
tried this solution
```auto
filters
| essql
query="SELECT \"vulnerability.category\" FROM \"ecs-servicehealth-o365\" WHERE \"event.outcome\"='Activated' AND \"event.category\"='Alerts' AND \"event.dataset\"='office365.servicehealth' AND \"service.state\"='ServiceDegradation' AND \"vulnerability.category\" IS NOT NULL"
| math "size(vulnerability.category)"
| metric "vulnerabilities detected"
metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center" lHeight=48}
labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"} metricFormat="0,0.[000]"
| render containerStyle={
containerStyle backgroundColor={
filters | essql query="SELECT \"vulnerability.category\" FROM \"ecs-servicehealth-o365\" WHERE \"event.outcome\"='Activated' AND \"event.category\"='Alerts' AND \"event.dataset\"='office365.servicehealth' AND \"service.state\"='ServiceDegradation' AND \"vulnerability.category\" IS NOT NULL" | if {gt 50} then="red" else="green"
}
}
```
advice and guidance welcome
Many thanks
Ian
---
### Author:  [@ppisljar](https://discuss.elastic.co/u/ppisljar)
#### Post date: [February 1, 2021, 7:17am UTC](https://discuss.elastic.co/t/metric-background-colour-based-on-query/262636/2 "2021-02-01T07:17:10Z")
could you show the output (sample) of your query inside the backgroundColor expression:
`filters | essql query="SELECT \"vulnerability.category\" FROM \"ecs-servicehealth-o365\" WHERE \"event.outcome\"='Activated' AND \"event.category\"='Alerts' AND \"event.dataset\"='office365.servicehealth' AND \"service.state\"='ServiceDegradation' AND \"vulnerability.category\" IS NOT NULL"`
i am expecting this to return some kind of string ? maybe you want to COUNT(\*) instead of get the category ? as you are then comparing the output to 50 ?
---
### Author:  [@ian\_k](https://discuss.elastic.co/u/ian_k)
#### Post date: [February 1, 2021, 9:34am UTC](https://discuss.elastic.co/t/metric-background-colour-based-on-query/262636/3 "2021-02-01T09:34:04Z")
Thank you Peter, I wondered if it was something like this, yes it does get a list of incidents that MS O365 report, in the form of incident numbers, we have the metric showing the Rows.length so I wasn't sure how to get the query to do the same, but I guess if the second query is only for the colour it doesn't matter. Could you also, if possible point me to somewhere I can learn how to construct these things? or is it simply by practice?
Many thanks
---
### Author:  [@ppisljar](https://discuss.elastic.co/u/ppisljar)
#### Post date: [February 1, 2021, 11:25am UTC](https://discuss.elastic.co/t/metric-background-colour-based-on-query/262636/4 "2021-02-01T11:25:36Z")
at the moment there is no good documentation around it as the ability to manually edit the expression is still very much experimental.
---
### Author:  [@ian\_k](https://discuss.elastic.co/u/ian_k)
#### Post date: [February 1, 2021, 11:29am UTC](https://discuss.elastic.co/t/metric-background-colour-based-on-query/262636/5 "2021-02-01T11:29:54Z")
Ah, I see, well thank you very much. I think background changing based on value might be a very useful feature to put on the front end also. so not so much editing of code, as I think it might be quite popular. it does make things easy to see at a glance for pane of glass dashboards. could you help me construct the render expression so it works? I can't figure it out?
I tried SELECT COUNT(\*) and also SELECT COUNT(vulnerability.category) and variations with slashes and quotes. sorry I'm not a native coder.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [March 1, 2021, 11:30am UTC](https://discuss.elastic.co/t/metric-background-colour-based-on-query/262636/6 "2021-03-01T11:30:50Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.