Feature: Treat null as zero for coloring thresholds

[pdf]

For Singlestat panels in particular it would be useful to treat null as zero, otherwise there's no way to color a panel with a null result.

[torkelo]

transformNull function works great if you use Graphite

[pdf]

There's no fill mechanism I can see right now for my backend.

[torkelo]

which is?

[pdf]

Prometheus

[torkelo]

Does prometheus have transformation functions / features? I have yet to try it out (really eager to do that, might add Prometheus as official data source plugin)

[pdf]

A pretty decent selection: http://prometheus.io/docs/querying/functions/

[feliksik]

In grafana 2.1 it seems like the 'null' values are treated as 0 values, as far as the coloring is concerned (even if null is mapped to a text as in #1166).

To make it a bit more challening: if lower is better, however, you may want to set thresholds for green/orange/red on 0/1/3, i.e. value >3 is read (but maybe null should also be red). So then a null value becomes green, but you can argue it should be red.

When addressing this issue, it is worth to consider it together with #1319. E.g. they could be addressed simultaneously by implementing ranges for text mappings, and specifying color codings for such texts (colors for values are then specified indirectly, which is more flexible but arguably more cumbersome).

[n-st]

@torkelo I agree with @feliksik and would go as far as calling this a bug: When one of my monitored nodes goes offline, querying its load average returns null. Since the singlestat panel replaces null with zero, the panel shows "N/A" in green, when it really should be red or colourless (preferably depending on a configuration option).

---

Edit: Turns out this feature existed before (#1130), but was removed in cc21c66. Guess we can move the discussion to #1130.

[gvohra]

+1 I agree with @n-st and @feliksik : currently our dashboard is showing 'red' for N/A when in fact N/A means no data / no activity, so should ideally be uncoloured.

[grobie]

It's possible to return 0 for null in Prometheus with the OR operator: exression OR on() vector(0) will return 0 if the metric expression does not return anything. @pdf @torkelo

[torkelo]

closing this as most TSDB backends have ways to handle nulls in the query

[boeboe]

@grobie

it's true that the OR operator fixes this if the expression was null. However, in a single stats page this fixes the color for null values, but breaks that for none-null metrics, as they will be reported having multiple values: the real value and the {} added by your prometheus expression.

Can I suggest to re-open this issue?

[grobie]

@boeboe I updated my expression from the comment above. This one should work for you.

Explanation: Prometheus uses label matching in expressions. If your expression returns anything with labels, it won't match the time series generated by vector(0). In order to make this possible, it's necessary to tell Prometheus explicitly to not trying to match any labels by adding on().

[boeboe]

@grobie thanks for your explanation. Unfortunately, the Prometheus documentation don't give a clear example on the usage of the on() operator (https://prometheus.io/docs/querying/operators)

Here is my current query:
counter_services_active_sessions_responses_4xx_total{kubernetes_namespace="dev-appservicebase"} OR vector(0)

How should I rewrite this one in order to avoid duplicates in case the metrics does exist (in this case of a microservice has return HTTP 4xx responces). Right now the query returns the following 2 results:

counter_services_active_sessions_responses_4xx_total{instance="172.24.27.130:8081",job="kubernetes-pods",kubernetes_namespace="dev-appservicebase",kubernetes_pod_name="active-sessions-dp-3210291495-nw9n7",kubernetes_pod_node_name="node-172-16-24-24",pod_template_hash="3210291495",type="active-sessions-type"} 1173
{} 0

[boeboe]

@grobie nevermind my last comment, must saw you updated the query. Thanks!

[TheFrogDaddy]

Does anybody have a query workaround for MySQL?

[marefr]

@TheFrogDaddy you can use a select case when and/or in combination with coalesce function.

[TheFrogDaddy]

@TheFrogDaddy you can use a select case when and/or in combination with coalesce function.

Hi, thanks for getting back to me so quickly. I not getting this to work, am I missing something?

Tried this:

SELECT $__timeGroup(updated, '1h', 0) AS time, COALESCE(SUM(1),0) as value, 'Errors' AS metric FROM mytable WHERE $__timeFilter(updated) AND error_message IS NOT NULL GROUP BY 1;

AND this

SELECT $__timeGroup(updated, '1h', 0) AS time, CASE ISNULL(SUM(1)) WHEN TRUE THEN 0 ELSE SUM(1) END AS value, 'Errors' AS metric FROM myTable WHERE $__timeFilter(updated) AND error_message IS NOT NULL GROUP BY 1;

Thanks

[marefr]

@TheFrogDaddy what are you trying to summarize with sum(1)? Makes no sense to me. I would suggest we continue the discussions on our community site and specifically the tag mysql. Please open a new topic there and you can ping me by @ + mefraimsson

[devils-ey3]

It's possible to return 0 for null in Prometheus with the OR operator: exression OR on() vector(0) will return 0 if the metric expression does not return anything. @pdf @torkelo

Don't forget about to active instant option !!

[jigetage]

说的真好

[jigetage]

It's possible to return 0 for null in Prometheus with the OR operator: exression OR on() vector(0) will return 0 if the metric expression does not return anything. @pdf @torkelo

good. brother

[urusha]

It's possible to return 0 for null in Prometheus with the OR operator: exression OR on() vector(0) will return 0 if the metric expression does not return anything. @pdf @torkelo

This doesn't work with by (label), while this seems to work:

expression OR up*0
sum by (label) (data_metric) - sum by (label) (nodata_metric or up*0)

[zekth]

Here is my 2 cents

sum(expression) by (label) -
sum(expression offset $__interval OR expression * 0) by (label)

This can give me the difference of the 2 expression in the targeted interval. If the timeserie is not present the calculation is done considering the value is 0

[valyala]

The following MetricsQL query is the easiest one if gaps on the graph must be filled with zeroes:

q default 0

Contrary to q on() vector(0), the solution with default operator works for any number of time series returned by q. It also works as expected for arbitrary labels per each returned time series, by filling gaps per each returned series.

[idanam-del]

It's possible to return 0 for null in Prometheus with the OR operator: exression OR on() vector(0) will return 0 if the metric expression does not return anything. @pdf @torkelo

this doesn't work when in the expression i use for an example node_id="111|222" but it does work when they are separate like node_id=111 or node_id=222. with this it means i need to do a separate query for each node :/

Is there a solution when a query returns a table and one of the rows have no data? As it is with 'OR on() vector(0)' the row that doesn't have data is just missing and doesn't have a 0.