Sample KNX2MQTT KNX2MySQL KNX2InfluxDB - Supergiovane/node-red-contrib-knx-ultimate GitHub Wiki
In this example, you can learn how to save KNX Data to InfluxDB, MYSQL, and send it to MQTT.
This example has been kindly by frankvandenhurk.
Feel free to ask him about this code.
First of all, you must creade the database, by pasting this query in the query editor:
CREATE TABLE `knx_messages` ( `id` BIGINT NOT NULL AUTO_INCREMENT, `timestamp` DATETIME(3) NOT NULL, `knx_destination` VARCHAR(32) DEFAULT NULL, `maingroupname` VARCHAR(64) DEFAULT NULL, `middlegroupname` VARCHAR(64) DEFAULT NULL, `ganame` VARCHAR(255) DEFAULT NULL, `payload` VARCHAR(128) DEFAULT NULL, `payload_unit` VARCHAR(16) DEFAULT NULL, `knx_dpt` VARCHAR(16) DEFAULT NULL, `knx_dptdesc` VARCHAR(128) DEFAULT NULL, `knx_source` VARCHAR(32) DEFAULT NULL, `knx_event` VARCHAR(32) DEFAULT NULL, `echoed` TINYINT(1) DEFAULT NULL, PRIMARY KEY (`id`) )
Then, copy and paste this code below, into your flow.
Remember also to install the Mysql, InfluxDB and MQTT nodes, via the palette manager.
View code
Adjust the nodes according to your setup
[ { "id": "0a6794b8dfd8132c", "type": "group", "z": "f6f2187d.f17ca8", "style": { "stroke": "#999999", "stroke-opacity": "1", "fill": "none", "fill-opacity": "1", "label": true, "label-position": "nw", "color": "#a4a4a4" }, "nodes": [ "7805e5873be91840", "abc8f4520b293625", "ca3523fe43fdadaf", "18fea65476aceebc", "e2e448f50344f959", "ac34a51c308f93f4", "9e2fbf5cf0c83090", "5e8662725d45fb6b", "7805e5873be91840", "ac34a51c308f93f4", "9e2fbf5cf0c83090" ], "x": 174, "y": 159, "w": 992, "h": 202 }, { "id": "7805e5873be91840", "type": "knxUltimate", "z": "f6f2187d.f17ca8", "g": "0a6794b8dfd8132c", "server": "", "topic": "", "setTopicType": "listenAllGA", "outputtopic": "", "dpt": "", "initialread": 0, "notifyreadrequest": false, "notifyresponse": true, "notifywrite": true, "notifyreadrequestalsorespondtobus": false, "notifyreadrequestalsorespondtobusdefaultvalueifnotinitialized": "0", "name": "", "outputtype": "update", "outputRBE": "false", "inputRBE": "false", "formatmultiplyvalue": 1, "formatnegativevalue": "leave", "formatdecimalsvalue": "3", "passthrough": "no", "sendMsgToKNXCode": "", "receiveMsgFromKNXCode": "", "listenallga": true, "x": 310, "y": 200, "wires": [ [ "abc8f4520b293625" ] ] }, { "id": "abc8f4520b293625", "type": "delay", "z": "f6f2187d.f17ca8", "g": "0a6794b8dfd8132c", "name": "", "pauseType": "rate", "timeout": "5", "timeoutUnits": "seconds", "rate": "25", "nbRateUnits": "1", "rateUnits": "second", "randomFirst": "1", "randomLast": "5", "randomUnits": "seconds", "drop": false, "allowrate": false, "outputs": 1, "x": 560, "y": 200, "wires": [ [ "ca3523fe43fdadaf", "18fea65476aceebc", "5e8662725d45fb6b" ] ] }, { "id": "ca3523fe43fdadaf", "type": "function", "z": "f6f2187d.f17ca8", "g": "0a6794b8dfd8132c", "name": "Prepare for InfluxDB", "func": "msg.measurement = msg.topic + \" \" + msg.devicename\nmsg.payload = msg.payload\nreturn msg;", "outputs": 1, "noerr": 0, "initialize": "", "finalize": "", "libs": [], "x": 800, "y": 200, "wires": [ [ "ac34a51c308f93f4" ] ] }, { "id": "18fea65476aceebc", "type": "function", "z": "f6f2187d.f17ca8", "g": "0a6794b8dfd8132c", "name": "Prepare for MQTT", "func": "var d = new Date();\nvar time = d.getTime()\nvar source = msg.knx.source;\nvar dpt = msg.knx.dpt;\nvar ga = msg.topic\nvar devicename = msg.devicename;\n\nif (msg.payloadmeasureunit == \"unknown\") {\n var unit = \"\";\n} else {\n // @ts-ignore\n var unit = \" \" + msg.payloadmeasureunit;\n};\nvar knx_textual = msg.payload + unit;\n\nif ((msg.payload === true) || (msg.payload === false)) {\n var value = Number(msg.payload);\n} else {\n // @ts-ignore\n var value = msg.payload;\n};\n\nvar part1rawx = String(devicename.match(/^.*\\>/));\nvar part2rawx = String(devicename.match(/\\>.*?\\)/));\nvar part3rawx = String(devicename.match(/\\).*/));\n\nvar part1raw = part1rawx.replace('/', '-');\nvar part2raw = part2rawx.replace('/', '-');\nvar part3raw = part3rawx.replace('/', '-');\n\nvar part1tmp = part1raw.replace('(', '');\nvar part1 = part1tmp.replace('->', '');\n\nvar part2tmp = part2raw.replace('>', '');\nvar part2 = part2tmp.replace(')', '');\n\nvar part3 = part3raw.replace(') ', '');\n\nvar nums = ga.split(\"/\");\n\nif (parseInt(nums[0]) < 10) {\n var num1 = \"0\" + nums[0] + \" \"\n} else {\n var num1 = nums[0] + \" \"\n};\n\nif (parseInt(nums[1]) < 10) {\n var num2 = \"0\" + nums[1] + \" \"\n} else {\n var num2 = nums[1] + \" \"\n};\n\nif (parseInt(nums[2]) < 10) {\n var num3 = \"0\" + nums[2] + \" \"\n} else {\n var num3 = nums[2] + \" \"\n};\n\nmsg.topic = \"knx/status/\" + num1 + part1 + \"/\" + num2 + part2 + \"/\" + num3 + part3;\n\nmsg.payload = {\n \"ts\": time,\n \"lc\": time,\n \"knx_src_addr\": source,\n \"knx_dpt\": dpt,\n \"GA\": ga,\n \"knx_textual\": knx_textual,\n \"val\": value,\n}\n\nreturn msg;\n\n\n\n\n", "outputs": 1, "noerr": 0, "initialize": "", "finalize": "", "libs": [], "x": 790, "y": 320, "wires": [ [ "e2e448f50344f959" ] ] }, { "id": "e2e448f50344f959", "type": "mqtt out", "z": "f6f2187d.f17ca8", "g": "0a6794b8dfd8132c", "name": "", "topic": "", "qos": "", "retain": "true", "respTopic": "", "contentType": "", "userProps": "", "correl": "", "expiry": "", "broker": "", "x": 1030, "y": 320, "wires": [] }, { "id": "ac34a51c308f93f4", "type": "influxdb out", "z": "f6f2187d.f17ca8", "g": "0a6794b8dfd8132c", "influxdb": "", "name": "KNX to InfluxDB", "measurement": "", "precision": "", "retentionPolicy": "", "database": "database", "precisionV18FluxV20": "ms", "retentionPolicyV18Flux": "", "org": "b2b1b37cec367896", "bucket": "TNF-NodeRed", "x": 1060, "y": 200, "wires": [] }, { "id": "9e2fbf5cf0c83090", "type": "mysql", "z": "f6f2187d.f17ca8", "g": "0a6794b8dfd8132c", "mydb": "", "name": "", "x": 1030, "y": 260, "wires": [ [] ] }, { "id": "5e8662725d45fb6b", "type": "function", "z": "f6f2187d.f17ca8", "g": "0a6794b8dfd8132c", "name": "Prepare for SQL", "func": "const esc = (s) => s?.toString().replace(/'/g, \"''\") ?? null;\nconst truncate = (label, value, maxLength) => {\n if (value == null) return null;\n const str = value.toString();\n if (str.length > maxLength) {\n node.warn(`${label} too long (${str.length}/${maxLength}): ${str}`);\n }\n return str.substring(0, maxLength);\n};\n\nconst knx = msg;\nif (typeof knx !== \"object\" || !knx.knx || !knx.gainfo) return null;\n\nconst timestamp = new Date().toISOString().slice(0, 19).replace(\"T\", \" \");\nconst val = knx.payload;\nconst valStr = (val === null || typeof val === \"undefined\") ? null : truncate(\"payload\", String(val).trim(), 128);\n\n// Truncate and warn for each field based on MySQL schema\nconst destination = truncate(\"knx_destination\", knx.knx.destination, 32);\nconst maingroupname = truncate(\"maingroupname\", knx.gainfo.maingroupname, 64);\nconst middlegroupname = truncate(\"middlegroupname\", knx.gainfo.middlegroupname, 64);\nconst ganame = truncate(\"ganame\", knx.gainfo.ganame, 255);\nconst payload_unit = truncate(\"payload_unit\", knx.payloadmeasureunit, 16);\nconst dpt = truncate(\"knx_dpt\", knx.knx.dpt, 16);\nconst dptdesc = truncate(\"knx_dptdesc\", knx.knx.dptdesc, 128);\nconst source = truncate(\"knx_source\", knx.knx.source, 32);\nconst event = truncate(\"knx_event\", knx.knx.event, 32);\n\nconst insertValues = [\n `'${timestamp}'`,\n `'${esc(destination)}'`,\n `'${esc(maingroupname)}'`,\n `'${esc(middlegroupname)}'`,\n `'${esc(ganame)}'`,\n valStr !== null ? `'${esc(valStr)}'` : \"NULL\",\n `'${esc(payload_unit)}'`,\n `'${esc(dpt)}'`,\n `'${esc(dptdesc)}'`,\n `'${esc(source)}'`,\n `'${esc(event)}'`,\n knx.echoed === true ? 1 : 0\n];\n\nmsg.topic = \"INSERT INTO knx_messages \" +\n \"(timestamp, knx_destination, maingroupname, middlegroupname, ganame, payload, payload_unit, knx_dpt, knx_dptdesc, knx_source, knx_event, echoed) \" +\n `VALUES (${insertValues.join(\", \")})`;\n\nmsg.payload = null;\nreturn msg;\n", "outputs": 1, "timeout": "", "noerr": 0, "initialize": "", "finalize": "", "libs": [], "x": 780, "y": 260, "wires": [ [ "9e2fbf5cf0c83090" ] ] } ]