Rolling up device-counts over time?

I’m using find3 to track location information for a large hall.

This tracks wifi devices, and how often they are seen - so the InfluxDB time-series data looks like this:

> select * from find3 LIMIT 5
name: find3
time                           active_mins first_seen               location      mac_address            num_scanners probability randomised randomized vendor
----                           ----------- ----------               --------      -----------            ------------ ----------- ---------- ---------- ------
2018-07-21T22:19:59.131000064Z 3           2018-07-21T04:32:16.821Z kidsroomlarge wifi-de:32:ed:c5:e7:0a 2            0.28        True                  ?
2018-07-21T22:19:59.151000064Z 3           2018-07-21T04:32:17.106Z kidsroomlarge wifi-5e:79:33:b9:d1:1f 2            0.36        True                  ?
2018-07-21T22:19:59.221999872Z 16096       2018-06-23T21:24:45.34Z  bbq           wifi-e0:b9:e5:6b:38:dd 2            0.46        False                 Technicolor
2018-07-21T22:19:59.243000064Z 72645       2018-03-10T15:40:12.806Z bbq           wifi-ec:8c:a2:27:ca:58 1            0.71        False                 Ruckus Wireless
2018-07-21T22:19:59.252999936Z 51          2018-03-20T02:21:12.767Z bbq           wifi-18:af:61:b7:f0:2e 1            0.63        False                 Apple

Each device will have a slightly different time-stamp based on when it sent packets etc.

I’m using Grafana to try to track active devices over time - however, I’m a bit confused by the roll-up behaviour in Grafana.

I’m doing a count across the mac_address field:

SELECT count(distinct("mac_address")) FROM "find3" WHERE ("randomised" = 'False') AND $timeFilter GROUP BY time($__interval) fill(null)

At zoomed-out levels, the numbers are very high - e.g. 100:

And at zoomed-in levels, the numbers are lower, but very spikey:

Is there an easy way to easily control the roll-up so it’s, for example, how many unique active devices per rolling 5-minute window?

The other complication is we only want each device MAC address (in the mac_address field) to be counted once - I think I’m doing this above with count() and distinct() in the same row in Grafana, but it doesn’t seem to have any visible effect when I remove/add distinct()?

$__interval is a dynamic variable and their value depends on the zoom level in your case (it can be 5m or 30d, …). Try to hard code it to GROUP BY time(5m), but it may kill your browser with a lot of data points, when you select a huge time range.

DISTINCT works as it was designed. It works with fields, but mac_address is a tag in your case. Try to GROUP BY "mac_address" instead of DISTINCT().