I get the same error. You may just need to wait until @corerootedxb posts it again.
1 Like
Try this:
JSON
[{
"id": "97f64ca.d04b7b",
"type": "tab",
"label": "Hubitat Logging",
"disabled": false,
"info": ""
},
{
"id": "a76fb58c.e649d8",
"type": "websocket in",
"z": "97f64ca.d04b7b",
"name": "Hubitat Logs WS",
"server": "",
"client": "a188bb97.72e918",
"x": 120,
"y": 80,
"wires": [
[
"ea94de21.c963d",
"b03a9a19.0765e8"
]
]
},
{
"id": "78ade93b.b1bac8",
"type": "mysql",
"z": "97f64ca.d04b7b",
"mydb": "b443bf10.3a478",
"name": "Logging DB",
"x": 1270,
"y": 80,
"wires": [
[]
]
},
{
"id": "b03a9a19.0765e8",
"type": "function",
"z": "97f64ca.d04b7b",
"name": "Create Logs INSERT",
"func": "var query = \"INSERT INTO logs(`name`,`msg`,`id`,`time`,`type`,`level`) VALUES(\";\n//var name = msg.name.replace(/'/g, \"\\'\");\n//var msg = msg.msg.replace(/'/g, \"\\'\");\n\nquery += \"'\" + msg.name + \"',\";\nquery += \"'\" + msg.msg + \"',\";\nquery += msg.id + ',';\nquery += \"'\" + msg.time + \"',\";\nquery += \"'\" + msg.type + \"',\";\nquery += \"'\" + msg.level + \"')\";\n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 640,
"y": 80,
"wires": [
[
"78ade93b.b1bac8"
]
]
},
{
"id": "1b825032.f16ee",
"type": "websocket in",
"z": "97f64ca.d04b7b",
"name": "Hubitat Events WS",
"server": "",
"client": "384bf512.4d2332",
"x": 130,
"y": 200,
"wires": [
[
"19d853b5.dd045c",
"f6d78ae6.9b33c8",
"837d6d2b.cd3d4"
]
]
},
{
"id": "837d6d2b.cd3d4",
"type": "function",
"z": "97f64ca.d04b7b",
"name": "Create Events INSERT",
"func": "var query = \"INSERT INTO events(`source`,`name`,`displayName`,`value`,`unit`,`deviceId`,`hubId`,`locationId`,`installedAppId`,`descriptionText`) VALUES(\";\n//var name = msg.name.replace(/'/g, \"\\'\");\n//var msg = msg.msg.replace(/'/g, \"\\'\");\n\nquery += \"'\" + msg.source + \"',\";\nquery += \"'\" + msg.name + \"',\";\nquery += \"'\" + msg.displayName + \"',\";\nquery += \"'\" + msg.value + \"',\";\nquery += \"'\" + msg.unit + \"',\";\nquery += msg.deviceId === null ? 'null,' : msg.deviceId + ',';\nquery += msg.hubId === null ? 'null,' : msg.hubId + ',';\nquery += msg.locationId === null ? 'null,' : msg.locationId + ',';\nquery += msg.installedAppId === null ? 'null,' : msg.installedAppId + ',';\nquery += msg.descriptionText === null ? 'null)' : \"'\" + msg.descriptionText + \"')\";\n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 640,
"y": 160,
"wires": [
[
"78ade93b.b1bac8"
]
]
},
{
"id": "e39467d7.6dc3e8",
"type": "catch",
"z": "97f64ca.d04b7b",
"name": "",
"scope": [
"1b825032.f16ee",
"a76fb58c.e649d8",
"78ade93b.b1bac8"
],
"x": 70,
"y": 720,
"wires": [
[
"5b6da7c4.6ec298"
]
]
},
{
"id": "5b6da7c4.6ec298",
"type": "change",
"z": "97f64ca.d04b7b",
"name": "Convert error.message to msg.payload",
"rules": [{
"t": "set",
"p": "payload",
"pt": "msg",
"to": "HE Logging has thrown an error",
"tot": "str"
}],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 330,
"y": 720,
"wires": [
[
"c3c2bc48.01bc5"
]
]
},
{
"id": "c3c2bc48.01bc5",
"type": "debug",
"z": "97f64ca.d04b7b",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "true",
"x": 590,
"y": 720,
"wires": []
},
{
"id": "46208e9a.ca969",
"type": "inject",
"z": "97f64ca.d04b7b",
"name": "",
"topic": "",
"payload": "",
"payloadType": "date",
"repeat": "900",
"crontab": "",
"once": true,
"onceDelay": 0.1,
"x": 110,
"y": 420,
"wires": [
[
"7990ff36.983b4"
]
]
},
{
"id": "7891807e.90163",
"type": "function",
"z": "97f64ca.d04b7b",
"name": "Create temp INSERT",
"func": "var query = \"INSERT INTO temperatures(deviceId, displayName, `value`) VALUES(\";\n\nquery += msg.payload.id + \",\";\nquery += \"'\" + msg.payload.label + \"',\";\nquery += msg.payload.attributes['temperature'];\nquery += \")\";\n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 1080,
"y": 420,
"wires": [
[
"ce199a1d.01e3a8"
]
]
},
{
"id": "7990ff36.983b4",
"type": "http request",
"z": "97f64ca.d04b7b",
"name": "Get Devices with Capabilites",
"method": "GET",
"ret": "obj",
"url": "",
"tls": "",
"x": 340,
"y": 420,
"wires": [
[
"b726ad7a.fa073"
]
]
},
{
"id": "b726ad7a.fa073",
"type": "function",
"z": "97f64ca.d04b7b",
"name": "Find Temperature Devices",
"func": "var devices = [];\nmsg.payload.forEach(function(device) {\n if(device.capabilities && device.capabilities[0] == 'TemperatureMeasurement')\n devices.push(device);\n});\n\nmsg.payload = devices;\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 610,
"y": 420,
"wires": [
[
"f1a7c34d.89ce1"
]
]
},
{
"id": "f1a7c34d.89ce1",
"type": "splitter",
"z": "97f64ca.d04b7b",
"name": "Split payload array",
"property": "payload",
"x": 850,
"y": 420,
"wires": [
[
"7891807e.90163"
]
]
},
{
"id": "ce199a1d.01e3a8",
"type": "mysql",
"z": "97f64ca.d04b7b",
"mydb": "b443bf10.3a478",
"name": "Logging DB",
"x": 1270,
"y": 420,
"wires": [
[]
]
},
{
"id": "ed81fd66.e69b7",
"type": "function",
"z": "97f64ca.d04b7b",
"name": "Find Battery Devices",
"func": "var devices = [];\nmsg.payload.forEach(function(device) {\n if(device.capabilities && device.capabilities[1] == 'Battery')\n devices.push(device);\n});\n\nmsg.payload = devices;\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 600,
"y": 560,
"wires": [
[
"7f6d0f15.9d5fa"
]
]
},
{
"id": "7f6d0f15.9d5fa",
"type": "splitter",
"z": "97f64ca.d04b7b",
"name": "Split payload array",
"property": "payload",
"x": 850,
"y": 560,
"wires": [
[
"36f8d654.2bf7ba"
]
]
},
{
"id": "36f8d654.2bf7ba",
"type": "function",
"z": "97f64ca.d04b7b",
"name": "Create battery INSERT",
"func": "var query = \"INSERT INTO batteries(deviceId, displayName, `value`) VALUES(\";\n\nquery += msg.payload.id + \",\";\nquery += \"'\" + msg.payload.label + \"',\";\nquery += msg.payload.attributes['battery'];\nquery += \")\";\n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 1080,
"y": 560,
"wires": [
[
"fd413858.152d38"
]
]
},
{
"id": "fd413858.152d38",
"type": "mysql",
"z": "97f64ca.d04b7b",
"mydb": "b443bf10.3a478",
"name": "Logging DB",
"x": 1270,
"y": 560,
"wires": [
[]
]
},
{
"id": "9a69f4a5.deaff8",
"type": "function",
"z": "97f64ca.d04b7b",
"name": "Create Motion INSERT",
"func": "var query = \"INSERT INTO motions(deviceId, displayName, `value`) VALUES(\";\n\nquery += msg.deviceId + \",\";\nquery += \"'\" + msg.displayName + \"',\";\nquery += \"'\" + msg.value + \"'\";\nquery += \")\";\n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 640,
"y": 200,
"wires": [
[
"78ade93b.b1bac8"
]
]
},
{
"id": "19d853b5.dd045c",
"type": "switch",
"z": "97f64ca.d04b7b",
"name": "Name switch",
"property": "name",
"propertyType": "msg",
"rules": [{
"t": "eq",
"v": "motion",
"vt": "str"
},
{
"t": "eq",
"v": "switch",
"vt": "str"
}
],
"checkall": "true",
"repair": false,
"outputs": 2,
"x": 410,
"y": 220,
"wires": [
[
"9a69f4a5.deaff8"
],
[
"cbbf29b1.b06698"
]
]
},
{
"id": "cbbf29b1.b06698",
"type": "function",
"z": "97f64ca.d04b7b",
"name": "Create Switch INSERT",
"func": "var query = \"INSERT INTO switches(deviceId, displayName, `value`) VALUES(\";\n\nquery += msg.deviceId + \",\";\nquery += \"'\" + msg.displayName + \"',\";\nquery += \"'\" + msg.value + \"'\";\nquery += \")\";\n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 640,
"y": 240,
"wires": [
[
"78ade93b.b1bac8"
]
]
},
{
"id": "20f2d767.c7b5a8",
"type": "comment",
"z": "97f64ca.d04b7b",
"name": "Use websockets to log all events and logs",
"info": "",
"x": 200,
"y": 40,
"wires": []
},
{
"id": "904730a4.46b0b",
"type": "comment",
"z": "97f64ca.d04b7b",
"name": "Switches on msg.name to log motions and switches",
"info": "",
"x": 950,
"y": 220,
"wires": []
},
{
"id": "1935fea3.313a31",
"type": "comment",
"z": "97f64ca.d04b7b",
"name": "Calls Maker API every 15 minutes to log temperatures. (Fixes issue with temps/batteries only updating when they want to via websockets)",
"info": "",
"x": 480,
"y": 380,
"wires": []
},
{
"id": "58f3eccf.6ebb34",
"type": "inject",
"z": "97f64ca.d04b7b",
"name": "",
"topic": "",
"payload": "",
"payloadType": "date",
"repeat": "21600",
"crontab": "",
"once": true,
"onceDelay": 0.1,
"x": 110,
"y": 560,
"wires": [
[
"13d86cdc.bb08a3"
]
]
},
{
"id": "13d86cdc.bb08a3",
"type": "http request",
"z": "97f64ca.d04b7b",
"name": "Get Devices with Capabilites",
"method": "GET",
"ret": "obj",
"url": "",
"tls": "",
"x": 340,
"y": 560,
"wires": [
[
"ed81fd66.e69b7"
]
]
},
{
"id": "4960662b.bc34c8",
"type": "comment",
"z": "97f64ca.d04b7b",
"name": "Calls Maker API every 6 hours to log batteries. (Fixes issue with temps/batteries only updating when they want to via websockets)",
"info": "",
"x": 450,
"y": 520,
"wires": []
},
{
"id": "ea94de21.c963d",
"type": "logger",
"z": "97f64ca.d04b7b",
"name": "Logs File Logger",
"filename": "/var/log/hubitat/logs.json",
"maxsize": "500",
"maxfiles": 10,
"complete": "payload",
"console": false,
"file": true,
"debug": false,
"zip": true,
"logtype": "info",
"x": 310,
"y": 120,
"wires": []
},
{
"id": "f6d78ae6.9b33c8",
"type": "logger",
"z": "97f64ca.d04b7b",
"name": "Events File Logger",
"filename": "/var/log/hubitat/events.json",
"maxsize": "500",
"maxfiles": 10,
"complete": "payload",
"console": false,
"file": true,
"debug": false,
"zip": true,
"logtype": "info",
"x": 310,
"y": 280,
"wires": []
},
{
"id": "311185f1.e9d61a",
"type": "comment",
"z": "97f64ca.d04b7b",
"name": "Log all entries to file on server for backup",
"info": "",
"x": 580,
"y": 120,
"wires": []
},
{
"id": "e5b4b2c1.92991",
"type": "comment",
"z": "97f64ca.d04b7b",
"name": "Log all entries to file on server for backup",
"info": "",
"x": 580,
"y": 280,
"wires": []
},
{
"id": "a188bb97.72e918",
"type": "websocket-client",
"z": "",
"path": "ws://192.168.1.110/logsocket",
"tls": "",
"wholemsg": "true"
},
{
"id": "b443bf10.3a478",
"type": "MySQLdatabase",
"z": "",
"host": "192.168.1.100",
"port": "3306",
"db": "hubitat_logging",
"tz": ""
},
{
"id": "384bf512.4d2332",
"type": "websocket-client",
"z": "",
"path": "ws://192.168.1.110/eventsocket",
"tls": "",
"wholemsg": "true"
}
]
I'm in! Thank you.
I have some unrecognized splitters. I know they are supposed to be splitting the payload but as is, they don't let me edit them. If I'm going to try to replace them with new splitters, I'd need help with the configuration. I searched this thread multiple times and can't see where it actually goes through setting up the splitters.
Looks like you may need to add:
that's exactly what was wrong. Thank you!
1 Like
Hello,
I managed to get much further into this project and I think I've hit what appears to be the final snag in writing to my DB.
I'm getting a bunch of errors on various device types that look similar to this:
I'm getting the errors on all the inserts. The only part that seems to be making it to the DB and Grafana is the create logs insert. I reviewed the inserts and they all look the same as the original imported flow from the first post.
I know exactly enough to be dangerous and a few tips to put me in the right path of debugging this would be appreciated. I'm so close!
My DB setup was created from the SQL in the first post so I am assuming that it's setup to receive this information properly.
Hey
This is how I use it:
hope is helping.
If not attach a catchNode to mySql and see what you get there
I have installed the node-red flow and found most of the problems so it it working except for the Temperature flow. The Battery flow is working and has data in sql. The temperature flow doesn't work. The sql data is empty. Here is the Hubitat-logging flow I am currently using.
Json
[
{
"id": "97f64ca.d04b7b",
"type": "tab",
"label": "Hubitat Logging",
"disabled": false,
"info": ""
},
{
"id": "a76fb58c.e649d8",
"type": "websocket in",
"z": "97f64ca.d04b7b",
"name": "Hubitat Logs WS",
"server": "",
"client": "a188bb97.72e918",
"x": 120,
"y": 80,
"wires": [
[
"ea94de21.c963d",
"b03a9a19.0765e8"
]
]
},
{
"id": "78ade93b.b1bac8",
"type": "mysql",
"z": "97f64ca.d04b7b",
"mydb": "b443bf10.3a478",
"name": "Logging DB",
"x": 1450,
"y": 200,
"wires": [
[]
]
},
{
"id": "b03a9a19.0765e8",
"type": "function",
"z": "97f64ca.d04b7b",
"name": "Create Logs INSERT",
"func": "var query = "INSERT INTO logs(name,msg,id,time,type,level) VALUES(";\n//var name = msg.name.replace(/'/g, "\'");\n//var msg = msg.msg.replace(/'/g, "\'");\n\nquery += "'" + msg.name + "',";\nquery += "'" + msg.msg + "',";\nquery += msg.id + ',';\nquery += "'" + msg.time + "',";\nquery += "'" + msg.type + "',";\nquery += "'" + msg.level + "')";\n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 640,
"y": 80,
"wires": [
[
"78ade93b.b1bac8"
]
]
},
{
"id": "1b825032.f16ee",
"type": "websocket in",
"z": "97f64ca.d04b7b",
"name": "Hubitat Events WS",
"server": "",
"client": "384bf512.4d2332",
"x": 130,
"y": 200,
"wires": [
[
"19d853b5.dd045c",
"f6d78ae6.9b33c8",
"837d6d2b.cd3d4"
]
]
},
{
"id": "837d6d2b.cd3d4",
"type": "function",
"z": "97f64ca.d04b7b",
"name": "Create Events INSERT",
"func": "var query = "INSERT INTO events(source,name,displayName,value,unit,deviceId,hubId,locationId,installedAppId,descriptionText) VALUES(";\n//var name = msg.name.replace(/'/g, "\'");\n//var msg = msg.msg.replace(/'/g, "\'");\n\nquery += "'" + msg.source + "',";\nquery += "'" + msg.name + "',";\nquery += "'" + msg.displayName + "',";\nquery += "'" + msg.value + "',";\nquery += "'" + msg.unit + "',";\nquery += msg.deviceId === null ? 'null,' : msg.deviceId + ',';\nquery += msg.hubId === null ? 'null,' : msg.hubId + ',';\nquery += msg.locationId === undefined ? 'null,' : msg.locationId + ',';\nquery += msg.installedAppId === null ? 'null,' : msg.installedAppId + ',';\nquery += msg.descriptionText === null ? 'null)' : "'" + msg.descriptionText + "')";\n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 630,
"y": 160,
"wires": [
[
"78ade93b.b1bac8"
]
]
},
{
"id": "e39467d7.6dc3e8",
"type": "catch",
"z": "97f64ca.d04b7b",
"name": "",
"scope": [
"1b825032.f16ee",
"a76fb58c.e649d8",
"78ade93b.b1bac8"
],
"x": 70,
"y": 720,
"wires": [
[
"5b6da7c4.6ec298"
]
]
},
{
"id": "5b6da7c4.6ec298",
"type": "change",
"z": "97f64ca.d04b7b",
"name": "Convert error.message to msg.payload",
"rules": [
{
"t": "set",
"p": "payload",
"pt": "msg",
"to": "HE Logging has thrown an error",
"tot": "str"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 510,
"y": 720,
"wires": [
[
"c3c2bc48.01bc5"
]
]
},
{
"id": "c3c2bc48.01bc5",
"type": "debug",
"z": "97f64ca.d04b7b",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "true",
"statusVal": "",
"statusType": "auto",
"x": 1050,
"y": 720,
"wires": []
},
{
"id": "46208e9a.ca969",
"type": "inject",
"z": "97f64ca.d04b7b",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "300",
"crontab": "",
"once": true,
"onceDelay": 0.1,
"topic": "",
"payloadType": "date",
"x": 110,
"y": 420,
"wires": [
[
"7990ff36.983b4"
]
]
},
{
"id": "7891807e.90163",
"type": "function",
"z": "97f64ca.d04b7b",
"name": "Create temp INSERT",
"func": "var query = "INSERT INTO temperatures(deviceId, displayName, value) VALUES(";\n\nquery += msg.payload.id + ",";\nquery += "'" + msg.payload.label + "',";\nquery += msg.payload.attributes['temperature'];\nquery += ")";\n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 1080,
"y": 420,
"wires": [
[
"ce199a1d.01e3a8"
]
]
},
{
"id": "7990ff36.983b4",
"type": "http request",
"z": "97f64ca.d04b7b",
"name": "Get Devices with Capabilites",
"method": "GET",
"ret": "obj",
"paytoqs": "ignore",
"url": "http://192.168.0.1/apps/api/XX/devices/all?access_token=XXXXXXXXXX",
"tls": "",
"persist": false,
"proxy": "",
"authType": "",
"senderr": false,
"x": 340,
"y": 420,
"wires": [
[
"b726ad7a.fa073"
]
]
},
{
"id": "b726ad7a.fa073",
"type": "function",
"z": "97f64ca.d04b7b",
"name": "Find Temperature Devices",
"func": "var devices = [];\nmsg.payload.forEach(function(device) {\n if(device.capabilities && device.capabilities[0] == 'TemperatureMeasurement')\n devices.push(device);\n});\n\nmsg.payload = devices;\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 610,
"y": 420,
"wires": [
[
"f1a7c34d.89ce1"
]
]
},
{
"id": "f1a7c34d.89ce1",
"type": "splitter",
"z": "97f64ca.d04b7b",
"name": "Split payload array",
"property": "payload",
"x": 850,
"y": 420,
"wires": [
[
"7891807e.90163"
]
]
},
{
"id": "ce199a1d.01e3a8",
"type": "mysql",
"z": "97f64ca.d04b7b",
"mydb": "b443bf10.3a478",
"name": "Logging DB",
"x": 1370,
"y": 420,
"wires": [
[]
]
},
{
"id": "ed81fd66.e69b7",
"type": "function",
"z": "97f64ca.d04b7b",
"name": "Find Battery Devices",
"func": "var devices = [];\nmsg.payload.forEach(function(device) {\n if(device.capabilities && device.capabilities[1] == 'Battery')\n devices.push(device);\n});\n\nmsg.payload = devices;\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 600,
"y": 560,
"wires": [
[
"7f6d0f15.9d5fa"
]
]
},
{
"id": "7f6d0f15.9d5fa",
"type": "splitter",
"z": "97f64ca.d04b7b",
"name": "Split payload array",
"property": "payload",
"x": 850,
"y": 560,
"wires": [
[
"36f8d654.2bf7ba"
]
]
},
{
"id": "36f8d654.2bf7ba",
"type": "function",
"z": "97f64ca.d04b7b",
"name": "Create battery INSERT",
"func": "var query = "INSERT INTO batteries(deviceId, displayName, value) VALUES(";\n\nquery += msg.payload.id + ",";\nquery += "'" + msg.payload.label + "',";\nquery += msg.payload.attributes['battery'];\nquery += ")";\n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 1080,
"y": 560,
"wires": [
[
"fd413858.152d38"
]
]
},
{
"id": "fd413858.152d38",
"type": "mysql",
"z": "97f64ca.d04b7b",
"mydb": "b443bf10.3a478",
"name": "Logging DB",
"x": 1370,
"y": 560,
"wires": [
[]
]
},
{
"id": "9a69f4a5.deaff8",
"type": "function",
"z": "97f64ca.d04b7b",
"name": "Create Motion INSERT",
"func": "var query = "INSERT INTO motions(deviceId, displayName, value) VALUES(";\n\nquery += msg.deviceId + ",";\nquery += "'" + msg.displayName + "',";\nquery += "'" + msg.value + "'";\nquery += ")";\n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 640,
"y": 200,
"wires": [
[
"78ade93b.b1bac8"
]
]
},
{
"id": "19d853b5.dd045c",
"type": "switch",
"z": "97f64ca.d04b7b",
"name": "Name switch",
"property": "name",
"propertyType": "msg",
"rules": [
{
"t": "eq",
"v": "motion",
"vt": "str"
},
{
"t": "eq",
"v": "switch",
"vt": "str"
}
],
"checkall": "true",
"repair": false,
"outputs": 2,
"x": 410,
"y": 220,
"wires": [
[
"9a69f4a5.deaff8"
],
[
"cbbf29b1.b06698"
]
]
},
{
"id": "cbbf29b1.b06698",
"type": "function",
"z": "97f64ca.d04b7b",
"name": "Create Switch INSERT",
"func": "var query = "INSERT INTO switches(deviceId, displayName, value) VALUES(";\n\nquery += msg.deviceId + ",";\nquery += "'" + msg.displayName + "',";\nquery += "'" + msg.value + "'";\nquery += ")";\n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 640,
"y": 240,
"wires": [
[
"78ade93b.b1bac8"
]
]
},
{
"id": "20f2d767.c7b5a8",
"type": "comment",
"z": "97f64ca.d04b7b",
"name": "Use websockets to log all events and logs",
"info": "",
"x": 200,
"y": 40,
"wires": []
},
{
"id": "904730a4.46b0b",
"type": "comment",
"z": "97f64ca.d04b7b",
"name": "Switches on msg.name to log motions and switches",
"info": "",
"x": 950,
"y": 220,
"wires": []
},
{
"id": "1935fea3.313a31",
"type": "comment",
"z": "97f64ca.d04b7b",
"name": "Calls Maker API every 15 minutes to log temperatures. (Fixes issue with temps/batteries only updating when they want to via websockets)",
"info": "",
"x": 480,
"y": 380,
"wires": []
},
{
"id": "58f3eccf.6ebb34",
"type": "inject",
"z": "97f64ca.d04b7b",
"name": "",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "21600",
"crontab": "",
"once": true,
"onceDelay": 0.1,
"topic": "",
"payloadType": "date",
"x": 110,
"y": 560,
"wires": [
[
"13d86cdc.bb08a3"
]
]
},
{
"id": "13d86cdc.bb08a3",
"type": "http request",
"z": "97f64ca.d04b7b",
"name": "Get Devices with Capabilites",
"method": "GET",
"ret": "obj",
"paytoqs": "ignore",
"url": "http://192.168.0.1/apps/api/XX/devices/all?access_token=XXXXXXXXXX",
"tls": "",
"persist": false,
"proxy": "",
"authType": "",
"senderr": false,
"x": 340,
"y": 560,
"wires": [
[
"ed81fd66.e69b7"
]
]
},
{
"id": "4960662b.bc34c8",
"type": "comment",
"z": "97f64ca.d04b7b",
"name": "Calls Maker API every 6 hours to log batteries. (Fixes issue with temps/batteries only updating when they want to via websockets)",
"info": "",
"x": 450,
"y": 520,
"wires": []
},
{
"id": "ea94de21.c963d",
"type": "logger",
"z": "97f64ca.d04b7b",
"name": "Logs File Logger",
"filename": "/hubitat/logs.json",
"maxsize": "500",
"maxfiles": 10,
"complete": "payload",
"console": false,
"file": true,
"debug": false,
"zip": true,
"logtype": "info",
"x": 310,
"y": 120,
"wires": []
},
{
"id": "f6d78ae6.9b33c8",
"type": "logger",
"z": "97f64ca.d04b7b",
"name": "Events File Logger",
"filename": "/hubitat/events.json",
"maxsize": "500",
"maxfiles": 10,
"complete": "payload",
"console": false,
"file": true,
"debug": false,
"zip": true,
"logtype": "info",
"x": 310,
"y": 280,
"wires": []
},
{
"id": "311185f1.e9d61a",
"type": "comment",
"z": "97f64ca.d04b7b",
"name": "Log all entries to file on server for backup",
"info": "",
"x": 580,
"y": 120,
"wires": []
},
{
"id": "e5b4b2c1.92991",
"type": "comment",
"z": "97f64ca.d04b7b",
"name": "Log all entries to file on server for backup",
"info": "",
"x": 580,
"y": 280,
"wires": []
},
{
"id": "a188bb97.72e918",
"type": "websocket-client",
"path": "ws://192.168.0.1/logsocket",
"tls": "",
"wholemsg": "true",
"hb": "0"
},
{
"id": "b443bf10.3a478",
"type": "MySQLdatabase",
"name": "",
"host": "localhost",
"port": "3306",
"db": "hubitat_logging",
"tz": "",
"charset": ""
},
{
"id": "384bf512.4d2332",
"type": "websocket-client",
"path": "ws://192.168.0.1/eventsocket",
"tls": "",
"wholemsg": "true",
"hb": "0"
}
]
I do have some event error messages from the event flow. It looks like some special characters in the events are causing database errors.
When I first started the event flow didn't work at all. All the events went to the error msg. I found my C5 hub was not sending locationId so that field was undefined instead of null. When I changed the locationId check to "undefined" instead of "null" the events flow started logging data.
UPDATE:
I have the temperature flow working by changing the "Find Temperature Devices" function from one function to 5 functions. I had to change the line (device.capabilities && device.capabilities[0] == 'TemperatureMeasurement') to (device.capabilities && device.capabilities[3] == 'TemperatureMeasurement') in the first temperature function. Then added 4 more temperature functions that used 4-7 in the second device.capablilities.
Wow i fell into the rabbit hole !
I got the data pushed into MySQL with node-red with succes! but I have no idea how to create the Dashboard in Grafana.
For instance, I try to create a graph of the outdoor temp.
SELECT
created as time,
value
from events
where (name = 'outdoorTemperature')
but it didn't work...I end up with a list of the readig...
Does any one can direct me to a guide or share some example of your SQL query?
thanks!
Is "outdoorTemperature" the device name or event name? I log temperature from my Hue Outdoor sensor and the name = 'temperature' and displayName is the name of the device that I am recording the reading for. I don't use Grafana but use the native Node-RED dashboard to do the graphs.
EDIT: Can you show what the results of your query look like?
outdoorTemperature is inside the table 'events', 'value' is the table where the actual temp is recorded
mysql> SELECT value from events where (name = 'outdoorTemperature') limit 10;
+-------+
| value |
+-------+
| -2 |
| -2 |
| -2 |
| -2 |
| -2 |
| -2 |
| -2 |
| -2 |
| -2.5 |
| -3.5 |
+-------+
I want to turn this request into a Time based line in Grafana
I believe you have to have the query return a time as well for Grafana to create a time based graph. You may want to try:
mysql> SELECT created, value from events where (name = 'outdoorTemperature') limit 10;
and see if that works.
What's the proper way to configure a RBE node for recording voltage? I only want to log voltage if it changed from previous value.
Does the device publish the event only when the value changes? If so, it should be different. If you are periodically pulling the value from the db, then one approach is to get the last value and compare before doing an insert...
The device is a shelly uni and it pushes voltages even if it hasn't changed... hmm I haven't investigated the driver, maybe it has an option to not send duplicates.
In some drivers (not sure about the Shelly device) there is an option to specify a net change value. If not, you can still use the approach of fetching the last value from the database and comparing the current and previous values.
I posted a couple of pics from the device event page and entries from the db. The events page looks like it's dropping the duplicate values but the db entries are not filtered.
I finally got the RBE stuff squared away and now dealing with Grafana. Complete newbie with Grafana but I can do some CLI sql queries. This is what my data looks like, someone please give me some pointers on how to create a time series graph with this info. Thanks!
Just started with this and ran into a few problems:
@1449smarthomeautomat if you haven't solved it already.
Specifically it seems that either newer version of HE os (or the C4 hardware version) does not generate a "locationId" value to the "Events" websocket.
The "Create Events INSERT" function looks for the 'locationId' in the ws output and inserts 'undefined' in the INSERT query.
I fixed the errors by removing the 'locationId' field from the "var query ..." declaration on line 1 of the "Create Events INSERT" function. (Copy/paste line 1 to create a duplicate & comment out '//' the original)
Secondly, comment out (// at begining of line) query += msg.locationId === null ? 'null,' : msg.locationId + ',';
ex: //query += msg.locationId === null ? 'null,' : msg.locationId + ',';
1 Like
