Additional examples for Wiki

[frankvandenhurk]

Hi,

I created some sample flows for KNX2MQTT, KNX2MySQL and KNX2InfluxDB. Could be nice to add to the wiki?

[ { "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" ] ] } ]

Statement to create the MySQL table:

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`) )

[Supergiovane]

Hi Frank,
thank you!
Could you please paste the code between brackets <>?

Otherwise, there will be errors.
Thank you!

[frankvandenhurk]

is this better?

[Supergiovane]

Yes, thank youy!

[Supergiovane]

Done!
https://github.com/Supergiovane/node-red-contrib-knx-ultimate/wiki/Sample-KNX2MQTT-KNX2MySQL-KNX2InfluxDB
In the next month, i'll take a look at your issue as well.