Metric background colour based on query

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.

    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

    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

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 ?

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

at the moment there is no good documentation around it as the ability to manually edit the expression is still very much experimental.

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.

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