Node-RED SQL Logging Datastreams
- Working with AlaSQL in Node-RED
- Connect our Camera to Node-RED
- Writing the Data to our SQL Database
- Backup the Database Table to a File
- Backup your camera's System Log
- Flow Export
In this Tutorial we want to use Node-RED to retrieve an Information from a INSTAR Full HD camera, display it's value on the Node-RED dashboard and feed it into a SQL database.
In the example we will read the current brightness level detected by the internal light sensor. This value can be used to detect when your camera switches the Infrared Cutfilter (IRCut), which is a useful information if you want to change the camera configuration based on the night and day mode (e.g. change the sensitivity of the motion detection or move to a different P&T position).
The exact value, when the switch happens depends on your camera model and has to be determined first:
The same mechanism can be used to create backups from your camera configuration or system logs. As an example we will use Node-RED to backup the camera's system logbook.
Scroll to the bottom of this page to find the complete flows and import it to your Node-RED installation to follow along. But make sure to first install
node-red-contrib-alasqlas described below.
Working with AlaSQL in Node-RED
AlaSQL is an open source SQL database for Javascript with a strong focus on query speed and data source flexibility for both relational data and schemaless data. It can be added to Node-RED by installing node-red-contrib-alasql:
The package provides us with a node that enables us to use SQL queries like CREATE, SELECT, INSERT and DROP to create tables, add data to them to re-feed them to our Node-RED project at a later stage. There is also a handy Out-File and In-File node that enables us to backup our tables to a file and recreate our database from this backup.
To prepare a table for our soon to be created data, we can use an AlaSQL node with the following command:
CREATE TABLE lightSensor (camera string, brightness number)
Click on the Inject node next to the CREATE node to create the table if it does not exist yet (the inject node is set to trigger 5s after Node-RED started - so you should already be good to go).
Connect our Camera to Node-RED
As an example we can connect to the HTTP API of INSTAR IP Full HD camera using a CGI command like (don't forget to adjust the IP address and your camera login!):
http://user:password@192.168.2.115/param.cgi?cmd=getsaradcstate
The command will retrieve to current brightness level recorded by the internal light sensor of the camera. We need to add some RegEX var brightness = msg.payload.replace(/^\D+|\D+$/g, ""); to clean up the response:
Then Regular-Expression will take the camera response var saradc_state="208";, extract the value (in this case 208) and we then set this value to the msg.payload. Which then can be used directly in a node-red-dashboard node:
Writing the Data to our SQL Database
For the AlaSQL node we can use an insert command to add the measurement to our database table (CREATE TABLE lightSensor (camera string, brightness number)):
INSERT INTO lightSensor VALUES ('192.168.2.115',?)
The Inject node is configured to trigger once every minute. You can check if there are already values stored inside the SQL table by clicking on the Inject node next to the SELECT node we created above - SELECT * FROM lightSensor. Open the debug panel in Node-RED to see the output - it should list every row inside the lightSensor table.
Backup the Database Table to a File
Once we collected all the data we needed, we can use an AlaSQL node to select all data from one camera SELECT * FROM lightSensor WHERE camera IN ('192.168.2.115') and use an AlaSQL Out-File node to write this information to a file (xlsx, csv, json, etc.). Make sure that the folder /home/pi/.node-red/sql/ exists and NodeRED has the necessary rights to write to it.
Backup your camera's System Log
To get your camera's System Log you can use the following path (please change the login and the IP address according to your camera setup):
http://admin:instar@192.168.2.116/tmpfs/syslog.txt
Once we send this command our camera will answer with a simple text file, listing all recorded camera events. To be able to write this information to a database, we first have to clean it up using regular expressions:
Now we are able to use an AlaSQL node to insert the information into our database table - e.g. sysLog116:
INSERT INTO sysLog116 VALUES (CURRENT_TIMESTAMP,?);
The following node inside the sequence will delete the log file on our camera to prevent duplicated event entries in our database:
http://admin:instar@192.168.2.116/param.cgi?cmd=cleanlog&-name=sys
Just as before we first need to create this table by triggering the CREATE node if the table does not exist already:
CREATE TABLE sysLog116 (ts TIMESTAMP VARCHAR(80), event string);
Flow Export
How to import a Flow
Open the top menu then select import -> clipboard, this will open a dialogue box where you can import the flow. Copy the JSON flow above to your system clipboard. When the file content has been pasted into the dialogue box press the import button.
Light Sensor Readout
[{"id":"54e58600.85c09c","type":"alasql","z":"4e79509.5da71b","name":"SELECT","query":"SELECT * FROM lightSensor","x":260,"y":220,"wires":[["f74b0716.9b1e78"]]},{"id":"f74b0716.9b1e78","type":"debug","z":"4e79509.5da71b","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":450,"y":129,"wires":[]},{"id":"c7d0d4c9.343eb8","type":"inject","z":"4e79509.5da71b","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":123,"y":220,"wires":[["54e58600.85c09c"]]},{"id":"64f88aa9.618984","type":"alasql","z":"4e79509.5da71b","name":"CREATE","query":"CREATE TABLE lightSensor (camera string, brightness number)","x":280,"y":100,"wires":[["f74b0716.9b1e78"]]},{"id":"3f95a33a.3bc3fc","type":"inject","z":"4e79509.5da71b","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"onceDelay":"5","x":130,"y":100,"wires":[["64f88aa9.618984"]]},{"id":"88a397a8.148378","type":"alasql","z":"4e79509.5da71b","name":"INSERT","query":"INSERT INTO lightSensor VALUES ('192.168.2.115',61),('192.168.2.116',66),('192.168.2.117',67),('192.168.2.118',2),('192.168.2.15',59),('80.152.227.12',2)","x":260,"y":160,"wires":[["f74b0716.9b1e78"]]},{"id":"c0deb77.6afab48","type":"inject","z":"4e79509.5da71b","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":123,"y":160,"wires":[["88a397a8.148378"]]},{"id":"20947549.54031a","type":"alasql","z":"4e79509.5da71b","name":"DROP","query":"DROP TABLE lightSensor","x":260,"y":40,"wires":[["f74b0716.9b1e78"]]},{"id":"d73e17f7.7800f8","type":"inject","z":"4e79509.5da71b","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":123,"y":40,"wires":[["20947549.54031a"]]},{"id":"2f0fb36e.ef2d1c","type":"http request","z":"4e79509.5da71b","name":"192.168.2.115","method":"GET","ret":"txt","paytoqs":false,"url":"http://admin:instar@192.168.2.115/param.cgi?cmd=getsaradcstate","tls":"","proxy":"","authType":"basic","x":320,"y":340,"wires":[["8e66245b.715848"]]},{"id":"8e66245b.715848","type":"function","z":"4e79509.5da71b","name":"brightness","func":"msg.topic = \"brightness\";\n\nvar brightness = msg.payload.replace(/^\\D+|\\D+$/g, \"\");\n\nmsg.payload = brightness;\n\nreturn msg;","outputs":1,"noerr":0,"x":488,"y":340,"wires":[["eed46a9f.8c8608","79b12832.8cfbf8","72dc17cb.ff6178"]]},{"id":"eed46a9f.8c8608","type":"ui_chart","z":"4e79509.5da71b","name":"Brightness","group":"58c9cffc.1484f","order":1,"width":"0","height":"0","label":"Brightness (IN-9008)","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":653,"y":339,"wires":[[]]},{"id":"79b12832.8cfbf8","type":"ui_text","z":"4e79509.5da71b","group":"58c9cffc.1484f","order":3,"width":0,"height":0,"name":"","label":"Current Brightness","format":"{{msg.payload}}","layout":"row-spread","x":672,"y":379,"wires":[]},{"id":"72dc17cb.ff6178","type":"alasql","z":"4e79509.5da71b","name":"lightSensor","query":"INSERT INTO lightSensor VALUES ('192.168.2.115',?)","x":630,"y":300,"wires":[[]]},{"id":"1a556423.56677c","type":"inject","z":"4e79509.5da71b","name":"1min Trigger","topic":"","payload":"","payloadType":"date","repeat":"60","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":340,"wires":[["2f0fb36e.ef2d1c"]]},{"id":"5bfab4c7.fead1c","type":"inject","z":"4e79509.5da71b","name":"20 Min Trigger","topic":"","payload":"","payloadType":"date","repeat":"1200","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":480,"wires":[["6c59f580.919d0c"]]},{"id":"6c59f580.919d0c","type":"alasql","z":"4e79509.5da71b","name":"SELECT 115","query":"SELECT * FROM lightSensor WHERE camera IN ('192.168.2.115')","x":310,"y":480,"wires":[["2d6e1036.6d267"]]},{"id":"2d6e1036.6d267","type":"alafile out","z":"4e79509.5da71b","name":"xlsx","filename":"/home/pi/.node-red/sql/lightSensor_115","format":"xlsx","columns":"*","headers":true,"x":450,"y":480,"wires":[]},{"id":"58c9cffc.1484f","type":"ui_group","z":"","name":"192.168.2.115","tab":"32dce8be.80f688","disp":true,"width":"6","collapse":false},{"id":"32dce8be.80f688","type":"ui_tab","z":"","name":"Brightness","icon":"fa-sun-o","order":11,"disabled":false,"hidden":false}]
System Log Backup
[{"id":"98abb348.a5e48","type":"inject","z":"e3ee2ba4.3b3d68","name":"24hrs Trigger","topic":"","payload":"","payloadType":"date","repeat":"86400","crontab":"","once":false,"onceDelay":0.1,"x":120,"y":100,"wires":[["3e24cc90.b66634"]]},{"id":"3e24cc90.b66634","type":"http request","z":"e3ee2ba4.3b3d68","name":"Get Log 116","method":"GET","ret":"txt","paytoqs":false,"url":"http://admin:instar@192.168.2.116/tmpfs/syslog.txt","tls":"","proxy":"","authType":"basic","x":220,"y":53,"wires":[["eb0870ff.5a5e1"]]},{"id":"ccf12822.059a18","type":"inject","z":"e3ee2ba4.3b3d68","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":100,"y":380,"wires":[["bd6d09c3.782788"]]},{"id":"40908c9f.0b1474","type":"inject","z":"e3ee2ba4.3b3d68","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"onceDelay":"5","x":110,"y":205,"wires":[["efc4323e.4c5bc"]]},{"id":"73c283ff.d3003c","type":"inject","z":"e3ee2ba4.3b3d68","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":100,"y":265,"wires":[["bded183e.8941a8"]]},{"id":"aca8074.2662bf8","type":"inject","z":"e3ee2ba4.3b3d68","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":100,"y":325,"wires":[["43e99a76.7f6154"]]},{"id":"43e99a76.7f6154","type":"alasql","z":"e3ee2ba4.3b3d68","name":"SELECT","query":"SELECT * FROM sysLog116","x":237,"y":325,"wires":[["8d1cd7bf.de6d98"]]},{"id":"b333dff4.6e8f","type":"alasql","z":"e3ee2ba4.3b3d68","name":"INSERT","query":"INSERT INTO sysLog116 VALUES (CURRENT_TIMESTAMP,?);","x":360,"y":265,"wires":[[]]},{"id":"efc4323e.4c5bc","type":"alasql","z":"e3ee2ba4.3b3d68","name":"CREATE","query":"CREATE TABLE sysLog116 (ts TIMESTAMP VARCHAR(80), event string);","x":263,"y":205,"wires":[[]]},{"id":"bd6d09c3.782788","type":"alasql","z":"e3ee2ba4.3b3d68","name":"DROP","query":"DROP TABLE sysLog116","x":237,"y":380,"wires":[[]]},{"id":"8d1cd7bf.de6d98","type":"debug","z":"e3ee2ba4.3b3d68","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":390,"y":325,"wires":[]},{"id":"3f7d0a1e.da1be6","type":"inject","z":"e3ee2ba4.3b3d68","name":"20 Min Trigger","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":110,"y":500,"wires":[["317d8b07.06a1a4"]]},{"id":"317d8b07.06a1a4","type":"alasql","z":"e3ee2ba4.3b3d68","name":"SELECT 116","query":"SELECT * FROM sysLog116;","x":278,"y":500,"wires":[["c31a719a.72d4a"]]},{"id":"c31a719a.72d4a","type":"alafile out","z":"e3ee2ba4.3b3d68","name":"json","filename":"/home/pi/.node-red/sql/syslog_116","format":"json","columns":"*","headers":true,"x":418,"y":500,"wires":[]},{"id":"eb0870ff.5a5e1","type":"function","z":"e3ee2ba4.3b3d68","name":"syslog","func":"msg.topic = \"syslog\";\n\nstr = msg.payload.replace(/(\\r\\n|\\n|\\r)/gm,\" \").replace(/\\[/g, '').replace(/\\]/g, \"\").replace(/\\./g, \";\");\n\nvar syslog = str.split(/;\\s/);\n\nmsg.payload = syslog;\n\nreturn msg;","outputs":1,"noerr":0,"x":318,"y":100,"wires":[["7e28ffaa.12763"]]},{"id":"7e28ffaa.12763","type":"alasql","z":"e3ee2ba4.3b3d68","name":"sysLog116","query":"INSERT INTO sysLog116 VALUES (CURRENT_TIMESTAMP,?);","x":417,"y":54,"wires":[["ac0d4d61.2265a"]]},{"id":"bded183e.8941a8","type":"function","z":"e3ee2ba4.3b3d68","name":"","func":"msg.payload = [\"2019_06_17 09:08:48 ipc_server start\", \"2019_06_17 09:09:00 ircut: display switch(color -> blackwhite)\", \"2019_06_17 09:09:02 ircut: display switch(blackwhite -> color)\", \"2019_06_17 09:09:02 user=admin login for live stream\", \"2019_06_17 09:09:10 user=%61%64%6D%69%6E login for mjpeg stream\", \"2019_06_17 09:15:50 motion detection(area=1) alarm\", \"2019_06_17 09:26:52 motion detection(area=1) alarm\", \"2019_06_17 09:31:25 motion detection(area=1) alarm\", \"2019_06_17 09:31:51 motion detection(area=1) alarm\", \"2019_06_17 09:32:12 motion detection(area=1) alarm\", \"2019_06_17 09:33:29 motion detection(area=1) alarm\", \"2019_06_17 09:43:52 motion detection(area=1) alarm\", \"2019_06_17 09:45:43 user=%61%64%6D%69%6E logout from mjpeg stream\", \"2019_06_17 10:29:59 motion detection(area=1) alarm\", \"2019_06_17 10:30:01 motion detection(area=1) alarm\", \"2019_06_17 10:30:11 motion detection(area=1) alarm\", \"2019_06_17 10:30:13 motion detection(area=2) alarm\", \"2019_06_17 10:30:20 motion detection(are\"]\nreturn msg;","outputs":1,"noerr":0,"x":230,"y":265,"wires":[["b333dff4.6e8f"]]},{"id":"ac0d4d61.2265a","type":"http request","z":"e3ee2ba4.3b3d68","name":"Delete Log","method":"GET","ret":"txt","paytoqs":false,"url":"http://admin:instar@192.168.2.116/param.cgi?cmd=cleanlog&-name=sys","tls":"","proxy":"","authType":"basic","x":507,"y":100,"wires":[[]]}]