Struggling to get a count of unique entries for geo data

InfluxDB 2
1

Hello,

x-influxdb-build: OSS
x-influxdb-version: v2.7.4

I am adding a bunch of geo data to my influxdb, things like IP, Lat/Long, City, etc.

I can query unique entries like so:

from(bucket: "xx")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "traffic_report")  
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")

however what I need in addition is a “count” which counts each unique entry by ip.

So if for example my data was:

ip,lat,lon
127.0.0.1,50,100
127.0.0.1,50,100
172.1.1.2,30,50

How could I then return something like this?

ip,lat,lon,count
127.0.0.1,50,100,2
172.1.1.2,30,50,1

Tried a bunch of stuff and really struggling to get this, maybe I’m not inserting the data correctly.

Regards

2

@Josh_Freeman Using your example data, you would group by ip, lat, and lon, then use count(). Assuming lat and lon are fields and require the pivot(), you’re going to lose the _value column. count() requires an existing column to store the count values in (default is _value), so you need to add a new count column first and use that to store the count values.

It would look like this:

from(bucket: "xx")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "traffic_report")  
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> set(key: "count", value: "")
  |> group(columns: ["ip", "lat", "lon"])
  |> count(column: "count")
3

Edit: Think I finally got there. After that query I added “|> group()” at the very end, which seemed to combine it into 1 big table and now it reads in Grafana. Not sure if that is the right way but it worked lol

Final query being:

from(bucket: "xx")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "traffic_report")  
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> set(key: "count", value: "")
  |> group(columns: ["ip", "lat", "lon"])
  |> count(column: "count")
  |> group()

Hi Scott, thanks for the swift response!

The data now shows the correct values but it seems to be returning in a key’d index type thing rather than just a normal table, this also means it doesn’t seem to work in Grafana as it’s using the json ip/lat/long as a full column

image
image980×220 15.4 KB

You can see on the left, I have to click these.

Is there anyway to get it as a nice full table?