Sum multiple columns without specifying names

Fluxlang
, ,
1

Hello,

I am trying to query the daily network traffic for a single device. I managed to get a big table with the network traffic for each access point on each channel. It looks like this:

grafik
grafik1742×603 49.9 KB

The query for the output from the picture is the following:

from(bucket: "Unifi")
|> range(start: 2023-04-29T00:00:00Z, stop: today())
|> filter(fn: (r) => r["_measurement"] == "clients")
|> filter(fn: (r) => r["name"] == "5e:7d:32:2e:33:76")
|> filter(fn: (r) => r["_field"] == "rx_bytes" or r["_field"] == "tx_bytes")
|> aggregateWindow(every: 1d, fn: spread, createEmpty: false)
|> pivot(rowKey:["_time"], columnKey: ["_field","ap_name","channel"], valueColumn: "_value")
|> drop(columns:["is_guest","is_wired","name","mac","oui","radio","radio_name","radio_proto","site_name","source","vlan","_start","_stop","sw_name","_measurement"])

I would like to sum all columns into one column.
So using the values from the picture I would like to have the following format for the first two rows:
2023-04-30 38728928
2023-05-01 40397539
…

I tried the group() and sum() functions but when using the sum() function I got the error: “column “_value” does not exist”. I guess its because I am using the pivot.

Also when a device connected to multiple access points during the day, they should also be summed to one row.
From
2023-05-12 52025607 922104984
2023-05-12 51225809 919753342
to
2023-05-12 1945109741

Does anyone have an idea how i can do that?

2

Hello @grindfiner,
you want to sum all the fields, then dont pivot.
Just group() and sum().
I’m not sure why you’re using a pivot. You usually only reserve that if you want to do math across fields or tags at the same timestamp.

If you want to sum a specific column other than _value you can specify it

    |> sum(column: "name")

But that would just give you the sum for one field after your pivot.

I hope that helps!

3

Thanks a lot!
I honestly don’t know anymore why I used the pivot function.

I used the group() and sum() functions, and now it is working as I want.
Just for completeness, here is my query:

from(bucket: "Unifi")
|> range(start: 2023-04-29T00:00:00Z, stop: today())
|> filter(fn: (r) => r["_measurement"] == "clients")
|> filter(fn: (r) => r["name"] == "5e:7d:32:2e:33:76")
|> filter(fn: (r) => r["_field"] == "rx_bytes" or r["_field"] == "tx_bytes")
|> aggregateWindow(every: 1h, fn: spread, createEmpty: false)
|> drop(columns:["is_guest","is_wired","name","mac","oui","radio","radio_name","radio_proto","site_name","source","vlan","_start","_stop","sw_name"])
|>group(columns: ["_time"])
|>sum()
|>group()