Hubitat > NodeRed > MySQL > Grafana (LONG READ)

[marktheknife]

sorry about that. will pm you to try to figure it out. Thanks again.

[jon1]

You might check out advance-logger node, it should be able to replace the lower two rows of nodes in your flow there all doing log rotation etc.

[stephack]

@stephack

I wanted my log data to import easily into Excel but the advanced logger insisted on adding it's own timestamp, logLevel and other info that made it unnecessarily complicated. Instead I just stripped out the data I needed into a delimited text file that could be easily imported into excel.

I did but it wouldn't cooperate with my specific use case.

[potts.mike]

has anyone used the new post feature of the maker api with Node Red yet?

[stephack]

I have, and it worked perfectly. I only connected a few devices on my dev hub to test the functionality but it performed perfectly with that scenario.

[corerootedxb]

Yup! I have ~175 devices hitting it in NR right now (including location events).

[corerootedxb]

Sooo, out of curiosity, has anyone tried this yet? The rabbit hole just turned into a sinkhole!

node-red-contrib-alexa-remote2

I just hooked it up and WOW!!! The amount of things you can do is mind boggling.

[potts.mike]

Do you mind sharing your flow? Specifically the Influxdb portion.

[corerootedxb]

Sure. Give me a few hours as I'm not at home ATM.

[corerootedxb]

Here is the Influx DB portion:

[{"id":"e4fc6910.8ae478","type":"tab","label":"[Prod] HE Events","disabled":false,"info":""},{"id":"6f93ff47.c7677","type":"http in","z":"e4fc6910.8ae478","name":"Incoming Event","url":"/incoming","method":"post","upload":false,"swaggerDoc":"","x":80,"y":100,"wires":[["122b7866.e80058"]]},{"id":"30fc763e.cf9dba","type":"debug","z":"e4fc6910.8ae478","d":true,"name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":390,"y":40,"wires":[]},{"id":"4ca21a51.724a04","type":"filter","z":"e4fc6910.8ae478","name":"Filter For InfluxDB","property":"payload.content.name","propertyType":"msg","asArray":false,"itemProperty":"","itemPropertyType":"item","rules":[{"t":"eq","v":"acceleration","vt":"str","output":1},{"t":"eq","v":"battery","vt":"str","output":1},{"t":"eq","v":"presence","vt":"str","output":1},{"t":"eq","v":"contact","vt":"str","output":1},{"t":"eq","v":"door","vt":"str","output":1},{"t":"eq","v":"humidity","vt":"str","output":1},{"t":"eq","v":"illuminance","vt":"str","output":1},{"t":"eq","v":"lock","vt":"str","output":1},{"t":"eq","v":"motion","vt":"str","output":1},{"t":"eq","v":"status","vt":"str","output":1},{"t":"eq","v":"power","vt":"str","output":1},{"t":"eq","v":"current","vt":"str","output":1},{"t":"eq","v":"powerFactor","vt":"str","output":1},{"t":"eq","v":"pressure","vt":"str","output":1},{"t":"eq","v":"lqi","vt":"str","output":1},{"t":"eq","v":"rssi","vt":"str","output":1},{"t":"eq","v":"sleeping","vt":"str","output":1},{"t":"eq","v":"tamper","vt":"str","output":1},{"t":"eq","v":"temperature","vt":"str","output":1},{"t":"eq","v":"threeAxis","vt":"str","output":1},{"t":"eq","v":"touch","vt":"str","output":1},{"t":"eq","v":"ultravioletIndex","vt":"str","output":1},{"t":"eq","v":"contact","vt":"str","output":1},{"t":"eq","v":"voltage","vt":"str","output":1},{"t":"eq","v":"water","vt":"str","output":1},{"t":"eq","v":"switch","vt":"str","output":1},{"t":"eq","v":"level","vt":"str","output":1}],"checkall":"false","outputs":1,"x":410,"y":100,"wires":[["46a544bc.c6502c"]]},{"id":"46a544bc.c6502c","type":"function","z":"e4fc6910.8ae478","name":"Set Influx Data","func":"var p = msg.payload.content;\n\nconst ACTIVE = 'active';\nconst CLOSED = 'closed';\nconst DETECTED = 'detected';\nconst FOLLOW_SCHEDULE = 'follow schedule';\nconst GOOD = 'good';\nconst HEATING = 'heating';\nconst LOCKED = 'locked';\nconst MUTED = 'muted';\nconst OFF = 'off';\nconst ON = 'on';\nconst OPEN = 'open';\nconst PRESENT = 'present';\nconst PUSHED = 'pushed';\nconst SLEEPING = 'sleeping';\nconst TOUCHED = 'touched';\nconst WET = 'wet';\n\nvar v = {\n 'value' : null,\n 'isBinary' : null\n};\n\nif(p.name == "acceleration") {\n v.value = (p.value == ACTIVE ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "alarm") {\n v.value = (p.value != OFF ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "button") {\n v.value = (p.value != PUSHED ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "carbonMonoxide") {\n v.value = (p.value == DETECTED ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "consumableStatus") {\n v.value = (p.value == GOOD ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "contact") {\n v.value = (p.value == OPEN ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "door") {\n v.value = (p.value != OPEN ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "lock") {\n v.value = (p.value == LOCKED ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "motion") {\n v.value = (p.value == ACTIVE ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "mute") {\n v.value = (p.value == MUTED ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "presence") {\n v.value = (p.value == PRESENT ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "shock") {\n v.value = (p.value == DETECTED ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "sleeping") {\n v.value = (p.value == SLEEPING ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "smoke") {\n v.value = (p.value == DETECTED ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "sound") {\n v.value = (p.value == DETECTED ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "switch") {\n v.value = (p.value == ON ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "tamper") {\n v.value = (p.value == DETECTED ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "thermostatMode") {\n v.value = (p.value != OFF ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "thermostatFanMode") {\n v.value = (p.value != OFF ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "thermostatOperatingState") {\n v.value = (p.value == HEATING ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "thermostatSetpointMode") {\n v.value = (p.value != FOLLOW_SCHEDULE ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "touch") {\n v.value = (p.value != TOUCHED ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "optimisation") {\n v.value = (p.value == ACTIVE ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "windowFunction") {\n v.value = (p.value == ACTIVE ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "water") {\n v.value = (p.value == WET ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "windowShade") {\n v.value = (p.value == CLOSED ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "threeAxis") {\n /var vz = p.value.split(',');\n var x = vz[0];\n var y = vz[1];\n var z = vz[2];\n v.value = {\n x: vz[0],\n y: vz[1],\n z: vz[2]\n };/\n v.value = p.value;\n v.isBinary = false;\n}\n// match if any characters are not digits, period, comma, or hyphen.\nelse if (typeof p.value === 'string' && p.value.match(/.[^0-9\.,-]./)) {\n v.value = '"' + p.value + '"';\n v.isBinary = false;\n}\n\n// Catch any other general numerical event (carbonDioxide, power, energy, humidity, level, temperature, ultravioletIndex, voltage, etc).\nelse {\n v.value = (p.name == 'temperature') ? parseFloat(p.value) : Number(p.value);\n v.isBinary = false;\n}\n\nmsg.measurement= p.name;\nmsg.timestamp = new Date();\n\nif (v.isBinary) {\n msg.payload = [\n {\n value: p.value,\n valueBinary: v.value\n },\n {\n deviceId: p.deviceId,\n displayName: p.displayName,\n unit: p.unit\n }\n ];\n}\nelse {\n msg.payload = [\n {\n value: (Array.isArray(v.value) ? v.value.join(',') : v.value),\n },\n {\n deviceId: p.deviceId,\n displayName: p.displayName,\n unit: p.unit\n }];\n}\nreturn msg;","outputs":1,"noerr":0,"x":600,"y":100,"wires":[["e66f1a81.f1f1c8"]]},{"id":"122b7866.e80058","type":"json","z":"e4fc6910.8ae478","name":"","property":"payload.content","action":"obj","pretty":false,"x":230,"y":100,"wires":[["4ca21a51.724a04","30fc763e.cf9dba"]]},{"id":"e66f1a81.f1f1c8","type":"influxdb out","z":"e4fc6910.8ae478","influxdb":"5152929f.bebe0c","name":"","measurement":"","precision":"","retentionPolicy":"","x":820,"y":100,"wires":[]},{"id":"5152929f.bebe0c","type":"influxdb","z":"","hostname":"127.0.0.1","port":"8086","protocol":"http","database":"hubitat","name":"","usetls":false,"tls":""}]

[potts.mike]

@corerootedxb

[{"id":"e4fc6910.8ae478","type":"tab","label":"[Prod] HE Events","disabled":false,"info":""},{"id":"6f93ff47.c7677","type":"http in","z":"e4fc6910.8ae478","name":"Incoming Event","url":"/incoming","method":"post","upload":false,"swaggerDoc":"","x":80,"y":100,"wires":[["122b7866.e80058"]]},{"id":"30fc763e.cf9dba","type":"debug","z":"e4fc6910.8ae478","d":true,"name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":390,"y":40,"wires":},{"id":"4ca21a51.724a04","type":"filter","z":"e4fc6910.8ae478","name":"Filter For InfluxDB","property":"payload.content.name","propertyType":"msg","asArray":false,"itemProperty":"","itemPropertyType":"item","rules":[{"t":"eq","v":"acceleration","vt":"str","output":1},{"t":"eq","v":"battery","vt":"str","output":1},{"t":"eq","v":"presence","vt":"str","output":1},{"t":"eq","v":"contact","vt":"str","output":1},{"t":"eq","v":"door","vt":"str","output":1},{"t":"eq","v":"humidity","vt":"str","output":1},{"t":"eq","v":"illuminance","vt":"str","output":1},{"t":"eq","v":"lock","vt":"str","output":1},{"t":"eq","v":"motion","vt":"str","output":1},{"t":"eq","v":"status","vt":"str","output":1},{"t":"eq","v":"power","vt":"str","output":1},{"t":"eq","v":"current","vt":"str","output":1},{"t":"eq","v":"powerFactor","vt":"str","output":1},{"t":"eq","v":"pressure","vt":"str","output":1},{"t":"eq","v":"lqi","vt":"str","output":1},{"t":"eq","v":"rssi","vt":"str","output":1},{"t":"eq","v":"sleeping","vt":"str","output":1},{"t":"eq","v":"tamper","vt":"str","output":1},{"t":"eq","v":"temperature","vt":"str","output":1},{"t":"eq","v":"threeAxis","vt":"str","output":1},{"t":"eq","v":"touch","vt":"str","output":1},{"t":"eq","v":"ultravioletIndex","vt":"str","output":1},{"t":"eq","v":"contact","vt":"str","output":1},{"t":"eq","v":"voltage","vt":"str","output":1},{"t":"eq","v":"water","vt":"str","output":1},{"t":"eq","v":"switch","vt":"str","output":1},{"t":"eq","v":"level","vt":"str","output":1}],"checkall":"false","outputs":1,"x":410,"y":100,"wires":[["46a544bc.c6502c"]]},{"id":"46a544bc.c6502c","type":"function","z":"e4fc6910.8ae478","name":"Set Influx Data","func":"var p = msg.payload.content;\n\nconst ACTIVE = 'active';\nconst CLOSED = 'closed';\nconst DETECTED = 'detected';\nconst FOLLOW_SCHEDULE = 'follow schedule';\nconst GOOD = 'good';\nconst HEATING = 'heating';\nconst LOCKED = 'locked';\nconst MUTED = 'muted';\nconst OFF = 'off';\nconst ON = 'on';\nconst OPEN = 'open';\nconst PRESENT = 'present';\nconst PUSHED = 'pushed';\nconst SLEEPING = 'sleeping';\nconst TOUCHED = 'touched';\nconst WET = 'wet';\n\nvar v = {\n 'value' : null,\n 'isBinary' : null\n};\n\nif(p.name == "acceleration") {\n v.value = (p.value == ACTIVE ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "alarm") {\n v.value = (p.value != OFF ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "button") {\n v.value = (p.value != PUSHED ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "carbonMonoxide") {\n v.value = (p.value == DETECTED ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "consumableStatus") {\n v.value = (p.value == GOOD ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "contact") {\n v.value = (p.value == OPEN ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "door") {\n v.value = (p.value != OPEN ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "lock") {\n v.value = (p.value == LOCKED ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "motion") {\n v.value = (p.value == ACTIVE ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "mute") {\n v.value = (p.value == MUTED ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "presence") {\n v.value = (p.value == PRESENT ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "shock") {\n v.value = (p.value == DETECTED ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "sleeping") {\n v.value = (p.value == SLEEPING ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "smoke") {\n v.value = (p.value == DETECTED ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "sound") {\n v.value = (p.value == DETECTED ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "switch") {\n v.value = (p.value == ON ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "tamper") {\n v.value = (p.value == DETECTED ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "thermostatMode") {\n v.value = (p.value != OFF ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "thermostatFanMode") {\n v.value = (p.value != OFF ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "thermostatOperatingState") {\n v.value = (p.value == HEATING ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "thermostatSetpointMode") {\n v.value = (p.value != FOLLOW_SCHEDULE ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "touch") {\n v.value = (p.value != TOUCHED ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "optimisation") {\n v.value = (p.value == ACTIVE ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "windowFunction") {\n v.value = (p.value == ACTIVE ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "water") {\n v.value = (p.value == WET ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "windowShade") {\n v.value = (p.value == CLOSED ? 1 : 0);\n v.isBinary = true;\n}\n\nelse if(p.name == "threeAxis") {\n / var vz = p.value.split(',');\n var x = vz[0];\n var y = vz[1];\n var z = vz[2];\n v.value = {\n x: vz[0],\n y: vz[1],\n z: vz[2]\n }; /\n v.value = p.value;\n v.isBinary = false;\n}\n// match if any characters are not digits, period, comma, or hyphen.\nelse if (typeof p.value === 'string' && p.value.match(/. [^0-9.,-]. /)) {\n v.value = '"' + p.value + '"';\n v.isBinary = false;\n}\n\n// Catch any other general numerical event (carbonDioxide, power, energy, humidity, level, temperature, ultravioletIndex, voltage, etc).\nelse {\n v.value = (p.name == 'temperature') ? parseFloat(p.value) : Number(p.value);\n v.isBinary = false;\n}\n\nmsg.measurement= p.name;\nmsg.timestamp = new Date();\n\nif (v.isBinary) {\n msg.payload = [\n {\n value: p.value,\n valueBinary: v.value\n },\n {\n deviceId: p.deviceId,\n displayName: p.displayName,\n unit: p.unit\n }\n ];\n}\nelse {\n msg.payload = [\n {\n value: (Array.isArray(v.value) ? v.value.join(',') : v.value),\n },\n {\n deviceId: p.deviceId,\n displayName: p.displayName,\n unit: p.unit\n }];\n}\nreturn msg;","outputs":1,"noerr":0,"x":600,"y":100,"wires":[["e66f1a81.f1f1c8"]]},{"id":"122b7866.e80058","type":"json","z":"e4fc6910.8ae478","name":"","property":"payload.content","action":"obj","pretty":false,"x":230,"y":100,"wires":[["4ca21a51.724a04","30fc763e.cf9dba"]]},{"id":"e66f1a81.f1f1c8","type":"influxdb out","z":"e4fc6910.8ae478","influxdb":"5152929f.bebe0c","name":"","measurement":"","precision":"","retentionPolicy":"","x":820,"y":100,"wires":},{"id":"5152929f.bebe0c","type":"influxdb","z":"","hostname":"127.0.0.1","port":"8086","protocol":"http","database":"hubitat","name":"","usetls":false,"tls":""}]

I think there is space in acceleration somewhere.

[corerootedxb]

Ahhh, yeah. I'll fix that. I just pulled it out of my main flow as there's tons of other stuff in there that I am still working on.

[dan.t]

It is the Discourse... it does some kind of formatting when you post it. I’d host on github and share a link here. I had the same problem before

[jon1]

When posting big chunks of code, or any code really, use the </> preformatted text tags

[kilowatts]

@corerootedxb

You could use a single RPi. I just don't recommend it as NodeRed+MySQL+Grafana will chew through 1GB of RAM and trash a cheap sd card easily. If you can separate the servers out, that'd be a lot better.

My RPi4 has 4GB of RAM. If I were to keep the OS on the SDcard and write the data to a connected USB flash drive, would that be a smidge better?

*edit: Went ahead and did this with a 128GB USB 3.0 flash drive. It should be easier on the sdcard, but maybe not the flash drive. I'll report back if any issues, but might start looking into regular database backups

[billmeek]

I'm using a Raspberry Pi 3 with one of the 250 gig Western Digital PiDrives (external hard drives). If I was using a Raspberry Pi 4 with the USB 3 ports, I'd likely just buy a WD external portable hard drive and (if needed) use a externally powered hub to make sure there is adequate power for the drive. A 4 TB WD portable drive is less than $100. I've got over 30 of the WD portable drives in use between here and customer sites which have been reliable to date. Full disclosure: I did have to replace one WD portable drive... after the customer knocked it off their desk onto the hardwood floor.

[kilowatts]

4TB... Whew. How much space are these metrics eating up? Think I could fill 4TB of house data in a lifetime?

[billmeek]

I usually buy what has the best cost per TB. I can always use the space for other things. There's pricing for 1/2/3/4/5 TB drives on this page:

Amazon.com: WD 4TB Elements Portable External Hard Drive - USB 3.0 -...

[mike]

I've got two hubs, but currently am only logging one hub. I noticed that there is HUB ID in the database, that is empty.

Where would I update the hub id?

The orange text, the green text, or elsewhere? (Obviously under hubId - line 12)

[ritchierich]

@mike

I noticed that there is HUB ID in the database, that is empty.

I’ve edited mine a bit. Do you have a set values function before that one? It’s on first line.