Summing multiple fields getting very slow

1

Hello,

I have measurement “table” with three data fields (that is energy of three phases) and one tag, which is basically device id. These field values are grouped by month and spread is used to get the difference, i.e. consumed energy.

But I need third data field which is the sum of those three separate energy fields (you always need total energy). The way I do it now is displayed below, but it is horribly slow and grafana always loses that final result field total_value each month and I have to update controls (it must have something with dates etc.)

Is there a better faster way? Timestamp of these values are identical because they are parsed from the same json in Telegraf. I always wanted to do that summing somewhere else, perhaps in Telegraf to avoid this cumbersome query, but not really sure if that is possible within Telegraf.

  import "date"
  import "timezone"

  option location = timezone.location(name: "Europe/Vilnius")

  startingDate = date.truncate(t: now(), unit: 1mo)
  
  nextMonth = date.add(d: 1mo, to: now())
  nextMonthTrunc = date.truncate(t: nextMonth, unit: 1mo)
  endMonth = date.sub(from: nextMonthTrunc, d: 1s)

  from(bucket: "${bucket_main}")
  |> range(start: startingDate, stop: endMonth)
  |> filter(fn: (r) => r["_measurement"] == "${measurement_energy}")
  |> filter(fn: (r) => r["device_id"] == "${device_2floor}")
  |> filter(fn: (r) => r["_field"] == "${general_a_total_act_energy}" or r["_field"] == "${general_b_total_act_energy}" or r["_field"] == "${general_c_total_act_energy}") 
  |> aggregateWindow(every: 1mo, fn: spread, createEmpty: true)
  |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({r with periodEndDate: date.sub(from: r._time, d: 1s)}))
  |> map(fn: (r) => ({r with total_value: r.${general_a_total_act_energy} + r.${general_b_total_act_energy} + r.${general_c_total_act_energy}}))
  |> yield(name: "total_energy")

Here is the snipper from Telegraf query.

[[inputs.mqtt_consumer]]
  servers = ["$MQTT_SERVER"]
  topics = [
      "device/3323FF9D/realtime",
      "device/82B85436/realtime"
  ]
  name_override = "energy"
  topic_tag = "topic"
  qos = 0
  connection_timeout = "60s"
  client_id = "telegraf-11"
  username = "$MQTT_USER"
  password = "$MQTT_PASS"
  client_trace = true
  data_format = "json_v2"
  [[inputs.mqtt_consumer.topic_parsing]]
    topic = "+/+/+"
    tags = "_/device_id/_"
  [[inputs.mqtt_consumer.json_v2]]
    [[inputs.mqtt_consumer.json_v2.field]]
      path = "Datas.0.3"
      rename = "a_total_act_energy"
      type = "float"
    [[inputs.mqtt_consumer.json_v2.field]]
      path = "Datas.1.3"
      rename = "b_total_act_energy"
      type = "float"
    [[inputs.mqtt_consumer.json_v2.field]]
      path = "Datas.2.3"
      rename = "c_total_act_energy"
      type = "float"

Thanks.

2

Hello @roflas,
Yes you can use the starlark processor plugin to calculate the total

[[inputs.mqtt_consumer]]
  servers = ["$MQTT_SERVER"]
  topics = [
      "device/3323FF9D/realtime",
      "device/82B85436/realtime"
  ]
  name_override = "energy"
  topic_tag = "topic"
  qos = 0
  connection_timeout = "60s"
  client_id = "telegraf-11"
  username = "$MQTT_USER"
  password = "$MQTT_PASS"
  client_trace = true
  data_format = "json_v2"
  [[inputs.mqtt_consumer.topic_parsing]]
    topic = "+/+/+"
    tags = "_/device_id/_"
  [[inputs.mqtt_consumer.json_v2]]
    [[inputs.mqtt_consumer.json_v2.field]]
      path = "Datas.0.3"
      rename = "a_total_act_energy"
      type = "float"
    [[inputs.mqtt_consumer.json_v2.field]]
      path = "Datas.1.3"
      rename = "b_total_act_energy"
      type = "float"
    [[inputs.mqtt_consumer.json_v2.field]]
      path = "Datas.2.3"
      rename = "c_total_act_energy"
      type = "float"

[[processors.starlark]]
  source = '''
def apply(metric):
    a_total = metric.fields.get("a_total_act_energy", 0.0)
    b_total = metric.fields.get("b_total_act_energy", 0.0)
    c_total = metric.fields.get("c_total_act_energy", 0.0)
    metric.fields["total_value"] = a_total + b_total + c_total
    return metric
  '''

And then you’d only hae to query for the total.
I haven’t tested that config but give starlark processor plugin a look!

3

That worked fine, @Anaisdg appreciate your help :slight_smile: I did not know anything about starlark, but now I know.

4

@roflas of course!
Out of curiosity what are you doing with InfluxDB? I love to learn about what community members are getting up to.