I am a bit new to this platform and had a question.
I have a couple installations of Node-Red sending a "Heartbeat" message to a "Server" version of Node-Red to just check in and let me know they are alive. I have installed SQlite and would like to store these heartbeats into a DB so I can populate a UI when called upon. What I am having trouble with is formatting the message to the SQlite node correctly. Any help would be appreciated.
My incoming message is a payload object with an ID, Timestamp, CPU load, and CPU temp.
yes sir! Several different searches. Its just me being new. cant seem to get it configured properly. I guess its getting the values out of the message first then formatting them to the SQlite Node that is sliding by me.
Without wishing to derail you chain of thought. Have you considered implementing MQTT? It is trivial to set up and is great for this kind of thing because, in reality, you don't need or want the complexity of a relational database for keeping simple "state".
Simply send the online/offline state to MQTT. If you like you can make that retained but if you do, don't forget to also have the last update date/time - also retained - so that you know when you last heard from the system.
Indeed, I've just updated my own system today to do that for our Wiser smart heating system after I finally realised that I couldn't reach the controller because someone had knocked it last night and it had slipped off the connectors. As I am querying the system every 60sec or so, via a REST web interface, if I get a 200 response, I know all is well and I update MQTT with a new date/time and a retained "OK" to the topic "HEATING/STATUS". If I get anything else, I pass the returned text to the status and again update the date/time.
Now I can easily consume that in a dashboard or any other system that understands MQTT.
Actually I am sending the heartbeat via MQTT. And yes it is a great solution. My project will at some point need this DB function and so I am learning how to populate it with my MQTT msg for larger requests in the future.
So, you need to transform the incoming JSON into a SQL statement that updates a particular record.
Because you are wanting to store state, you should make sure that your db table has the record first using a dummy INSERT. Then you are free to simply use an UPDATE each time to the same record.
If you can tell us the format of your record, we can probably help further.
Just to clarify, are you wanting to keep a history of the events, or just the latest state? If you want the history then you will need to INSERT a new record each time.
Are you already familiar with sqlite? The first thing you will have to do is to decide what columns you want in the database table, then create the table in the database. You can do this using a command line sqlite utility or one of the GUI ones if you want.
Yes doing Okay with SQlite itself so far but new to it as well. But I do have the basics down using the DB. I am wanting to keep the history so yes looking at the insert function at this point. Was able to create a table with something like this.... CREATE TABLE heartbeat(id INTEGER PRIMARY KEY AUTOINCREMENT, id TEXT, time NUMERIC, cpu_load NUMERIC, cpu_temp NUMERIC)
So when my heartbeat comes into my server it looks like this as shown above,
It is always worth looking in flows.nodered.org for stuff. In particular there you will find https://flows.nodered.org/flow/2c48fc395772e22ecc64 which shows examples of how to use sqlite. Rather than using a fully defined INSERT in the topic like you have it is better to use the '?' format in the topic for the values and pass the values as an array in the payload. Then node-red will take care of formatting the data for you and will prevent sql injection attacks.
Then to get the data in the payload into an array to match the fields you can use a function node or a change node (or other ways as well I expect).
Have a look at https://nodered.org/docs/user-guide/messages and https://nodered.org/docs/writing-functions.
You are very close. However, check how you've created some columns in your table as numeric but the incoming data is actually text. You need to either keep as text in the db or convert the incoming text values to numeric. Also, your date/time field is defined as numeric which is wrong as well. it either needs to be text (so you can simply dump in the ISO formatted date/time string that you currently have) or as a datetime field.
The db formats you use depend on what you want to do with the data later.
