Prometheus query to average over time by a specific label

Question

I need to query a metric and find out the average value of the metric over a period of 24hrs. But using using avg_over_time directly on the metric won't work. There is a specific ipaddr label. The average has to be grouped by each ipaddr. Now, grouping is not allowed in avg_over_time. In such case, how can I find out the average of the metric over 24 hrs for each ipaddr?

The metric and its values are like this

K_utilization{ifName="Ds12:1/0/30",ipaddr="10.1.109.54",node="worker"}  3.5
K_utilization{ifName="Ds65:1/0/4",ipaddr="10.1.5.50",node="worker"} 13.2
K_utilization{ifName="Ds26:1/0/8",ipaddr="10.1.123.58",node="worker"}   3.2
K_utilization{ifName="Ds69:0/0/10",ipaddr="10.1.115.55",node="worker"}  6.2
K_utilization{ifName="Ds71:0/0/21",ipaddr="10.1.25.51",node="worker"}   13.5

score 13 · Accepted Answer · answered Feb 06 '20 at 01:27

13

The avg_over_time function expects a range vector, which means that you could (if I understood correctly) use subquery like:

avg_over_time(K_utilization[1h:5m])

This will look at the K_utilization metric for the last 1h at a 5m resolution, the result should contain all labels from the metric.

You could also aggregate the metric in the subquery by the ipaddr label with a sum subquery and then calculate the avg_over_time:

avg_over_time(sum by (ipaddr) (K_utilization)[1h:5m])

More info about Prometheus subqueries

answered Feb 06 '20 at 01:27

Jorge Luis

3,098
2
16
21

The 2nd example seems to just results in `: parse error: unexpected \"(\""` – Douglas Gaskell Sep 15 '22 at 22:57
1

Which version of Prometheus are you using? If I'm not mistaken the subquery support was added in Prometheus 2.7 so you'll need at least that version. Running that query against an empty prometheus server (via docker) that doesn't contain the `K_utilization` metric yields an _Empty query result_ but not a parse error. – Jorge Luis Sep 17 '22 at 07:17
That might be it yeah, I'll verify our version and upgrade if needed. – Douglas Gaskell Sep 17 '22 at 20:20

score 3 · Answer 2 · answered Mar 22 '22 at 14:10

The following PromQL query returns the average K_utilization over the last 24 hours grouped by ipaddr:

sum(sum_over_time(K_utilization[24h])) by (ipaddr)
/
sum(count_over_time(K_utilization[24h])) by (ipaddr)

It uses sum_over_time and count_over_time functions for calculating the average value.

This query is roughly equivalent to the following SQL:

SELECT ipaddr, avg(value)
FROM K_utilization
WHERE timestamp > now() - interval '24 hours'
GROUP BY ipaddr

It is assumed that the K_utilization table contains the following fields:

ipaddr string
timestamp int
value float

Prometheus query to average over time by a specific label

2 Answers2