I want to create a query that calculates the average of the top 10 highest values of a metric (just one metric with one label) within a 1-hour time window.
I currently have a time series dashboard with the query:
avg_over_time(node_load1{instance=~“$node”}[1h])
But instead of averaging all values, I need to calculate the average of only the top 10 values.
How can I achieve this?
I tried using the following query:
avg(topk(10,(node_load1{instance=~“$node”,job=“$job”})))
However, topk doesn’t seem to work the way I need. My goal is to get the top 10 values for a single instance within 1 hour, rather than the top 10 values across multiple instances.
AFAIK, PromQL doesn’t have a direct function to get the top N values and then average them.
You could query the raw data using your existing PromQL query avg_over_time(node_load1{instance=~"$node"}[1h]) and then use Grafana’s transformations or other panel plugins to process the data. You’d fetch all the data points within the 1-hour window and then perform the top-10 average calculation in Grafana. This shifts the processing burden to Grafana.
Use a simple PromQL query to get all the data points for your single instance over the last hour: node_load1{instance="$node"}[1h]
Then utilize Grafana’s transformations to sort and limit the data.
In your Grafana panel, add the query node_load1{instance="$node"}[1h].
Use the “Sort” transformation to sort the data in descending order.
Use the “Limit” transformation to keep only the top 10 values.
Then, use the “Reduce” transformation to calculate the average of the remaining values.
I thought about that but from my tests Limit transformation only limits the view (Grafana 11.1.3), not the actual values (although all my top 10 values were more than 20, the mean was calculated as 18). Reduce transformation would take all the values into account:
From PromQL side there seems to be no such function.
What you could do is to create a hidden panel with Grant’s solution minus the reduce function, then create a second panel with query from dashboard datasource like this:
