Node-RED

Alle Node-RED Tutorials

All Node-RED FAQs

Node-RED and SQL Logging

Mike Polinowski
INSTAR MQTT
Firmware Update
Full HD Series
INSTAR Deutschland GmbH
INSTAR MQTT

Make your camera the heart of your smart home

INSTAR MQTT

Using the MQTT interface of your INSTAR Full HD cameras you can connect them to an existing smarthome system (Home Assistant, OpenHAB, Node-RED, Athom Homey, Homematic, ioBroker, Loxone, homee) can be added. Or even make it the main broker for your MQTT sensors. MQTT allows you to automate ALL functions of your camera and link them to other services in your smarthome.

Mike Polinowski
IFTTT Applets
Firmware Update
Full HD Series
INSTAR Cloud
INSTAR Deutschland GmbH
INSTAR IFTTT Applets

Control your camera via the IFTTT online service

INSTAR IFTTT

On the IFTTT platform, we provided a service called INSTAR. The INSTAR applets provide you with the ability to control some settings of your INSTAR camera or INSTAR Cloud with IFTTT. You can connect INSTAR with triggers for location (Geo Location) and date & time, send notifications and much more. For example, you can connect INSTAR applets to the IFTTT location service and automatically disable the alarm when you arrive home. You can also use the INSTAR applets to create your own automation and connect them to other third-party applets.

In this Tutorial we want to use Node-RED to retrieve an Information from our 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:

alasql in node-red

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-alasql as 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:

alasql in node-red

alasql in node-red

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)

alasql in node-red

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

alasql in node-red

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:

alasql in node-red

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:

alasql in node-red

alasql in node-red

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',?)

alasql in node-red

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.

alasql in node-red

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

alasql in node-red

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:

alasql in node-red

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,?);

alasql in node-red

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);

alasql in node-red

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

alasql in node-red 

[{"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

alasql in node-red 

[{"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":[[]]}]