Issues with Formatting json

[the-butterfry]

I'm trying to get the Format to correctly parse out json received with the File handler plugin.
Here's an example string that is being received.
{"Time":"2018-03-13T20:16:55","DS18B20":{"Temperature":19.8},"TempUnit":"C"}

All I want to do is pull out the {Temperature} data into the File, discarding the rest. I can't seem to make it work. Any tips?
P.S.
I can make {time} work just fine, but as soon as I try to transform {temperature} is where the problems starts.

[amotl]

Dear Cory,

thanks for reporting this. It is probably about the fact that "Temperature" is nested inside the main dictionary, right? Accessing the data through JSON Pointer might help here in the future, see also #303.

With kind regards,
Andreas.

[nzhillnet]

Hi,

I've been using mqttwarn to parse mqtt topic's to influxdb. Works perfectly.

I'm now looking at parsing nested JSON messages and this is where I get stuck (I'm a self taught hobbiest).

I see there is a workaround for nested JSON included in README.md. Would you be able to advise if that will work with multiple payloads? This is my received JSON message:

{ "Time":"2018-08-14T07:21:45", "DS18B20-1":{ "Id":"051693DD63FF", "Temperature":15.4 }, "DS18B20-2":{ "Id":"0516941253FF", "Temperature":14.8 }, "SI7021":{ "Temperature":12.2, "Humidity":99.9 }, "TempUnit":"C" }

I'm happy to "have a go" but if you could comment I'd appreciate it.

Many thanks,

Roland

[rgitzel]

@hillnet I wrote the workaround in the README. It will work for your situation -- multiple temperature sensors with just one Sonoff? nice, I want to try that one day :-) -- it will take just a little more coding.

The problem is that one message can create only one InfluxDb entry, by default. What you want is for each message to be processed twice. So we'll make mqttwarn do that.

Make two Python functions like in the example, one pulling out the "-1" value and the other pulling out the "-2" value. Then create two topic sections in mqttwarn.ini, but don't name them based on the topic (see https://github.com/jpmens/mqttwarn#the-__topic__-sections). "Temperature1" and "Temperature2" would do fine.

That should do it.

[nzhillnet]

Thanks @rgitzel , I'll have a go!
FYI, I'm running multiple sensors on a ESP8266 NodeMCU running the Tasmota firmware....
Appreciate you taking time to respond.

[nzhillnet]

Hi @rgitzel ,
I finally got back to this, and am fine with extracting one entry, but am failing at getting the message processed more than twice.

For reference, here is the nested JSON from TASMOTA

{
   "Time":"2018-09-07T21:29:44",
   "DS18B20":{
      "Temperature":13.4
   },
   "SI7021":{
      "Temperature":13.7,
      "Humidity":59.4
   },
   "BME280":{
      "Temperature":14.3,
      "Humidity":62.6,
      "Pressure":1016.7
   },
   "TempUnit":"C"
}

My functions.py entry looks like this:

import ast
import logging
import time
from datetime import datetime

def ds18b20_values(topic, data, srv=None):
    payload = ast.literal_eval(data["payload"])
    ts = datetime.strptime(payload["Time"], "%Y-%m-%dT%H:%M:%S")
    millis = long(time.mktime(ts.timetuple()) * 1000)
    temp = payload["DS18B20"]["Temperature"]
    d = dict( Topic = topic, Timestamp = millis, Temperature = temp )
    logging.debug(d)
    return d

def dht11_values(topic, data, srv=None):
    payload = ast.literal_eval(data["payload"])
    ts = datetime.strptime(payload["Time"], "%Y-%m-%dT%H:%M:%S")
    millis = long(time.mktime(ts.timetuple()) * 1000)
    temp = payload["SI7021"]["Humidity"]
    d = dict( Topic = topic, Timestamp = millis, Humidity = temp )
    logging.debug(d)
    return d

I took

then create two topic sections in mqttwarn.ini, but don't name them based on the topic

to mean the following, but I must have misunderstood as it dosn't "trap" the mqtt message:

[temperature1]
targets = log:info
alldata = ds18b20_values()
format  = weather,Topic={Topic} Temperature={Temperature} {Timestamp}

[humidity1]
targets = log:info
alldata = dht11_values()
format  = weather,Topic={Topic} Humidity={Humidity} {Timestamp}

The topic being published to mqtt is: tele/e3_datalogger/SENSOR

If you have time to provide further guidance I'd appreciate it.

Thanks.

[rgitzel]

Hi @hillnet, you're close!

Add topic = tele/e3_datalogger/SENSOR to both of the sections, and that should do it.

[humidity1]
topic = tele/e3_datalogger/SENSOR
targets = log:info
...

I would also rename your second function to si7021_values since it's a different sensor.

Cheers!

[nzhillnet]

Thank you @rgitzel . This is awesome...I was sooo close. It is now working as you documented.

I need to do some work to get the "format" correct for influxdb, but that won't take too much now I have these single line data inputs.

I appreciate you taking time to help.

--
Roland

[nzhillnet]

Aplogies for continuing this, and if I'm now off topic please say so.

As noted above I can now "un-nest" nested JSON payloads.

What I am struggling with is getting them inserted into influxdb. This is what I have in my mqttwarn.ini file (as a test before trying influxdb as the target):

[temperature1]
topic = tele/e3_datalogger/SENSOR
targets = file:log-me
alldata = ds18b20_values()
format  = temperature,topic={Topic} value={Temperature} {Timestamp}

This writes into the file as:
temperature,topic=tele/e3_datalogger/SENSOR value=19.6 1538337319000

This looks pefect for my current schema, but when I change the target to targets = influxdb:temperature

I get the following errors in my mqttwarn.log file:

2018-10-01 21:05:57,549 DEBUG [mqttwarn] Message received on tele/e3_datalogger/SENSOR: {"Time":"2018-10-01T09:05:50","DS18B20":{"Temperature":19.6},"SI7021":{"Temperature":19.8,"Humidity":57.7},"BME280":{"Temperature":20.2,"Humidity":58.8,"Pressure":1027.1},"TempUnit":"C"}
2018-10-01 21:05:57,550 DEBUG [mqttwarn] Section [temperature1] matches message on tele/e3_datalogger/SENSOR. Processing...
2018-10-01 21:05:57,553 DEBUG [functions] {'Topic': 'tele/e3_datalogger/SENSOR', 'Timestamp': 1538337950000L, 'Temperature': 19.6}
2018-10-01 21:05:57,554 DEBUG [mqttwarn] Message on tele/e3_datalogger/SENSOR going to influxdb:temperature
2018-10-01 21:05:57,554 DEBUG [mqttwarn] New `influxdb:temperature' job: tele/e3_datalogger/SENSOR
2018-10-01 21:05:57,554 DEBUG [mqttwarn] Processor #0 is handling: `influxdb' for temperature
2018-10-01 21:05:57,556 DEBUG [influxdb] *** MODULE=services/influxdb.pyc: service=influxdb, target=temperature
2018-10-01 21:05:57,558 DEBUG [connectionpool] Starting new HTTP connection (1): hillnet
2018-10-01 21:05:57,560 DEBUG [connectionpool] http://hillnet:8086 "POST /write?db=mqttwarn HTTP/1.1" 400 144
2018-10-01 21:05:57,562 WARNING [influxdb] POST request failed: (400) {"error":"unable to parse 'temperature,topic=tele_e3_datalogger_SENSOR value=temperature,topic=tele/e3_datalogger/SENSOR value=19.6 1538337950000': invalid boolean"}

2018-10-01 21:05:57,564 WARNING [mqttwarn] Notification of influxdb for `tele/e3_datalogger/SENSOR' FAILED or TIMED OUT

I've read the section on "Line Protocol" (and noted the format below) on the InfluxDB website, and it appears this should work, but I am obviously missing something.

+-----------+--------+-+---------+-+---------+
|measurement|,tag_set| |field_set| |timestamp|
+-----------+--------+-+---------+-+---------+

I don't (yet) understand why my config gets transformed into value=temperature which is where I think the error is coming from.

Aploogies for the "newbie" noise, but I hope one more steer will resolve this.

--
Roland

[rgitzel]

@hillnet Try changing your format:

format  = value={Temperature}

The plugin will insert a tag for topic (albeit with underscores instead of slashes). You can add other tags in there, see the plugin docs.

I suppose the examples I wrote are misleading, as they don't work with that plugin.

What I'm doing myself is writing full Influxdb lines out to topic influx/input. Meanwhile I am running Telegraf, which pulls the Influxdb lines from the topic and writes them to InfluxDb.

This gave me more control: I can use the timestamp from message, not from mqttwarn, and keep the original topic name.

I suppose another option would be to change the plugin to allow more control. Hmmm.

[nzhillnet]

Thank AGAIN @rgitzel, and yes I should have read the influxdb plugin guide last time :-(

The partial solution was to actually have format = {Temperature} (or format = {Temperature} {Timestamp}), as format = value={Temperature} added a double "value":

temperature,topic=tele_e3_datalogger_SENSOR value=value=16.4

My last remaining issue is to add a tag to each sensor "entity". At the moment I get 3 temperature values (1 per sensor, all bundled in the original nested JSON) and no way to identify them uniquely in grafana.

The influxdb plugin treatment of the format = setting isn't working as I initially thought, but looking into the influxdb.py file I can now see why:

measurement = item.addrs[0]
    tag         = "topic=" + item.topic.replace('/', '_')
    value       = item.message

    try:
        url = "http://%s:%d/write?db=%s" % (host, port, database)
        data = measurement + ',' + tag + ' value=' + value

I'll keep trying and see if I can come up with something...

I am now almost sorted and can use TASMOTA on all my devices knowing mqttwarn can parse the nested JSON data to influxdb.

--
Best regards,

Roland

[jwyse]

@hillnet You might consider pulling the latest version of mqttwarn. Judging by the code you copy/pasted from influxdb.py, you're not looking at the latest version (on your local machine, I assume?), which now includes some enhancements to the InfluxDB service (and expanded readme/format example).

[nzhillnet]

Thanks @jwyse ,

You are of course correct. Rookie mistake of not updating....

I cloned the repo today and now have success. For the record, my mqttwarn.ini entry looks like:

[temperature1]
topic = tele/e3_datalogger/SENSOR
targets = influxdb:temperature
alldata = ds18b20_values()
format  = entity=ds18b20 value={Temperature}

This gives the following log entry:

DEBUG [influxdb] temperature,topic=tele_e3_datalogger_SENSOR,entity=ds18b20 value=17.2

[Edit: deleted {Timestamp} from "format = " as grafana seems to have an issue with it. Using server timestamps for now]

I truely have success now.

FYI @rgitzel

Thanks for all the help.

mqttwarn is an awesome piece of software @jpmens & @amotl

--
Roland

[jacques42]

I totally second @hillnet on mqttwarn being fantastic work and also thanks for the influxdb service plugin - I use it frequently.

Most recently I came across a scenario where using the InfluxDB service pluging, along with a simple format string the Influx posting did not work. I am responding to this particular issue as reading through the above, it seems my scenario is related.

Different from the formerly discussed solutions, I did not want to add an extra tag as I am fine and sufficient with the topic itself being added as tag:

Inbound JSON String
{"RainTicks":"1"}

mqttwarn.ini

[MySensors/sensor-weather-1/Rain-1]
topic = MySensors/sensor-weather-1/Rain
targets = influxdb:influx-rain
format = {RainTicks}

which resulted in the following error log entry
2019-10-05 22:32:01,418 WARNING [influxdb] POST request failed: (400) {"error":"unable to parse 'rain,topic=MySensors_sensor-weather-1_Rain,1': missing tag value"}

Looking at the code in services/influxdb.py it currently seems assumes that by appending item.message after a comma, any message includes at least one more influx tag to start with:

# InfluxDB line protocol: 
# measurement,<tag_key>=<tag_value>,<tag_key>=<tag_value> <field_key>=<field_value>,<field_key>=<field_value> timestamp

55:         data = measurement + ',' + tag + ',' + item.message

This did not work for me, hence I've enhanced the code to deal with different format string scenario's and in particular when no additional tags are provided.

I'm not an expert coder but you may want to look at pull request #376 and eventually consider the changes to be added in the one or other way.

Thanks and best - Oliver

[amotl]

As @hillnet signals goodness

I truly have success now.

and #376 contributed by @jacques42 as been merged already, I believe it is safe to close this issue.

Please reopen if any of these things outlined within this discussion does not resolve the problem you have been observing, @the-butterfry.

[amotl]

P.S.: As both a side note and a shameless plug from my side, I would like to mention that Kotori also received a Tasmota decoder the other day through daq-tools/kotori@77b94fa1 and daq-tools/kotori@5f5d9511, see also [1,2].

[1] https://getkotori.org/docs/decoders/
[2] https://getkotori.org/docs/decoders/sonoff-tasmota.html