How to get count of unique users per location with Flux

InfluxDB 2
1

I’m trying to learn queries using Flux. I have sample data that has every instance of users (cardholders) badging into various locations. I want to see the breakdown of how many people were in any location per day.
My data is organized as follows:

  • measurement: “Event”
  • tags: location, door, Event_type, cardholder
  • fields: badge
    There is a one-for-one correspondence between a cardholder and a badge, but that doesn’t really matter. Also, there is a timestamp for each badge swipe.

With this query, I can get the total number of times someone was admitted to a location per day:

from(bucket: "testbucket1")
  |> range(start: -60d, stop: -30d)
  |> filter(fn: (r) => r._measurement == "Event")
  |> filter(fn: (r) => r.Event_type == "Access Granted")
  |> group(columns:["location"])
  |> aggregateWindow(every: 1d, fn: count)

However, this gives me the total “Access Granted” count, so if a person badges in multiple times, each of those is included in the count. I’m trying to have just the number of unique people who badged in. So, if John badged in 3 times and Jane badged in twice, I want the count to show 2, not 5 as it shows now.

I tried playing with the unique() function, and I tried adapting the solution shown here (Flux - amount of unique TAGS by day) but haven’t figured out the magic yet. Any suggestions would be most welcome.

Thanks!

2

@DaveB You can group by location, apply unique() with the cardholder column, and then count the number of distinct cardholder values in each location:

from(bucket: "testbucket1")
    |> range(start: -60d, stop: -30d)
    |> filter(fn: (r) => r._measurement == "Event")
    |> filter(fn: (r) => r.Event_type == "Access Granted")
    |> group(columns:["location"])
    |> unique(column: "cardholder")
    |> count()
3

Thanks Scott, that works! I was sure that I tried that and kept getting something along the lines of ‘no column “cardholder” exists’, but it works now, so I must have included an incorrect step along the way when playing with the unique() function. I know that I switched the column to unique(column: “door”) just to see if it would find the door column (it did), then switched it back to “cardholder” and got the error again.

p.s. I think that this is what I did when I was getting the no-column-exists error:

  |> group(columns: ["location", "cardholder"] 
  |> unique(column: "cardholder")

Then I switched back to just grouping the “location” column without trying the unique() function again.

Thanks!!!