Hello,
I have a small side project to parse log ip from nginx reverse proxy. I get the ip,source_lat,source_lon
What I would like is first to group the combinations (ip,source_lat,source_lon ) together and then count the number of occurrences of this combination that I have and associate it with a new column with the value. The probleme is that ip adress field disapear after counting.
The best I have for now is this:
from(bucket: "telegraf-nginx")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "nginx_access")
|> filter(fn: (r) => r["_field"] == "ip" or r["_field"] == "source_lat" or r["_field"] == "source_lon" or r["_field"] == "ip")
|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
|> keep(columns: ["ip","source_lat", "source_lon"])
|> filter(fn: (r) => exists r.source_lat and exists r.source_lon)
|> group(columns: ["source_lat", "source_lon"])
|> count(column: "ip")
|> rename(columns: {"ip": "ip_cnt"})
|> group(columns: ["_result"])
Wanted result :
ip source_lat source_lon nb
1.5.6.8 23 25 2
1.4.5.5 21 15 5
Thanks.