Select the most frequent metric value

Question

I need to create a PromQL query to select the most frequent metric value and display it in a Grafana panel; something like (Find most frequent value in SQL column)

In the following example, for metric status with label job,

I want to display in a panel status 1 (given count for 1 = 2, count for 2 = 1, count for 3 = 1) (and possibly use that 1 in Grafana, i.e., as a value in the Values Mapping section of a panel such as Polystat).

status(job="a") = 1
status(job="b") = 2
status(job="c") = 1
status(job="d") = 3

score 2 · Accepted Answer · answered Dec 07 '20 at 12:39

2

Try:

count_values("val", status)

You can try to apply topk(1, ...) on that, but it is tricky (what if two top values have the same number of occurrences?).

answered Dec 07 '20 at 12:39

bjakubski

1,477
10
9

I've being playing around with this and it looks promising! It'll show `{job="1"} 2` and I could simply display `1` using `{{job}}` as a _Legend_. What if something more "sophisticated" is needed such as using that **`1`** as a value in the _Values Mapping_ section? – jbuddy Dec 07 '20 at 13:51

score 1 · Answer 2 · answered Dec 07 '20 at 12:42

1

The query max(count_values("value",status)) == ignoring(value) group_right count_values("value",status) will give you {value="1"} 2 (and unlike topk may give you more rows if several metrics have the same maximal count).

Then in Grafana you just need to display the label from the result.

answered Dec 07 '20 at 12:42

Michał Politowski

4,288
3
30
41

Thank you! This is quite an interesting solution which has a similar result as the other one posted here: It'll show `{job="1"} 2` and I could simply display `1` using `{{job}}` as a _Legend_. What if something more "sophisticated" is needed such as using that **`1`** as a value in the _Values Mapping_ section? – jbuddy Dec 07 '20 at 13:53
@jbuddy This depends on what exactly you want to do (and, I think, if your Grafana is a current version), but most likely it will help you to set "Format" to "Table" in Grafana query editor (and probably turn on the "Instant" switch). – Michał Politowski Dec 07 '20 at 14:06
Yeah, this is getting trickier, I'm trying to use that **`1`** as a value in the _Values Mapping_ section of [Polystat](https://grafana.com/grafana/plugins/grafana-polystat-panel). Example value: **`1`** -> text : `Running`. – jbuddy Dec 07 '20 at 15:04

Select the most frequent metric value

2 Answers2