Help with influx query

Grafana InfluxDB
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

If you are using Flux, have you tried this function?

3

tried this:

from(bucket: “clients”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “clients”)
|> aggregateWindow(every: 1d, fn: count, createEmpty: false)
|> group(columns: [“username”])
|> distinct(column: “username”)
|> keep(columns: [“_value”, “_time”])
|> count()

but i get " ```
runtime error: no time column: _time

4

Hi @fuba77

These 3 lines should do it:

  |> group(columns: ["_time"])
  |> distinct(column: "username")  
  |> count(column: "_value")

Example where the distinct regions are being counted for each date:

image
image1000×871 43.2 KB

import "csv"

csvData =
    "
#datatype,string,long,dateTime:RFC3339,dateTime:RFC3339,dateTime:RFC3339,string,string,double
#group,false,false,false,false,false,true,true,false
#default,,,,,,,,
,result,table,_start,_stop,_time,region,host,_value
,mean,0,2018-05-08T20:50:00Z,2018-05-08T20:51:00Z,2018-05-08T20:51:00Z,east,A,15.43
,mean,0,2018-05-08T20:50:00Z,2018-05-08T20:51:00Z,2018-05-08T20:51:00Z,east,A,65.15
,mean,0,2018-05-08T20:50:00Z,2018-05-08T20:51:00Z,2018-05-08T20:51:00Z,north,B,59.25

,mean,0,2018-05-09T20:50:00Z,2018-05-09T20:50:00Z,2018-05-09T20:50:00Z,east,B,18.67
,mean,0,2018-05-09T20:50:00Z,2018-05-09T20:52:00Z,2018-05-09T20:50:00Z,west,C,52.62
,mean,0,2018-05-09T20:50:00Z,2018-05-09T20:52:00Z,2018-05-09T20:50:00Z,south,C,52.62

,mean,0,2018-05-10T20:50:00Z,2018-05-10T20:52:00Z,2018-05-10T20:51:00Z,east,C,82.16
"

csv.from(csv: csvData)
  |> range(start: 2018-05-08T20:50:00Z, stop: 2018-05-10T20:52:00Z)
  |> group(columns: ["_time"])
  |> distinct(column: "region")  
  |> count(column: "_value")
5

Thanks,
i just tested it with this code:

from(bucket: “clients_temp”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “clients_temp”)
// Group by day and username
|> aggregateWindow(every: 1d, fn: count, timeSrc: “_start”)
|> 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}))

This seems to work in Data Explorer of InfluxDB, but in Grafana i only get one BAR with a count of all days.

6

Hi @fuba77

Does adding this transformation help? It is only available with the latest version of Grafana.

image
image1361×285 21.3 KB