Nested JSON from MQTT and parsing json_v2 issue

[Hobbes]

Hello,
I really enjoy this great community, which while reading through it, helped me out setting up the whole outfit:
Tasmota SP111 → [mqtt] → Mosquitto → Telegraf → InfluxDB. However, I’ve been scratching my balding head for a longer while… I’ve read this and this multiple times, but… what I am still missing, I am still at loss. The case seems to be really simple, I want a tag from Topic - the Sensor ID, and one value from the JSON - here’s the MQTT Json I am trying to parse:

Topic: socks/SP111LaSock-01/SENSOR
{
  "Time": "2022-12-08T12:03:58",
  "ENERGY": {
    "TotalStartTime": "2022-12-07T09:19:06",
    "Total": 1.891,
    "Yesterday": 0.573,
    "Today": 1.318,
    "Period": 9,
    "Power": 348,
    "ApparentPower": 348,
    "ReactivePower": 0,
    "Factor": 1,
    "Voltage": 220,
    "Current": 1.583
  }
}

All I want from it is:
SENSOR=SP111LaSock-01, Power=348,

So I am testing this telegraf setup and can’t seem to get past it:

[agent]
  ..default..stuff..here
  hostname = ""
  omit_hostname = true
[[outputs.influxdb_v2]]
  urls = ["http://my.influxdb.at.home:8086"]
  token = "$INFLUX_TOKEN"
  organization = "Home"
  bucket = "MQTT"
[[inputs.mqtt_consumer]]
  servers = ["tcp://my.mosquitto.at.home:1883"]
  topics = [
    "/socks/+/SENSOR",
  ]
  username = "***"
  password = "***"
  data_format = "json_v2"
  [[inputs.mqtt_consumer.topic_parsing]]
        topic = "+/+/SENSOR"  # all topics with SENSOR
        tags = "_/sensor_id/_"
        measurement = "_/measurement/_"
  [[inputs.mqtt_consumer.json_v2]]
      [[inputs.mqtt_consumer.json_v2.field]]
          path = "ENERGY.Power"
          type = "int"

Right now, the result is that I don’t see anything in my InfluxDB bucket at all…
Could anyone hit me, for what I am doing wrong? Pls.
Thanks!

[jpowers]

@Hobbes

I don’t see anything in my InfluxDB bucket at all…

What I like to do in these cases is to use the [[outputs.file]] output, just to be sure it isn’t something wrong with InfluxDB, your query, or how you have filtering set up there. Try that and see if any metrics show up.

Next, to verify the JSON parsing, I put the data in a file and use a file input to verify the parsing:

[[inputs.file]]
  files = ["file.json"]
  data_format = "json_v2"
    [[inputs.file.json_v2]]
      [[inputs.file.json_v2.field]]
          path = "ENERGY.Power"
          type = "int"

Which seems to produce:

file,host=ryzen Power=348i 1670509760000000000

That looks good.

I would double check your topic settings and ensure they are correct.

@srebhan or others, any other thoughts?

[Hobbes]

Hi @jpowers - thanks a lot! Yes, that was my thought as well, and I put another section into the telegraf.config with some output to /tmp/metrics.out

[[outputs.file]]
  files = ["stdout", "/tmp/metrics.out"]
  data_format = "influx"
  influx_max_line_bytes = 0
  influx_sort_fields = false
  influx_uint_support = false

I am testing telegraf and influxdb on my local laptop - while the mosquitto sits on my homelab Linux server - fully accessible (I can see the messages flying through in the MQTTX client on my laptop hooked to mosquitto via mqtt port and auth); and I am able to get to the InfluxDB webUI as well. So, I am assuming my connectivity is not the culprit…

So, I see mesgs are flying through in the MQTTX client , the telegraf config seems to be Ok, I can access my local InfluxDB on the default port of 8086 …

but nothing comes in to neither influx or the output file.

[jpowers]

@Hobbes

but nothing comes in to neither influx or the output file.

This tends to mean that telegraf is either not finding any data at the topics you provided or that the JSON parsing is not working correctly. Since we verified that JSON parsing is working (assuming that data is good), my guess is something with the topic settings.

[Hobbes]

Hi, Hmmm…mkay. So… the topics lines are:

topics = [
    "/socks/+/SENSOR",
]

where I am trying to pick all the SENSOR topics (skipping the LWT and STATE)…
for example: socks/SP111LaSock-01/SENSOR (actually, I am copy-pasting it from the MQTTX UI)

and then below I am trying to parse the picked topics to get the sensor name as tag.

[[inputs.mqtt_consumer.topic_parsing]]
        topic = "+/+/SENSOR"  # all topics with SENSOR

…where I can see the beginning forward / is missing? Would that be it?

naaah… when I add the / into the topic = “+/+/SENSOR” # all topics with SENSOR - the telegraf throws an error: Error running agent: could not initialize input inputs.mqtt_consumer: config error topic parsing: measurement length does not equal topic length

[Hobbes]

Ok, I think I got it… thou, I am not sure WHY

this one works:

[[inputs.mqtt_consumer]]
  servers = ["tcp://mosq.nukelab.home:1883"]
  topics = ["+/+/SENSOR"]
  username = "mqtt"
  password = "wytest007"
  data_format = "json_v2"
  [[inputs.mqtt_consumer.topic_parsing]]
        topic = "+/+/+"  # all topics with SENSOR
        tags = "_/sensor_id/_"
  [[inputs.mqtt_consumer.json_v2]]
      [[inputs.mqtt_consumer.json_v2.field]]
          path = "ENERGY.Power"

producing the following output in the file:

~/Work/MQTT] $ tail -f /tmp/metrics.out 
mqtt_consumer,sensor_id=SP111KuSock-02,topic=socks/SP111KuSock-02/SENSOR Power=225 1670519164651412302
mqtt_consumer,sensor_id=SP111CoSock-01,topic=socks/SP111CoSock-01/SENSOR Power=26 1670519177561244318
mqtt_consumer,sensor_id=SP111SaSock-01,topic=socks/SP111SaSock-01/SENSOR Power=0 1670519208803260828
mqtt_consumer,sensor_id=SP111LaSock-01,topic=socks/SP111LaSock-01/SENSOR Power=2 1670519242075243151
mqtt_consumer,sensor_id=SP111KuSock-01,topic=socks/SP111KuSock-01/SENSOR Power=218 1670519258403384592

yet, nothing is coming to the Influxdb bucket… definitely, the telegraf HAS connectivity to Influx, as I am modifying the config through UI and running telegraf with the --config http://myInfluxIP:8086 Argh…

[jpowers]

@Hobbes

telegraf HAS connectivity to Influx, as I am modifying the config through UI and running telegraf with the --config http://myInfluxIP:8086

Can you run telegraf with --debug and then you look at the telegraf logs do you see a message saying “wrote N metrics” to the influxdb output?

[Hobbes]

Sure, good idea. Now this… hmm…
E! [outputs.influxdb_v2] When writing to [http://myInfluxIP:8086]: failed to write metric to MQTT (403 Forbidden): forbidden: insufficient permissions for write Interesting, there’s only me in the InfluxDB and me who created the bucket! But, we’re getting closer. Thank you so much!

Ok, I got it. Sometime earlier today while furiously troubleshooting my setup, I must have deleted and re-created the bucket, without re-creating the token. Somehow, the token worked when telegraf was pulling the config through API at the start telegraf --config .. but it didn’t allow to push messages. So, renewing the Token fixed the last obstacle.

Again, thank you so much @jpowers for your help and patience.

[jpowers]

Glad you got it working!

[srebhan]

@Hobbes and @jpowers the leading slash issue was resolved in fix(inputs.mqtt_consumer): topic parsing error when topic having prefix / by wuxingzhong · Pull Request #11527 · influxdata/telegraf (github.com) I think. So it would be good if you, @Hobbes, could try to run a recent nightly build to see if it works with your initial (with leading slash) configuration…

[Hobbes]

Hi @srebhan,
The initial setting with the trailing forward slash, comes from the error I was getting when and ONLY when I skipped that in the first topics=[".."] line in the mqtt.consumer section. The error was exactly what (I thought) was missing: [inputs.mqtt_consumer] Error in plugin: GJSON path is required. But apparently that error was thrown only if the initial part of the path was specified - the “socks”. Ultimately, when I came up with +/+/SENSOR, it let me do it without the slash… and worked

I will try the nightly build this Saturday and will let you know, for sure. Thank you.

[Hobbes]

Hello @srebhan,
So I’m just running the nightly build with the initial config (from the first post in this thread) and the fix in the nightly build is working I suppose. I can see metrics being dumped into the output file as well as into Influxdb.

docker run -e "INFLUX_TOKEN=*****" quay.io/influxdb/telegraf-nightly:alpine --debug --config http://mytestInfluxdb:8086/api/v2/telegrafs/...

$ docker exec -it e41c1c6f4541 cat /tmp/metrics.out
SP111CoSock-01,host=e41c1c6f4541,sensor_id=SP111CoSock-01,topic=socks/SP111CoSock-01/SENSOR Power=26i 1670670351950780137
SP111KuSock-02,host=e41c1c6f4541,sensor_id=SP111KuSock-02,topic=socks/SP111KuSock-02/SENSOR Power=223i 1670670373947042753
SP111LaSock-01,host=e41c1c6f4541,sensor_id=SP111LaSock-01,topic=socks/SP111LaSock-01/SENSOR Power=2i 1670670414265096291

@srebhan & @jpowers - so I managed to deploy all components (Mosquitto, Telegraf and InfluxDB) to Nomad/Consul/Vault cluster with all the secrets kept in Vault. I’ll share an URL with whole setup described when I put it on my blog. Thank you again!

[jpowers]

Thanks for following up. Let us know if you post that blog post!