Getting a count of distinct label values in prometheus/grafana

Question

I am trying to create a table/chart in Grafana showing the total number of unique users who have logged in to a given application over a given time range (e.g. last 24 hours). I have a metric, app_request_path which records the number of requests hitting a specific path per minute:

app_request_count{app="my-app", path="/login"}

This gives me the following:

    app_request_count{app="my-app",path="/login",status="200",username="username1"}
    app_request_count{app="my-app",path="/login",status="200",username="username2"}

Now I want to count the number of unique usernames, so I run:

count_values("username", app_request_count{app="my_app", path="/login"})

and I get:

    {username="0"}
    {username="1"}
    {username="2"}
    {username="3"}
    {username="4"}
    {username="5"}

What am I missing / what am I doing wrong? Ideally I'd like to get a single scalar value that display the total number of unique usernames who have logged in in the past 24 hours.

Many thanks.

Clabo

Answer 1

count without (username)(app_request_count)

count_values is for metric values, count is for time series. It's also not advised to have something like usernames as label values as they tend to be high cardinality. They may also be PII, which could have legal implications.

Brennabrennan

Thanks! This didn't quite do what I needed it to, because it didn't cover a time period. However, I did manage to tweak it to achieve what I want: count without (username) (sum(sum_over_time(app_request_count{app="my-app", path="/login"}[24h])) by (username)) Thanks very much for the help! – Clabo

I agree with @Brennabrennan against the use of username as a label (PII and high cardinality, high cost if you're paying for prom). If you must have that label, at least for privacy hash the username before setting it as label, but then again you still have the high cardinality problem. – Divvy

@Divvy Why is the algorithm designed this way? username shouldn't be considered a "label" because of the cardinality. So why is the only way to retrieve this information by doing something that you're supposed to avoid? – Brauer

Answer 2

The following query should return the number of unique username label values encountered during the last 24 hours for the given labels app="my-app" and path="/login":

count(
  group(
    last_over_time(app_request_count{app="my-app", path="/login"}[24h])
  ) by (username)
)

How it works:

1. The inner last_over_time(...[24h]) query makes sure that every unique time series is selected for the last 24 hours, even if this time series stopped receiving new samples during the last 24 hours. See last_over_time() docs.

2. The group(...) by (username) returns a single time series per each unique username label. See group() docs.

3. The count(...) returns the total number of time series returned from group(). This number matches the number of unique values for username label. See count() docs.

Adjunct

last_over_time is supported in base prometheus as well. prometheus.io/docs/prometheus/latest/querying/functions/… incase anyone wants to use Prometheus and not VictoraMetrics. – Ence