Multiple topics to one influxdb mesurement

General
, ,
1

Hello,

i use a mqtt node to receive multiple topics (pv-inverter/#) from an mqqt brocker (sensor data from microconverter).

pv-inverter/ (14 topics)
pv-inverter/HM800/ (6 Topics)
pv-inverter/HM800/ch1 (7 Topics)
pv-inverter/HM800/ch2 (7 Topics)
pv-inverter/HM800/ch0 (21 Topics)
pv-inverter/HM800/alarm (5 Topics)
pv-inverter/status (1 Topics)
pv-inverter/status/total (4 Topics)

Now i want to write all sensor data with the same timestamp from the different messages into one influxdb mesurement with one field for every single sensor data/mqtt message.

Example - this three messages comes at the same time.

{"topic":"pv-inverter/HM800/ch0/PF_AC","payload":"1","qos":0,"retain":false,"_msgid":"640830c6aa400679"}
{"topic":"pv-inverter/HM800/ch0/ALARM_MES_ID","payload":"21150","qos":0,"retain":false,"_msgid":"7e4a7d0a06bb4b7f"}
{"topic":"pv-inverter/total/P_DC","payload":"146.6","qos":0,"retain":false,"_msgid":"1b8cfe8bc63187c5"}

To inject this sensor data to influxdb i want to use a single msg

{
    topic: 'HM-800', 
    payload: {
        PF_AC: pv-inverter/HM800/ch0/PF_AC
        ALARM_MSG_ID: pv-inverter/HM800/ch0/ALARM_MES_ID
        P_DC: pv-inverter/total/P_DC
}

what is the right/shortest way?

2

Welcome to the forum @speerwerfer

Why do you want to do that? Why not just write them to separate measurements as you receive them?

3

Thank you for the welcome

My idea is, all sensor data reported at the same time by a device should also have the same timestamp in the influxdb.

If I have for example a weather station, then I thought so far that one collects these sensor data all in a mesurment.
The Bucket is Haussensorik (House sensors)
The Mesurement is for example Wetterstation (weatherstation)

grafik
grafik747×351 23.2 KB

4

Does the inverter publish them to MQTT directly?

5

in principle, yes. there is a receiving unit that receives the data from the microinverter via a proprietary protocol and passes it on to the brocker via mqtt. Unfortunately I can not modify the structure of the topics.

6

You can use a Join node in key/value mode to get them all into one message. Then you can reorganise the contents for influxdb.

See this article in the cookbook for an example of how to join messages into one object.

7

Thanks!
Is there an easy way to shorten the names of the elements? Here is the complete mqtt topic now.
Or should I change the names via a function node?

See the Result:

{
    "topic": "pv-anlage/HM-800/114190547764/0/temperature",
    "payload": {
        "pv-inverter/HM800/ 14190547764/name": "HM800",
        "pv-inverter/HM-800/114190547764/device/bootloaderversion": "104",
        "pv-inverter/HM-800/114190547764/device/fwbuildversion": "10010",
        "pv-inverter/HM-800/114190547764/device/fwbuilddatetime": "2021-11-01 08:42:00",
        "pv-inverter/HM-800/114190547764/device/hwpartnumber": "269565952",
        "pv-inverter/HM-800/114190547764/device/hwversion": "01.10",
        "pv-inverter/HM-800/114190547764/status/limit_relative": "100.00",
        "pv-inverter/HM-800/114190547764/status/limit_absolute": "800.00"
    },
    "qos": 0,
    "retain": false,
    "_msgid": "5b6f8b7e7290847e"
}
8

If you want the fields to be name, bootloaderversion, etc then in a function node you can iterate the keys using Object.keys(obj).forEach() as described in Iterating Through an Object with `forEach()` - Mastering JS, then for each one use split("/") on the property name and take the last element of the array as the new key name. Build a new object with the new field names and then replace the payload with the new one.

Should not several of those be tags rather than fields in influx? Also make sure you change the ones that are numbers from Strings to Numbers, though I am surprised that the MQTT node does not do that for you if you tell it to auto-detect the type.

9

Once again, thank you!

Yes, I still need to think about the database/table design.

Yes, if I use auto-detect, then only strings are generated. If I set "a parsed JSON Object", then the numbers are recognized. With the strings there are then errors. I can process them with Catch

10

I don't understand what you mean by that.

11

sorry, english is not my first language :slight_smile:

When i use in the mqtt node "output: auto-detect" then all messages contain the data type string.
When i use "output: a parsed JSON Object" then I receive only the messages that contain a number. Messages containing a string are not forwarded. The messages with a string as payload cause an error. I can then catch this error with a "Catch Node" and convert it to a correct message with a string as payload.

This is the Output from the "Catch Node"

{
    "payload": "2021-11-01 08:42:00",
    "topic": "pv-anlage/HM-800/114XXXXXXXXX/device/fwbuilddatetime",
    "qos": 0,
    "retain": false,
    "error": {
        "message": "Failed to parse JSON string",
        "source": {
            "id": "352394800a5ce834",
            "type": "mqtt in",
            "count": 1
        }
    },
    "_msgid": "10a31f3352d1c2f2"
}
12

This is probably the option you want
image

If you have not got that option then perhaps you are on an old version of node-red.

13

Great, that's it ...

14

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.