The next step in troubleshooting this would be to attach a debug node to the event or log socket output, open the debug tab and see if there is activity on the socket. Don't forget to click Deploy after changes.
That’s the issue. I am not seeing anything. 
Also 
...if you are proficient in Grafana and know that isn't your issue then what @jon1 suggested is a good starting point. I always set my debug node to show the complete message and not just the payload so I can see everything coming out of that node. If it's outputting what I expect I move the debug one or more node down the flow to ensure everything is what it should be.
2 Likes
So apparently the SQL script must have error and did not create the table. I now have data flowing in. Just trying to get a history setup on logs. Working on a SQL query now.
1 Like
Alright I am seeing logs and events but both stopped any additional log entries after 1:00am. I noticed I have used 's in my device names. So I went ahead and removed those out of my devices accordingly. BUT, NodeRed is no longer gathering data from either my log or event websockets. Seems the queue is stuck. How do you delete/flush the current nodered queue?
9 May 09:39:43 - [info] Starting flows
9 May 09:39:44 - [info] Started flows
9 May 09:39:44 - [info] Server now running at http://127.0.0.1:1880/
9 May 09:40:00 - [error] [mysql:Logging DB] Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's Room','inactive')' at line 1
9 May 09:40:00 - [error] [mysql:Logging DB] Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's Room','inactive','null',2195,null,null,null,null)' at line 1
9 May 09:40:00 - [error] [mysql:Logging DB] Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's Room','74.0','°F',2195,null,null,null,'temperature is 74.0°F')' at line 1
9 May 09:40:00 - [error] [mysql:Logging DB] Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's Room','73.4','°F',2196,null,null,null,'temperature is 73.4°F')' at line 1
9 May 09:40:00 - [error] [mysql:Logging DB] Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's Room','online','null',2196,null,null,null,null)' at line 1
9 May 09:40:00 - [error] [mysql:Logging DB] Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's Room','inactive')' at line 1
9 May 09:40:00 - [error] [mysql:Logging DB] Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's Room','inactive','null',2196,null,null,null,null)' at line 1
9 May 09:40:00 - [error] [mysql:Logging DB] Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's Room','online','null',2195,null,null,null,null)' at line 1
When my NodeRed gets hung up like that I just close and restart....but I'm running everything on a Win10 box. Everything I know about linux is through google.
Yeah I tried that and I still have these error popping up in the node-red-log. I can't tell where these error is happening in my nodered flow. Suggestions on removing apostrophes in nodered?
I'm not at my PC but there should be a debug tab on your right panel (icon looks like an actual bug and is next to the "i" tab). Most errors and debug messages populate there. If you hover over the error it should highlight the node that generated the error. You can then add a debug node just before or right after to see what's going on.
Yes that is how I acquired the above error codes. I added a replace all 's to just s and restarted nodered. Looks like the errors are not reproducing but not sure when data will flow into the database again. I am refreshing grafana but not seeing any new logs.
EDIT: nvm my string fix didn't do anything. ugh!
I don't know the required syntax for you database but the string node (node-red-contrib-string)can do a lot to properly format text for various types of outputs.
I'm sure @corerootedxb can provide a suggestion or 2 as well.
Thanks for sharing this setup. the visual information is fantastic. i have this all setup and working as services on my windows home server.
I need some help with the time offset from MariaDB MySQL to Grafana. The time is getting saved correctly in MySQL but Grafana pulls epoch time. which gives me a 4 hour variation. I have searched high and low and cant seem to figure out how to correct this.
Yeah, that's a known Grafana issue that's been around for years now. The best I can suggest is to make sure the time zone settings in Grafana is correct and if that doesn't solve it, you can adjust the time periods in your graphs within Grafana.
I'm having some confusion and/or issues with Node-Red. In your sample you reference everything like: msg.name or msg.whateverKey. however, if I do this i just get undefined. However, if I convert the payload into a json object using the json stage I can then get at the nodes like msg.payload['name'] or msg.payload['whateverKey']. I realize this is not a node-red thread, but i googled around a bit and don't see a lot of things to point me in a better direction.
Thanks,
Scott
It's a bit unclear what you are asking honestly. I never used node red before attempting to set this flow up myself. The link below is a great resource that explains how flows and nodes all tie together. It really helped me understand the basics of NR and I now have more than 10 flows for various things I want to keep out of Hubitat. Trust me it 's worth the small time investment.
I will go read that thread. What I was basically asking is that the samples provided didnt' work when trying to read the payload.
So I was suggesting that msg.name (a key/value) pair in the payload JSON that I saw being referenced I was unable to read. When I tried to write a function just using the key/value like msg.name I was getting undefined back. However, If I converted the payload (which is a string) into a real JSON object I was then able to get to the property by using msg.payload('name'). I was just trying to figure out if I was missing something in how the OP was just using msg.name to get the value from the payload string.
the reason your request is confusing is because the answer to that question has a hundred "depends". The values fro msg.name, etc all exist/or not exist and change at various points in the flow. There are a few nodes that you need to customize for your own setup. I use a heavily customized version of this now so I won't be able to assist as much as I would like to.
First step is to open and configure the nodes described in the first post. If any errors show in your system or debug console...report those here. Otherwise, if you think you know where the break down is occuring you can attache a debug node on the out end of that part of the flow and it will show you the values (msg.payload, name , etc) that are being passed at that stage.
Can you please provide example code? “msg” is an object that you can set and retrieve values from. I have a few flows where I am setting say msg.michael = “firstName” and retrieving that later in another node.
my code that works:
var query = "INSERT INTO events (source, name, displayName, value, unit, deviceId, hubId, locationId, installedAppId, descriptionText) VALUES(";
query += "'" + msg.payload['source'] + "',";
query += "'" + msg.payload['name'].replace(''', '') + "',";
query += msg.payload['displayName'] === null ? 'null,' : "'" + msg.payload['displayName'].replace(''','') + "'" + ",";
query += "'" + msg.payload['value'].replace(''', '') + "',";
query += "'" + msg.payload['unit'] + "',";
query += msg.payload['deviceId'] === null ? 'null,' : msg.payload['deviceId'] + ",";
query += msg.payload['hubId'] === null ? 'null,' : msg.payload['hubId'] + ",";
query += msg.payload['locationId'] === null ? 'null,' : msg.payload['locationId'] + ",";
query += msg.payload['installedAppId'] === null ? 'null,' : msg.payload['installedAppId'] + ",";
query += msg.payload['descriptionText'] === null ? 'null)' : "'" + msg.payload['descriptionText'] + "')";
msg.topic = query;
return msg;
So.. when I originally tried to get msg.name or msg.source all I got back from the node was 'undefined'.
Interestingly, the data that came as msg.payload from the web socket event or log listener is just a "string" according to node-red.
If in the same function node these would be msg.payload.name and msg.payload.source. You have to remember that msg.payload is a common variable that can can change from node to node and if you need a value from it you need to assign it:
msg.source = msg.payload.source before the return msg at the end.
