Hnmmm... No. It's a typo. It should just be an index. Not sure why it threw a unique constraint in there.
This the correct create SQL:
DROP TABLE IF EXISTS `motions`;
CREATE TABLE `motions` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`deviceId` int(10) DEFAULT NULL,
`displayName` varchar(500) DEFAULT NULL,
`value` varchar(20) DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `motions_deviceId_index` (`deviceId`),
KEY `motions_created_index` (`created`)
);
DROP TABLE IF EXISTS `switches`;
CREATE TABLE `switches` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`deviceId` int(10) DEFAULT NULL,
`displayName` varchar(500) DEFAULT NULL,
`value` varchar(20) DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `switches_deviceId_index` (`deviceId`),
KEY `switches_created_index` (`created`)
);
DROP TABLE IF EXISTS `temperatures`;
CREATE TABLE `temperatures` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`deviceId` int(11) DEFAULT NULL,
`displayName` varchar(500) DEFAULT NULL,
`value` decimal(10,2) DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `temperatures_deviceId_index` (`deviceId`),
KEY `temperatures_created_index` (`created`)
);
Sorry about that. I don't know what my Windows editor did. 
Working like a charm.
I'm still standing on the edge of it, but I can tell this Node Red rabbit hole is deep and, as they say, a maze of twisty little passages. I'm really intrigued.
1 Like
Yeah, it's an amazing tool. I've found so many uses for it and I know I am still just scratching the surface of it.
Here's another one I use. I use this to download my backup daily at 02:00.
1 Like
Next time you're in there, could you post a screenshot of your HTTP Request node settings for the backup? No matter what I do, I can't get the file contents out of it. I can see the file coming over to the Node-RED server in a tcpdump, but I'm not getting anything from a debug logger I've hooked up to the output nor the rest of the flow.
Thanks!
This is the string node that I use: node-red-contrib-string You can add it to your pallet (3 line menu > Manage Pallet > Install > node-red-contrib-string).
1 Like
Had it right, but it turns out I was blowing through the JS heap memory limit. Tuned that in the startup script and it's working beautifully. Thanks again!
1 Like
Hubitat Elevation Logging Utility has been created/updated. This is an extendable nodejs solution that does the same thing as the NodeRed flow (with the exception of the temperatures/switches/motion/battery filters). This version also has InfluxDB support (which I am still working on finalizing).
For those of you who know nodejs, you can drop in your own data endpoints now! Want to write your data to SumoLogic? Use the SumoLogic npm package (sumologic - npm), drop it into a writer and you're on your way! You can add it to package.json or install it on your server locally.
HE support has stated that websockets are unsupported and undocumented.
(But, they do have a plan to make them official).
As such, I will support this until they either remove the websockets OR make any changes to the streams.
Any suggestions are appreciated.
I've branched this off to HE Logging Utility
1 Like
For anyone who is interested, here is a Node-Red flow to post events directly from Hubitat's eventsocket to Influx.
You need to install "node-red-contrib-filter" and "node-red-contrib-influxdb" to make it work in Node-Red.
JSON code can be found here:
HE2Influx JSON
Have been using this for a while without any issues. Here is my default Grafana board:
6 Likes
This is why I absolutely love NodeRed. Well done!
Ditto!
Thanks for posting this, was looking for NodeRed and Influx setup!
1 Like
hi @dan.t thanks for sharing this. I am unable to import this JSON into node-red for some reason. I tried to see if the JSON is valid but getting it's saying there is an error at line 282 on jsonlint.com.
Anyone else able to import it?
I think there is a problem once I copy the JSON to the website and save it..... Let me see how I can fix that.
Greetings from the rabbit hole!
I've been dealing with a frequently non-responsive hub. It's most assuredly a result of something I've done, but still, I want metrics. Since we don't have any profiling tools (I kid, I kid!) and my wife gets cranky if the lights don't come on when I'm not home, I came up with this flow.
It does the following:
- Gets the list of installed apps and calculates the time it took to complete the request
- Shoves the elapsed time into the database for graphing/reporting
- Checks the response times.
- If response times start to become concerning, it sends a Pushover alert.
- If I'm awake, it's allowed to do it once an hour.
- If I'm sleeping, it does it once so I see the notification in the morning and then shuts up for the rest of the night.
- If they get really bad, it will reboot the hub up to once an hour.
- Notifications via Pushover follow the same restrictions as above.
- Hub response times immediately after a reboot can potentially be very high while the services are still starting. This check avoids false positives.
- If response times start to become concerning, it sends a Pushover alert.
5 Likes
Rabbit hole indeed! I really like the calc on the hub response times after a reboot. would you mind sharing the flow?
I also came across this the other day. It allows you to send Alexa notifications from pretty much anything that can do a HTTP request out. I'm using it for a status report and some alerts. Might be nice to use along with pushover alerts.
Hey Coby,
I uploaded the JSON to github here: HE 2 Influx
That should work. I also adjusted my original message above.
Let me know if you still have problems
2 Likes
@dan.t, I imported your flow but it's not writing anything to my influxDB. I'm new to nodeRed so it's probably something I'm doing wrong.
I am using the 8086 port and I put in the name of my DB in the required field. What am I missing?
