The.field column repeats the value count and generates a new corresponding column

InfluxDB 2
, ,
1

Hello guys，I want to count the number of requests for duplicate ip
input
| client_ip |
|----------------|---------------|
| 10.10.11.11 |
| 10.10.11.11 |
| 10.10.11.11 |
| 10.10.13.12 |
| 10.10.13.12 |
| 10.10.13.12 |
| 10.20.13.15 |
| 10.20.13.16 |
| 10.20.13.18 |
| 10.20.13.18 |

Assuming the original table looks like this, I would like to count it as
output

client_ip _value
10.10.11.11 3
10.10.11.12 3
10.20.13.15 1
10.20.13.16 1
10.20.13.18 2
With this output, how do you write the statement，Tried many times without success Please help me.

My query looks like this I’m using influxdb version 2.7

from(bucket: "nginx_prod")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "nginx_log")
  |> filter(fn: (r) => r["server_name"] == "www.test.com")
  |> filter(fn: (r) => r["_field"] == "client_ip")
  |> group(columns: ["_field"])
  |> count(column: "_value")

But the result obtained is this

WX20240807-133456
WX20240807-1334561340×133 12.4 KB

2

@tengdagg Since client_ip is a field, you need to restructure your data a little bit. Let me explain–the result of this query:

from(bucket: "nginx_prod")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "nginx_log")
  |> filter(fn: (r) => r["server_name"] == "www.test.com")
  |> filter(fn: (r) => r["_field"] == "client_ip")

Will look something like this:

_measurement _time server_name _field _value
nginx_log 2024-08-07T23:25:48Z www.test.com client_ip 10.10.11.11
nginx_log 2024-08-07T23:25:49Z www.test.com client_ip 10.10.11.11
nginx_log 2024-08-07T23:25:50Z www.test.com client_ip 10.10.11.11
nginx_log 2024-08-07T23:25:51Z www.test.com client_ip 10.10.13.12
nginx_log 2024-08-07T23:25:52Z www.test.com client_ip 10.10.13.12
nginx_log 2024-08-07T23:25:53Z www.test.com client_ip 10.10.13.12
nginx_log 2024-08-07T23:25:54Z www.test.com client_ip 10.20.13.15
nginx_log 2024-08-07T23:25:55Z www.test.com client_ip 10.20.13.16
nginx_log 2024-08-07T23:25:56Z www.test.com client_ip 10.20.13.18
nginx_log 2024-08-07T23:25:57Z www.test.com client_ip 10.20.13.18

You need to:

  1. Duplicate the _value column as client_ip.
  2. Group by the new client_ip column.
  3. Run count() on the newly grouped data to get a count per unique values of client_ip.
  4. “Ungroup” the data to return everything in a single table.
from(bucket: "nginx_prod")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "nginx_log")
  |> filter(fn: (r) => r["server_name"] == "www.test.com")
  |> filter(fn: (r) => r["_field"] == "client_ip")
  |> duplicate(column: "_value", as: "client_ip")
  |> group(columns: ["client_ip"])
  |> count()
  |> group()

Using the data above, this query returns:

client_ip _value
10.10.11.11 3
10.10.13.12 3
10.20.13.15 1
10.20.13.16 1
10.20.13.18 2
3

@scott
extremely grateful，The way you did it was the right way.