Hello,
This is my first post in this forum and I've learned enough about Node-Red on my own to be dangerous. I've gone down a delightfully convoluted rabbit hole to basically try to graph out various information coming out of my Hubitat hub. I certainly didn't design it, I'm just following footsteps and trying to debug along the way.
The project overview is here: Hubitat > NodeRed > MySQL > Grafana (LONG READ) - Integrations - Hubitat perhaps somebody here is familiar with this project or perhaps somebody here is familiar with a less invasive solution but at the end of the day this is a wonderful learning opportunity for me that I've enjoyed.
The flow I used:
[{
"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 fairly handy with this kind of stuff and some light guidance will go a long way with me. I'm sure I've come to the correct place of enthusiasts to help me
This being my first post here, I did read some of the tips for making a valuable post. I hope I provided enough info to at least convey to somebody where I am at and the statement of the problem in the correct format. If there's better ways I should be providing information, let me know; I'm flexible.
At the end of the day, what I am asking for is help with how to read/act upon the errors I'm seeing.