MQTT integration with Telegraf/InfluxDB

dlaporte commented 5 years ago

Has anyone had any success logging genmon data into InfluxDB via Telegraf (not HA)? While I can get the data in as type "string", I'm having trouble getting the values to properly parse using anything else. Any help (or working configs!) would be greatly appreciated!

dlaporte commented 5 years ago

Also, if anyone has another way (not MQTT) of getting the data into Influx, I'm all ears!

jgyates commented 5 years ago

If you enabled the Genmon MQTT option on the Add-Ons page "JSON for Numerics" it will return numeric values instead of strings for all numeric items on the Status page. You can also export the numeric data in JSON format with the command "generator: status_num_json". To issue the command you can either use the myclient.py python library or issue a socket call to port 9082, sending the text "generator: status_num_json" .

dlaporte commented 5 years ago

Thanks for the quick response! I have tried JSON numeric option, but it doesn't seem to apply to all numeric values? I was able to get Battery/Utility voltage, but not much else.

jgyates commented 5 years ago

@dlaporte

Not exactly sure what you are seeing or how you are viewing it, more info may help me understand. In my testing I can not see an issue. I did check in an update that will allow us to test this a bit easier. If you type the following URL (note HTTP or HTTPS depending on your settings)

htttp://ipAddressofYourPi:8000/cmd/status_num_json

this is the command that genmqtt.py uses to extract numeric data. Note that each numeric entry will contain the following the JSON data for the status page. For example the frequency would be listed like this:

{"Frequency": {"type": "float", "value": 0.0}},

The non numeric call (htttp://ipAddressofYourPi:8000/cmd/status_json) will return this: {"Frequency": "0 Hz",}

As of V1.12.7 you can enter the status_num_json command in the browser and the web server will pass it along. I also removed a case where JSON was double parsed.

Let me know if you have any questions.

Jason

dlaporte commented 5 years ago

Thanks for adding that feature, made things much easier to debug on my end! My issue ended up being that influx didn't like each mqtt topic ending in "/value" and was colliding on that being the key field. I was able to get around the issue by adding "name_override" per measurement in telegraf.conf to allow for different types. For example:

[[inputs.mqtt_consumer]] name_override = "battery_voltage" servers = ["tcp://localhost:1883"] qos = 0 connection_timeout = "30s" topics = [ "generator/Status/Engine/Battery Voltage/value", ] persistent_session = false client_id = "" username = "abc" password = "xyz" insecure_skip_verify = true data_format = "value" data_type = "float"

Everything is working as expected now, thanks again!

jgyates commented 5 years ago

Glad to hear it. I am closing this thread but feel free to comment here if you have any questions on this topic.

lmamakos commented 5 years ago

I've managed to do this with Home Assistant, as that's in the middle of all my stuff. I don’t know if this is an option for you. For example, my power measurements that I extract from some other entities are stored in watts, and I get these stored as floats:

> show field keys from "W"
name: W
fieldKey        fieldType
--------        ---------
entity_id_str       string
friendly_name_str   string
icon_str        string
value           float

These get extracted from JSON payloads from MQTT messages. In home assistant, I use "templates" do to this, and make sure I cast the result to "float" to make sure it ends up as a number, not a text string before it's used as a value for a the sensor entity. So, something like this:

sensor:
  - name: 'Power Water Pump'
    platform: mqtt
    state_topic: dev/gem-012345
    unit_of_measurement: 'W'
    value_template: '{{ value_json.ch3_w | float | round(1) }}'

I realize this isn't an answer to the original question, but might be helpful if you decide to go down a different path to a solution. In the case of genmon, I'm still parsing the text strings and stripping out the textual units with text substitutions and then doing the | float | round(1) trick in the Home Assistant template expression to turn it into a number.

Ntampata commented 2 years ago

Hi @dlaporte

I was reading your thread, and i also get interested to store my controller data into influxdb database.

Did you manage to get it sorted?

Below are my doubts:

Do I need to configure only the: [[inputs.mqtt_consumer]] on telegraf.conf file to integrate the Mqtt (from genmon add-ons) with telegraf?
The [[inputs.influxdb]] at telegraf.conf file do I need to leave it (keep it default)?
Where I will use the url: http://localhost:8000/cmd/status_num_json to collect the Json data?
Do I also need to configure the influxdb.conf file?

These are the steps or configurations that I did, following your steps:

1st - I configured the TELEGRAF.CONF [[insputs.mqtt_consumer]] name_override = "battery_voltage" servers = ["tcp://127.0.0.1:1883"] topics = ["generator/Status/Engine/Battery Voltage/value",] qos = 0 connection_timeout = "30s" persistent_session = false client_id = "genmon" username = "pi" password = "raspberry" insecure_skip_verify = false data_format = "value" data_type = "float"

2nd – At Genmon [Add-Ons] I Set the MQTT integration on and the JSON for Numerics

I am not getting nothing on influxdb, I am missing something? I'm not yet familiar with databases, You support is welcome!

dlaporte commented 2 years ago

Here are the relevant entries from my telegraph.conf, hope it's helpful

[[outputs.influxdb]]
  urls = ["http://influxdb:8086"]
  database = "telegraf"
  retention_policy = ""
  write_consistency = "any"
  timeout = "5s"

[[inputs.mqtt_consumer]]
  name_override = "utility_voltage"
  servers = ["tcp://mosquitto:1883"]
  qos = 0
  connection_timeout = "30s"
  topics = [
    "generator/Status/Line/Utility Voltage/value"
  ]
  persistent_session = false
  client_id = ""
  username = "admin"
  password = "53n50r!"
  insecure_skip_verify = true
  #data_format = "json"
  data_format = "value"
  data_type = "integer"

[[inputs.mqtt_consumer]]
  name_override = "engine_rpm"
  servers = ["tcp://mosquitto:1883"]
  qos = 0
  connection_timeout = "30s"
  topics = [
    "generator/Status/Engine/RPM/value"
  ]
  persistent_session = false
  client_id = ""
  username = "admin"
  password = "53n50r!"
  insecure_skip_verify = true
  #data_format = "json"
  data_format = "value"
  data_type = "integer"

[[inputs.mqtt_consumer]]
  name_override = "output_voltage"
  servers = ["tcp://mosquitto:1883"]
  qos = 0
  connection_timeout = "30s"
  topics = [
    "generator/Status/Engine/Output Voltage/value"
  ]
  persistent_session = false
  client_id = ""
  username = "admin"
  password = "53n50r!"
  insecure_skip_verify = true
  #data_format = "json"
  data_format = "value"
  data_type = "integer"

[[inputs.mqtt_consumer]]
  name_override = "battery_voltage"
  servers = ["tcp://mosquitto:1883"]
  qos = 0
  connection_timeout = "30s"
  topics = [
    "generator/Status/Engine/Battery Voltage/value"
  ]
  persistent_session = false
  client_id = ""
  username = "admin"
  password = "53n50r!"
  insecure_skip_verify = true
  #data_format = "json"
  data_format = "value"
  data_type = "float"

[[inputs.mqtt_consumer]]
  name_override = "engine_frequency"
  servers = ["tcp://mosquitto:1883"]
  qos = 0
  connection_timeout = "30s"
  topics = [
    "generator/Status/Engine/Frequency/value"
  ]
  persistent_session = false
  client_id = ""
  username = "admin"
  password = "53n50r!"
  insecure_skip_verify = true
  #data_format = "json"
  data_format = "value"
  data_type = "float"

[[inputs.mqtt_consumer]]
  name_override = "generator_maintenance"
  servers = ["tcp://mosquitto:1883"]
  qos = 0
  connection_timeout = "30s"
  topics = [
    "generator/Maintenance/#"
  ]
  persistent_session = false
  client_id = ""
  username = "admin"
  password = "53n50r!"
  insecure_skip_verify = true
  #data_format = "json"
  data_format = "value"
  data_type = "string"

Ntampata commented 2 years ago

Hi @dlaporte

I have followed your instructions, i am still not getting nothing on influxdb when I look at measuremnts, but when I debug the telegraf, it says as info Loaded Inputs: cpu, diskio, kernel, mem, mqtt_consumer, see image below.

Did you configure the [[outputs.mqtt]]?

dlaporte commented 2 years ago

    I’m guessing you didn’t replace “mosquito” with the IP/host and of your MQTT server.
    —David ***@***.***

From: Haudálio JJ Portraite @.>Sent: Sunday, March 13, 2022 06:28To: jgyates/genmon @.>Cc: David LaPorte @.>; Mention @.>Subject: Re: [jgyates/genmon] MQTT integration with Telegraf/InfluxDB (#223) Hi @dlaporte I have followed your instructions, i am still not getting nothing on influxdb when I look at measuremnts, but when I debug the telegraf, it says as info Loaded Inputs: cpu, diskio, kernel, mem, mqtt_consumer, see image below. Did you configure the [[outputs.mqtt]]?

—Reply to this email directly, view it on GitHub, or unsubscribe.Triage notifications on the go with GitHub Mobile for iOS or Android. You are receiving this because you were mentioned.Message ID: @.***>

blalor commented 1 month ago

You can take the measurement from the topic, so you don't need so much boilerplate:


[[inputs.mqtt_consumer]]
  servers = [ "…" ]
  username = "…"
  password = "…"
  qos = 0

  topics = [
    "genmon/generator/Status/Engine/+/value",
    "genmon/generator/Status/Line/+/value",
  ]

  ## tag in which the full topic is stored
  topic_tag = "topic"

  # https://github.com/influxdata/telegraf/tree/v1.27.1/plugins/parsers/value
  data_format = "value"
  data_type = "float"

  [[inputs.mqtt_consumer.topic_parsing]]
    # genmon/generator/Status/Engine/RPM/value
    topic = "genmon/generator/Status/+/+/value"
    measurement = "_/_/_/measurement/_/_" # Engine, Line
    tags = "_/_/_/_/field/_"

  [[processors.pivot]]
      tag_key = "field"
      value_key = "value"

  [inputs.mqtt_consumer.tags]
    destination_bucket = "genmon"

The resulting bucket looks like this (ignore outage; that was an error from earlier; Engine and Line are relevant):

jgyates / genmon

MQTT integration with Telegraf/InfluxDB #223