How to get number of hosts posting stats to influxDB in timeseries graph using Flux?

I am using InfluxDB2 and have use case that needs to monitor the number of the active servers(hosts) posting stats to InfluxDB in time series graph. However below query does not give the expected result, could someone please help? Thanks

from(bucket:"test")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter (fn: (r) => r["_meaurement"] == "cpu")
|> filter (fn: (r) => r["_field"] == "usage_system")
|> filter (fn: (r) => r["cpu"] == "cpu-total")
|> aggregateWindow(every: v.windowPeriod, fn:count, createEmpty: false)
|> distinct(column: "host")
|> group()
|> count()

This worked for me…might not be as efficient as other ways but I’m a noob.

from(bucket:“test”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter (fn: (r) => r["_meaurement"] == “cpu”)
|> filter (fn: (r) => r["_field"] == “usage_system”)
|> filter (fn: (r) => r[“cpu”] == “cpu-total”)
|> group()
|> keep(columns: “host”)
|> unique(column: "host")
|> count(column: "host")

If you’re using Grafana to visualize you can just set the filter query then use transformations there to get the same info. That’s if you’re using Grafana of course.

Thank you for your reply @RobR. I do use infuxDB query in Grafana. The ideal graph is in time series which x-axis = _time, y-axis = number of the host. With the below query, it returns count only and can not be graphed in time series though. Did I miss something?

from(bucket:“test”)

Sorry…I missed the time series bit…I thought you wanted the current total count based on the time range used in a stat panel or something. How about this?

from(bucket:“test”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter (fn: (r) => r[“_meaurement”] == “cpu”)
|> filter (fn: (r) => r[“_field”] == “usage_system”)
|> filter (fn: (r) => r[“cpu”] == “cpu-total”)
|> keep(columns: [“host”, “_stop”, “_time”])
|> group()
|> unique(column: “host”)
|> map(fn: (r) => ({ r with count: “nothing” }))
|> aggregateWindow(
every: 1m,
fn: count,
column: “count”,
timeSrc: “_stop”,
timeDst: “_time”,
createEmpty: false
)

The aggregate window with the column option wants the column that it uses to exist, so I just mapped a new column with “nothing” in it.

Like I said, I’m a noob at this so not sure if this is getting you what you need.

Thanks a lot for your help @R0bR. it is very close to what I need. With the updated query you suggested, It can be graphed in table view but not in time series graph. What was the issue then?

You mean in Grafana? You may need to add some transformations to make sure it is trying to graph the correct fields. One I use often is “Organize Fields” then hide the columns I don’t need. This is in Grafana 8.

Appreciated your help @RobR Made it work after appending

map (fn: (r) => ({
time: r._time,
server: r_host,
_value: r.count
}))

Cheers

That’s awesome! Going to add that to my notes.

@R0bR @Jennyinfluxdb2 can you please share full latest query? I am getting this error " undefined identifier r_host" … :frowning: