Select disctinct and count with flux

InfluxDB 2
1

Hi,
I’m using InfluxDB to collect data with telegraf, I would like to be able to monitor the number of client that is sending data to my bucket over time. I didn’t find any predifined counter that seems to collect this information so I’m using the host tag to try to count the unique occurence at specific timerange. I cannot get the expected result.

The beginning of the request I’m trying to use is :

from(bucket: “telegraf_bucket”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == “cpu” or r["_measurement"] == “win_cpu”)
|> filter(fn: (r) => r["_field"] == “Percent_Idle_Time” or r["_field"] == “usage_idle”)
|> map(fn: (r) => ({
_time: r._time,
_value: r.host
}))

//|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)

The result is :

table _value _time
0 srv01 2022-04-04T13:49:10.000Z
0 srv01 2022-04-04T13:49:10.000Z
0 srv01 2022-04-04T13:49:10.000Z
0 srv02 2022-04-04T13:49:10.000Z
0 srv02 2022-04-04T13:49:10.000Z
0 srv01 2022-04-04T13:49:20.000Z
0 srv01 2022-04-04T13:49:20.000Z
0 srv01 2022-04-04T13:49:20.000Z
0 srv02 2022-04-04T13:49:21.000Z
0 srv02 2022-04-04T13:49:21.000Z
0 srv01 2022-04-04T13:49:30.000Z

What I expect to get to be able to display it on a graph is :

table _value _time
0 2 2022-04-04T13:49:10.000Z
0 1 2022-04-04T13:49:20.000Z
0 1 2022-04-04T13:49:21.000Z
0 1 2022-04-04T13:49:30.000Z

_value would be the result of grouping _time and counting distinct occurence of _value.
I tried to achieve that with unique, distinct, count etc. with no result.

Do you have any advice on how it could be possible to get the information?

Thanks

2

@mtbrem Do you want a single count for a given queried time range? Or do you want to return a count for windows of time over time? These can each be accomplished with two different approaches.

One count of unique tag values in a time range

import "influxdata/influxdb/schema")

schema.tagValues(tag: "host", start: -30d)
    |> count()

Host count over time

The following query will give you the count of unique host values per 30 minute window.

from(bucket: “telegraf_bucket”)
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["_measurement"] == “cpu” or r["_measurement"] == “win_cpu”)
    |> filter(fn: (r) => r["_field"] == “Percent_Idle_Time” or r["_field"] == “usage_idle”)
    |> keep(columns: ["_time", "host", "_value"])
    |> group()
    |> sort(columns: ["_time"])
    |> aggregateWindow(every: 30m, fn: (tables=<-, column) => tables |> unique(column: "host") |> count())
    |> keep(columns: ["_time", "_value"])
3

Thanks a lot! Yes I would like to have the host count over time to display it on a graph. The request you provided seems to do the job :smiley: