Prometheus query equivalent to SQL DISTINCT

user_165345 0
Viewed 13929

I have multiple Prometheus instances providing the same metric, such as:

my_metric{app="foo", state="active",   instance="server-1"}  20
my_metric{app="foo", state="inactive", instance="server-1"}  30
my_metric{app="foo", state="active",   instance="server-2"}  20
my_metric{app="foo", state="inactive", instance="server-2"}  30

Now I want to display this metric in a Grafana singlestat widget. When I use the following query...

sum(my_metric{app="foo", state="active"})

...it, of course, sums up all values and returns 40. So I tell Prometheus to sum it by instance...

sum(my_metric{app="foo", state="active"}) by (instance)

...which results in a "Multiple Series Error" in Grafana. Is there a way to tell Prometheus/Grafana to only use the first of the results?

grafana prometheus
3 Answers
user_99789170

One way I just found is to additionally do an average over all values:

avg(sum(my_metric{app="foo", state="active"}) by(instance))
user_1653450

If you need to return an arbitrary time series out of multiple matching time series, then this can be done with topk() or bottomk() functions. For example, the following query returns a single time series with the maximum value out of multiple time series which match my_metric{app="foo", state="active"}:

topk(1, my_metric{app="foo", state="active"})

You need to set instant query option in Grafana when using topk(). Otherwise topk(1, ...) may return multiple time series when it is used for building a graph with range query. This is because topk(1, ...) selects a single time series with the max value individually per each point on the graph. Different points on the graph may have different time series with the max value. There is a workaround, which allows returning a single series out of many series on a graph in alternative Prometheus-like systems such as VictoriaMetrics. It provides topk_* and bottomk_* functions for this purpose. See, for example, topk_last or topk_avg.

Note that topk() has no common grounds with DISTINCT from SQL. If you need to select distinct label values with PromQL, then you need to use count(...) by (label). It will return unique label values for the given label alongside the number of unique time series per each label value. For example, count(my_metric) by (app) will return unique app label names for time series with my_metric name. This is roughly equivalent to the following SQL with DISTINCT clause:

SELECT DISTINCT app FROM my_metric

See count() docs for details.

user_2749370
Related