Hubitat > NodeRed > MySQL > Grafana (LONG READ)

JasonJoel · July 28, 2019, 8:52pm

Really? Maybe I was making it too hard on myself...

I'll try that. Only takes a few seconds to spin up an influxdb docker.

JasonJoel · July 28, 2019, 9:55pm

Yeah, InfluxDB seems to show the timestamps correctly. Not sure how I had it wrong in MariaDB... Or the MariaDB docker... Or both.

stephack · July 28, 2019, 9:59pm

Influxdb is time series based DB so it probably is designed to work with Zulu time output of HE...same with Grafana....they just work. At least that's my layman understanding of it.

ritchierich · July 31, 2019, 9:52pm

Last night I was updating my hubs (I have 3) and my coordinator hub was fighting me with the update and I had to try many times and reboot it. Long story short this is the hub that my event socket is connected to in NodeRed and I noticed this morning that my logs and database weren't up to date and had stopped around the time I was updating the hub firmware.

Is there anything I can do in my flow to ensure this doesn't happen and that it will try to reconnect after X minutes?

btk · August 8, 2019, 12:13am

I added a watchdog to my websocket relay flow to handle this.

Every 5 minutes, it checks to see how long ago it received a log message or an event. If it's been more than 300 seconds, it sets a flow variable to true and sends a pushover notification. If it's still been more than 300 seconds the next time it polls, it sends a notification and restarts the node-red service.

Code is here:

github.com

bkeifer/hubitat/blob/master/flows/HubitatWebsocketRelay.json

[  
   {  
      "id":"cadd3106.3ec1d",
      "type":"tab",
      "label":"Websocket Relay",
      "disabled":false,
      "info":""
   },
   {  
      "id":"f7d93ba1.933908",
      "type":"change",
      "z":"cadd3106.3ec1d",
      "name":"Remove _session",
      "rules":[  
         {  
            "t":"delete",
            "p":"_session",
            "pt":"msg"
         }
      ],

This file has been truncated. show original

JohnRob · August 21, 2019, 5:00am

@codersaur
Great writeup!! Thank you. (ref original post)
I realize I'm late to the party however I just recently decided to log some sensor data. I don't want to use a data base but simply stream the logs and/or event to a disk file.

I've used you JSON file and copied the "CreateLogsINSERT" function hoping it would Parse the HE message. I'm sending the results to the debug panel but the information is not being recognized by the CreateLogsINSERT function.

Am I on the right track? Is there anything obvious I'm missing?

BTW I was surprised how easy it was to get this far. The info in your write up was the reason I went down this road.

Thanks
John

Flow:

Error msg from debug panel:

Function "code":

stephack · August 21, 2019, 10:50am

I believe you meant to tag @corerootedxb in your powt but unfortunately he hasn't been active recently.

Since your are planning to output to a text file, you don't need queries or inserts. You can simply pump your data to a file output node. You will probably want to do some formatting to it looks presentable and that can be done with a change node.

I output my logs socket to a text file and format it to include delimiters....so I can work with it in Excel. I'll pm you my setup when I can get to my PC. It will probably be doing more than you need (event formatting should be simpler) but should at least give you a template you can use as a guide.

dan.t · August 21, 2019, 11:22am

Here is a very simple flow that converts all of the HE websocket events to a csv file

https://raw.githubusercontent.com/danTapps/node-red/master/HE2csv.json

You need to adjust the websocket URL and in the file node the path where you want to store it.

That should be the easiest way to achieve you goal

stephack · August 21, 2019, 11:37am

Lol, was was just about to post a quick and dirty flow. I'm sure yours is better (since I learned most this from editing your flows in any case).

dan.t · August 21, 2019, 11:42am

Lol, I wouldn’t call my flow pretty, i always like the “fit for purpose” approach

JohnRob · August 21, 2019, 3:05pm

Wow I have to thank you both ( a lot)

I have Dan's code running now and have imported stephack's code and am learning from it

You both deserve kudos for you willingness to help others, both in this thread and others.

I am still quite delighted this forum has held its professional culture and not turning ugly as some others have. Even the lounge conversations are all written with a helpful (or humorous) way.

For others following this thread; I started installing Node-RED yesterday afternoon (about 18 hours ago) and this morning I have code running that essentially does what I need. The only thing I need to add is Time/Date for the events, which I think should be pretty straight forward. The folks in the forum and extremely helpful and if you have even an inclination you should go for it.

John

ritchierich · September 28, 2019, 12:02pm

Does anyone know if NodeRed can restart itself? When I restart my hub sometimes the socket connections don’t reconnect. I have my flow notifying me of this situation but it would be awesome if I could invoke the same action as Deploy - Restart Flows via a node. Would be nice to listen for the systemStart event and perform this automated restart of the flows after a delay.

I am running NodeRed in a docker container on my NAS. I did some online searching and tried a few things that didn’t work. Thanks in advance.

stephack · September 28, 2019, 1:05pm

I started looking into this a while back and it fell on the back burner. I remember getting the flow that had my websocket redeployed using the NR http api below:
https://nodered.org/docs/api/admin/methods/post/flows/

For some reason I never finished looking at this...can't remember why. After I finish peering into my new rabbit hole (home assistant / mqtt), I'll look into this again.

ritchierich · September 28, 2019, 2:28pm

Thanks I am using some of @btk’s watchdog

But his is executing a command on the “server” to restart the flow. Unfortunately I couldn’t get that to work with my docker setup but your post flow links look promising. I will post back here when I find some time to try it.

corerootedxb · September 28, 2019, 2:33pm

I know it's possible to reset a container from within itself, but it's a PITA. I rarely run docker, so my knowledge is a bit spotty.

Life and death of a container - DevOpsion - Medium explains it pretty well on how to possibly manage a container from within that container itself.

jon1 · September 28, 2019, 9:04pm

I read somewhere recently on here about WebSocket ping/pong being the solution to stuck half open sockets. I dont know if the node-red ws connectors support it, or if the HE does on its end also.

ritchierich · September 30, 2019, 3:00pm

@stephack and @corerootedxb thank you for the ideas and inspiration. The article that Stephen provided with the Post was helpful and after doing a little messing around, I got my flows to restart via PostMan and a simple http Post passing the following header:
Node-RED-Deployment-Type:reload

I was hoping to use a simple RM rule that was triggered from the Location systemStart event but I couldn't figure out how to include a header in the rule action so I gave up. My NodeRed container kept logging various errors. I ended up using a slightly modified @ogiewon's HTTP Momentary Switch driver where I have included an extra header preference:

Then I setup a rule triggered by a hub restart to turn on this momentary switch:

stephack · September 30, 2019, 3:11pm

That's exactly where I stopped...proof of concept. Glad to see you took it to completion. I randomly lose connection (even if HE hub did not restart) so I need to think out how to make my Watchdog node monitor the socket for disconnects and then execute the momentary switch to reload the flow.

It hasn't happened in a while so my motivation is low to work on this but thanks for sharing @ritchierich. I'll be reaching out for that modified driver when I'm ready to tackle this ..back to my Hassio/Mqtt black hole.

jon1 · September 30, 2019, 4:50pm

I went ahead and pinged the node-red devs about implementing ping/pong heartbeat on the websocket node here, we'll see what happens.

tjsawyer · October 15, 2019, 2:40pm

Hoping for some help here. Probably missing something simple. First time node-red here and I've tried a bunch of stuff, so I have two different questions to ask ...

Simple enough flow ...

HE Event is configured as such ...

The filter ...

The Set Influx Data (thanks to -- someone on here, sorry, can't find the source!)

And the Influx DB ...

--- First issue ---

A 'TypeError' debug message that I can correlate to line 166 of the Set Influx DB that parses msg.value.match. I was able to overcome this by changing eventsocket receive from 'payload' to 'entire message'.

Is this the correct config, or is there some reason my payload setting is not working as expected? I've seen both varieties posted here, but at least one stated that changing to 'entire message' is what fixed it for them and that seems to work for me as well, but wanted to make certain.

--- End First Issue ---

So, now that I tweaked to 'entire message' on the payload, I adjusted the filter accordingly ...

---Start Second Issue ---

And now it seems to pass data all the way to the InfluxDB connector, but then fails because there is 'No measurement specified', however if I look in the payload of the debug message, I can see the measurement type is being set.

Going back to my InfluxDB config, I have left the 'Measurement' setting blank and I think that, as per the tip at the bottom of that screen, it should read the measurement value from the payload.

--- End Second Issue ---

This is the part I am stuck at. Should I be putting a value in that 'Measurement' config box, or am I not passing values properly to start with?

Any help would be greatly appreciated!