Trying to use distinct with time series

InfluxDB 2 Dashboards
1

I’m trying to poll the unique users from a wireless access point (AP) based on its’ Mac address -CliAPMAC. As clients move around, they appear on multiple APs, I just want the distinct values for the last X minutes or hours.

this works, but returns all entries, I just want the distinct values for the last hour.
select distinct(CliIP) from bsnMobileStationTable where (“CliAPMAC” =~ /^2c:d0:2d:ec:23:c0$/)
output:
name: bsnMobileStationTable
time distinct

0 192.168.15.129
0 192.168.15.118
0 192.168.15.230
0 192.168.15.208
0 192.168.15.201
0 192.168.15.54
0 192.168.15.249
0 192.168.15.128
0 192.168.15.222

This does not work:

select distinct(CliIP) from bsnMobileStationTable where (“CliAPMAC” =~ /^2c:d0:2d:ec:23:c0$/) AND time > NOW() - 1h GROUP BY time(1h)

2

Hi @Eric_Garnel

Does this work?

select distinct(CliIP) from bsnMobileStationTable where (“CliAPMAC” =~ /^2c:d0:2d:ec:23:c0$/) AND time > NOW() - 1h
3

No, it doesn’t return any results. Trimming off the time statement provides results

4

I think part of the problem is that distinct has no sense of time:

select distinct(CliIP) from bsnMobileStationTable where (“CliAPMAC” =~ /^2c:d0:2d:ec:23:c0$/)
name: bsnMobileStationTable
time distinct

0 192.168.15.129
0 192.168.15.118
0 192.168.15.230
0 192.168.15.208
0 192.168.15.201
0 192.168.15.54
0 192.168.15.249
0 192.168.15.128
0 192.168.15.222

5

For some unknown reason, its’ working now
select distinct(CliIP) from bsnMobileStationTable where (“CliAPMAC” =~ /^2c:d0:2d:ec:23:c0$/) and time > now() -30m
name: bsnMobileStationTable
time distinct

1585058836339636774 192.168.15.129
1585058836339636774 192.168.15.208
1585058836339636774 192.168.15.249