Flux - amount of unique TAGS by day

InfluxDB 2
1

Hello,
i need help with a query:

Need to know, how much uniqe TAGs entries are there per day.

data

timestamp username field1 tag2

2023-08-20 01:00:00 user1 20 PRD
2023-08-20 01:00:00 user2 20 PRD
2023-08-20 01:00:00 user1 20 PRD
2023-08-21 01:00:00 user3 20 PRD
2023-08-23 01:00:00 user1 20 PRD
2023-08-23 02:00:00 user2 20 PRD
2023-08-23 03:00:00 user3 20 PRD

output:

day #count
2023-08-20 2
2023-08-21 1
2023-08-23 3

2

Hello @fuba77,
Something like this might work:

from(bucket: "your_bucket_name")
  |> range(start: -30d)
  |> filter(fn: (r) => r._measurement == "your_measurement_name")
  // Group by day and username
  |> window(every: 1d)
  |> group(columns: ["_start", "username"])
  // Get unique entries
  |> distinct(column: "username")
  // Regroup by day only to get the count
  |> group(columns: ["_start"])
  |> count()
  |> rename(columns: {_value: "#count"})
  |> map(fn: (r) => ({day: r._start, #count: r.#count}))
3

thanks, that seems to work in Influx Data Exporer.
If i put it in Grafana Dashboard, i only see one count (for all days). :\

4

@Anaisdg
can you also tell me the change, if i want to group/distict not only for username, but a combination of “username” and e.g. “site” ?