How to sum values from multiple host?

,
1

Hello,
I’m new to this new query model and I’m trying to sum 2 values in one.
Here is my actual query :

from(bucket: “monitoring”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “snmp”)
|> filter(fn: (r) => r[“_field”] == “L1”)
|> filter(fn: (r) => r[“agent_host”] == “h1” or r[“agent_host”] == “h2”)

if L1 value for h1 is 12 and l1 value for h2 is 14, i’d like to get 26 as query result
And to be complete, if I could divide this value by 10 it will be perfect.

2

Welcome @sybux to the Influx forum.

You need to get the h1 and h2 on the same row in order to do a calculation involving both of them. To do this, use the pivot() function. Then you can use the map() function to do your desired calculation.

Here’s an example with my data which contains Outdoor Temperature and Humidity:

from(bucket: "Bucket1")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "WeatherData")
  |> filter(fn: (r) => r["_field"] == "OutdoorTemp" or r["_field"] == "OutdoorHumidity")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "HumidityAndTemperature")

image
image1748×782 82.7 KB

and with these two functions added:

  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")  
  |> map(fn: (r) => ({ r with newColumn: r.OutdoorHumidity * r.OutdoorTemp }))

…we get this new query:

from(bucket: "Bucket1")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "WeatherData")
  |> filter(fn: (r) => r["_field"] == "OutdoorTemp" or r["_field"] == "OutdoorHumidity")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")  
  |> map(fn: (r) => ({ r with newColumn: r.OutdoorHumidity * r.OutdoorTemp }))  
  |> yield(name: "HumidityTimesTemperature")

image
image1748×782 85 KB

3

Sorry for this long reply, to much work and welcome holidays :slight_smile:

Thanks you for this example, it was perfect for me to do what I want. I have one last question, How can I change this query so it returns only the calculated field (HumidityTimesTemperature in your example).

4

Glad you got the results you wanted. You can drop or keep columns using drop or keep.