Node-Red

Home Assistant History on Node-Red Charts

April 7, 2021Posted in IoT, Node-RedTagged HA, History, Home Assistant, Node-Red, NodeRed, Trend1 Comment

Home Assistant is an open source home automation platform that can monitor and control smart home devices and it integrates with many of other common systems.

Home Assistant installation is targeted for Raspberry Pi’s but other hardware options are available.

I was very impressed how easy it was to install Home Assistant and get a basic home integration system up and running.

There is a huge number of integration solutions (1500+) that connect to most of the mainstream products. However if you want to do some custom programming with connections to Arduino or other Raspberry Pi or PCs there isn’t an easy “out of the box” solution. To solve this requirement Home Assistant has included Node-Red as an add-on.

Node-RED is a visual programming tool for wiring together hardware devices, APIs and online services.

For information how to install Node-Red on Home Assistant see the HA documentation. (I wrote a blog for my installation).

Home Assistant History

The default installation of Home Assistant has history enabled, and the data is stored in local SQLite database (home-assistant_v2.db) within your configuration directory unless the recorder integration is set up differently.

Charts of sensor history can be show in the Home Assistant Overview pages or as dialogs.

Using Node-Red with HA History

The Node-Red installation has a number of Home Assistant nodes that allow sensors data to be read and created. For viewing history the HA get history node is used.

A simple manual test circuit to get history would have: an injector, a get_history and a debug node.

By double-clicking on the get history node its configuration can be defined. For this example a sensor entity id of sensor.cpu_temp is used with a 10 minute (10m) time scale. When the injector is toggled the debug window will show an array to data and time entries.

3 Button History Chart

The logic to create a 3 button history chart would use: 3 dashboard buttons, 3 get history nodes, a Javascript function, and a dashboard chart node.

The Node Red chart node typically does local historical storage within the node itself. However the chart node can also be used to read external data and show a line chart of the data,nothing is stored locally (more info on this).

A javascript function node can be used to format the data into the required form:

//
// Format the HA History results to match the charts JSON format
//

var series = ["HA Values"];
var labels = ["Data Values"];
var data = "[[";
var thetime;
 
for (var i=0; i < msg.payload.length; i++) {
    thetime = (msg.payload[i].last_changed); // Note: check your format?
    data += '{ "x": "' + thetime + '", "y":' + msg.payload[i].state + '}';
    if (i < (msg.payload.length - 1)) {
        data += ","
    } else {
        data += "]]"
    }
}
var jsondata = JSON.parse(data);
msg.payload = [{"series": series, "data": jsondata, "labels": labels}];
return msg;

The payload data will be in the format of:

If everything is formatted correctly the Node Red dashboard page should look like:

For a more flexible presentation it would be good to use adjustable time periods.

Final Thoughts

For simple historical storage I found that the built-in History worked fine, however if you’re looking for custom long term storage then the InfluxDB add-on to HA might be a better solution.

MySQL with Arduino and Node-Red

April 25, 2020April 29, 2020Posted in Arduino, Node-RedTagged Arduino, Mysql, NodeRed, sql1 Comment

In this blog I would like to document a project that I did with my kids. The goal of the project was to learn SQL in a couple of different environments.

We did most of our initial testing/learning directly on the MySQL server. Once we got everything worked out we moved to Arduino, then Node-Red.

For our MySQL testing we used alwaysdata.com which has a free low use account. They run MariaDB which looks exactly like MySQL.

MySQL Setup

The plan was to have the Arduino module write 3 values:

randint – a random integer from 1-100
saw – a saw tooth integer from 0-100
sstatus – a text value of : HIGH, OKAY or LOW

We didn’t want to generate a time stamp from Arduino, instead we wanted MySQL to generate it. Our SQL structure was:

The field “thetime” was defined as a timestamp with CURRENT_TIMESTAMP as the default. By doing this we only needed to INSERT the Arduino data and MySQL would add the current timestamp.

After the Arduino Tables was created and tested we were able to start inserting data from Arduino.

Arduino and MySQL

There is an excellent MySQL library for Arduino, that can be added via the Arduino IDE “Manage Libraries” option.

Our Arduino code wrote the 3 values to MySQL every 10 seconds.

/*
  MySQL Connector/Arduino Example : connect by wifi
*/
#include <ESP8266WiFi.h>           // Use this for WiFi instead of Ethernet.h
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

IPAddress server_addr(111,222,111,111);  // IP of the MySQL *server* here
char user[] = "yourusername";              // MySQL user login username
char password[] = "yourpassword";        // MySQL user login password

// WiFi card example
char ssid[] = "xxxxx";         // your SSID
char pass[] = "xxxxx";     // your SSID Password

WiFiClient client;                 // Use this for WiFi instead of EthernetClient
MySQL_Connection conn(&client);
MySQL_Cursor* cursor;

int saw = 0;

void setup()
{
  Serial.begin(9600);

  // Begin WiFi section
  Serial.printf("\nConnecting to %s", ssid);
  WiFi.begin(ssid, pass);
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }

  // print out info about the connection:
  Serial.println("\nConnected to network");
  Serial.print("My IP address is: ");
  Serial.println(WiFi.localIP());

  Serial.print("Connecting to SQL...  ");
  if (conn.connect(server_addr, 3306, user, password))
    Serial.println("OK.");
  else
    Serial.println("FAILED.");
  
  // create MySQL cursor object
  cursor = new MySQL_Cursor(&conn);
}

void loop()
{
  // Sample insert query
  //
  char INSERT_SQL[] = "INSERT INTO datal (randint, saw, sstatus) VALUES ('%d','%d','%s')";
  char query[128];
  int randint = random(100);


saw++;
if (saw == 100){
  saw = 0;
}

  if (randint <= 10){
     sprintf(query, INSERT_SQL, randint, saw, "LOW");
  } else if (randint >= 90){
     sprintf(query, INSERT_SQL, randint, saw, "HI");
  } else {
     sprintf(query, INSERT_SQL, randint, saw, "OKAY");
  }
  
  Serial.println(query);

  if (conn.connected())
    cursor->execute(query);


  // Sample select query - Get the last 2 values and show them 
  // Table: arduino1 - this is written to from Node-Red 

  int arddata;
  // Initiate the query class instance
  row_values *row = NULL;
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);

  char squery[] = "SELECT thetime,arddata FROM arduino1 ORDER BY thetime DESC limit 2;";
  cur_mem->execute(squery);
  // Fetch the columns (required) but we don't use them.
  column_names *columns = cur_mem->get_columns();

  do {
    row = cur_mem->get_next_row();
    if (row != NULL) {
      //thetime = (row->values[0]);
      arddata = atoi(row->values[1]);
      Serial.print("Time: "); Serial.print(row->values[0]); Serial.print(" data: "); Serial.println(arddata);
      
    }
  } while (row != NULL);
  delete cur_mem;
  
  delay(10000);
  
}

MySQL Views

The table(s) that we created were nice and simple for Arduino data, but they couldn’t be used directly for any types of statistics like daily/hourly/minute averages/maxs or minimums. So we created some MySQL views.

The first things that we needed to do was create some “time” columns like HOUR and MINUTE.

CREATE VIEW v_raw as 
 SELECT HOUR(thetime) as HOUR, 
   MINUTE(thetime) as MINUTE, 
   randint, saw, sstatus from datal 
   order by thetime DESC

This SQL statement creates a view (v_raw) that shows the hour, minute fields with data ordered from newest to oldest. The query of : SELECT * FROM v_raw; gives:

We then created a number of statistic views like:

CREATE VIEW v_min AS 
  SELECT HOUR(thetime) as HOUR, MINUTE(thetime) as MINUTE, 
    AVG(randint), AVG(saw) 
   from datal 
     GROUP BY HOUR,MINUTE ORDER BY thetime LIMIT 720

This gave us some minute averages:

Node-Red with MySQL

The component node-red-node-mysql can be added manually or via the “Manage Palettes” option.

For our project we wanted to show:

Last 100 Arduino values
Latest Average Minute Values
Latest Average Hourly Value

Then we wanted to Insert a new 0/1 value that could be read back in the Arduino. This inserted value could be used to turn on a relay connected to the the Arduino (like a start a fan, turn on a light etc.)

The logic used Dashboard buttons (to select an action). MySQL nodes do the SELECT or INSERT command, and a Dashboard Table to show the results.

The buttons are configured to pass the SQL views statement to the MySQL nodes, with the results showing in a Dashboard table node.

After the logic is complete, use the Deploy button to make the logic live. The web dashboards can be viewed at: http://yourNodeRed_IP:1880/ui . Below is an example of 1) Minute Averages, and 2) Last 100 (raw values).

Final Comments

For this project we kept things simple, and used tables to show the results. As a next step it would be good to show the results in a trend chart. In a previous blog I looked at putting SQLite into a Node-Red chart. I would use this technique with MySQL data.

Animated Node-Red Graphics with MQTT and SVG

April 15, 2020April 15, 2020Posted in IoT, Node-RedLeave a comment

There is a new SVG (Scalar Vector Graphics) node that is available for Node-Red Dashboards. This node allows for animated Node-Red graphics that can be viewed on a smart phone.

In this blog I wanted to document an example of integrating MQTT messaging to SVG animated graphics.

Getting Started

If you’re unfamiliar with SVG graphics that are some good tutorials. For my own reference I wrote some notes on SVG and Javascript integration.

The Web Dashboards should be at the latest version, you can do this in the “Manage Palette” option. The node-red-contrib-ui-svg node can be install from the “Manage Palette” option, or manually by:

cd ~/.node-red)
npm install node-red-contrib-ui-svg

There is some excellent documentation on this node.

The SVG Graphic node has a built-in SVG graphic editor or SVG code can be pasted directly into the “SVG Source” tab.

For my project I used industrial SVG examples from: https://www.opto22.com/support/resources-tools/demos/svg-image-library

The SVG editor is useful for identified and defining SVG items that are you’d like to animate.

For this project the solar panels (id=panels) and the output (id=watts) were to be dynamically updated from MQTT.

SVG items can be dynamically update by: 1) the “Input Bind” tab in the node’s definition or, 2) as an input message.

An example using the input message approach would be:

In this example the colour of the panels is set to green using an injector and function node.

MQTT with SVG Graphics

There are some good MQTT brokers, such as Mosquitto that can be used. Node-Red also has a MQTT broker node (MOSCA) that is easy to install.

The earlier test logic can be adapted to connect MQTT inputs:

For this example two MQTT tags were used: 1) watts, and 2) panel_status.

MQTT testing can be done with the MQTT client tools, that are installed by:

 sudo apt-get install mosquitto mosquitto-clients -y

From a terminal MQTT tags can be published to a broker (-h servername) with a topic (-t thetopic) and a message (-m themessage) :

mosquitto_pub -h 192.168.0.111 -t watts -m "123"
mosquitto_pub -h 192.168.0.111 -t panel_status -m "gold"

In Node-Red the watts text is updated by the function node code of:


// Pass the MQTT payload
// and update the text, hardcode the units
//

msg.payload = {
"command": "update_text",
"selector": "#watts",
"textContent": (msg.payload + " C")
}

return msg;

The panels color is changed by the function node code of:

//
// Pass the MQTT payload
// as a fill color attribute
//

msg.payload = {
"command": "update_style",
"selector": "#panels",
"attributeName": "fill",
"attributeValue": msg.payload
}

return msg;

Final Comments

Drawing SVG graphics from scratch is awkward, but there are some great Internet examples of pre-build SVG graphics. All it takes is a bit of time to find the graphic items that you need to animate.

In this blog I looked at making SVG files dynamic. It is also possible to put “hot links” on the SVG file to call URLs or to send messages back to Node-Red.

Monitor Linux Servers with SSH/command line tools and Node-Red

April 11, 2020April 16, 2020Posted in Node-RedTagged hddtemp, lm-sensors, Node-Red1 Comment

There are a number of technologies and packages available for monitoring computer hardware. For medium to large systems an SNMP (Simple Network Monitoring Protocol) approach is usually the preferred solution. However if you have a smaller system with older servers there are excellent light weight command line utilities that can be used.

These command line utilities can be remotely run using SSH (Secure Shell) and the output is parsed to return only the data value. This value can then be graphically shown in a Node-Red web dashboard.

In this blog I will show some examples using iostat to monitor CPU utilization, and lm-sensors and hddtemp to monitor temperatures.

iostat – CPU Utilitization

The iostat utility is part of the sysstat package and it is probably already loaded on your systems, if not it can be installed by:

 sudo apt-get install sysstat

When iostat will generate a report of CPU, device and file system utilization.

$iostat 
Linux 4.15.0-72-generic (lubuntu) 	2020-04-16 	_i686_	(4 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          19.48    0.01    7.96    0.65    0.00   71.90

Device             tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
loop0             0.00         0.00         0.00       1543          0
loop1             0.21         0.21         0.00     107980          0
loop2             0.13         0.13         0.00      66224          0
loop3             0.00         0.00         0.00       1141          0
loop4             0.00         0.00         0.00          8          0
sda               1.92        13.08        31.89    6722321   16395304

To find a specific result, an iostat option and some bash code. For example to find just the CPU %idle:

$ iostat -c
Linux 4.15.0-72-generic (lubuntu) 	2020-04-16 	_i686_	(4 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          19.45    0.01    7.94    0.65    0.00   71.95

$ # get the 4th line of just stats
$ iostat -c | sed -n 4p
 19.45 0.01 7.94 0.65 0.00 71.95

$  # get the 4th line, 6th string
$ iostat -c | sed -n 4p | awk '{print $6}'
 71.95

lm-sensors – Chip based temperature sensors

To install lm-sensor on Ubuntu enter:

sudo apt-get install lm-sensors

The next step is to detect which sensors are available and need to be monitored:

 sudo sensors-detect

This step will give a number of prompts about which sensor to scan. Once the scan step is complete the sensors command will return results for hardware that it found:

pete@lubuntu:~$  sensors  
dell_smm-virtual-0
Adapter: Virtual device
Processor Fan: 2700 RPM
CPU:            +42.0°C  
Ambient:        +34.0°C  
SODIMM:         +34.0°C  

acpitz-virtual-0
Adapter: Virtual device
temp1:        +48.5°C  (crit = +107.0°C)

coretemp-isa-0000
Adapter: ISA adapter
Package id 0:  +43.0°C  (high = +87.0°C, crit = +105.0°C)
Core 0:        +42.0°C  (high = +87.0°C, crit = +105.0°C)
Core 1:        +40.0°C  (high = +87.0°C, crit = +105.0°C)

Specific sensors can be shown by: sensors the-chip .The grep command can be used to get a specific line in the output. For example to get just the Core 0 temperature:

pete@lubuntu:~$ sensors | grep 'Core 0'
Core 0:        +45.0°C  (high = +87.0°C, crit = +105.0°C)

The awk command can be used to get just the temperature, (the third string on the line).

pete@lubuntu:~$ sensors | grep 'Core 0' | awk '{print $3}'
+45.0°C

Later in Node-Red I will show this value in a graph or chart.

hddtemp – Monitor Hard Drive Temperatures

hddtemp is a hard drive temperature monitoring package. It can be installed by:

sudo apt-get install hddtemp

By default hddtemp requires superuser rights, to make the results available to non-superusers enter:

sudo chmod u+s /usr/sbin/hddtemp

To see the temperature of a hard drive, enter the drivers device name. For example to see /dev/sda :

pete@lubuntu:~$ hddtemp /dev/sda
/dev/sda: WDC WD3200BPVT-75JJ5T0: 34°C

Again the awk command can be used to parse the output to get just the temperature. For the /dev/sda example the temperature was the fourth string.

pete@lubuntu:~$ hddtemp /dev/sda | awk '{print $4}'
34°C

Psensor – a Sensors Graphic Widget

This is a little off topic but it is worth mentioning. Psensor offers a widget that will show CPU idle time and it monitors data from lm-sensor and hddtemp.

Psensor is install by:

sudo apt-get install psensor

Psensor is a slick utility for local monitoring, but it isn’t really designed to pass information to a central monitoring server.

Remotely Running Commands

Rather than having remote Linux servers send data a central node, the central node can periodically poll the remote servers for data.

SSH (Secure Shell) can be used to run remote commands. The only issue is that SSH needs a user to enter a password. This is fine for manual testing but for automated polling this is a problem. There are a couple of solutions to this problem:

ssh-keygen – can generate an ssh key pair that is stored in a user directory. This allows the standard ssh client to run without entering a password.
sshpass – is an ssh client that includes the username/password as an command line option.

The ssh-keygen approach is recommended for most applications because it does not expose passwords. For the testing I will show the sshpass method and then in the Node-Red project I will use the ssh-keygen approach.

The sshpass is included in many standard distributions and it can be installed by:

sudo apt-get install sshpass

An sshpass example get to get some CPU board and hard drive temperatures would be:

$ sshpass -p pete ssh pete@192.168.0.116 sensors dell_smm-virtual-0
dell_smm-virtual-0
Adapter: Virtual device
Processor Fan: 3044 RPM
CPU: +31.0°C 

$ sshpass -p pete ssh pete@192.168.0.116 hddtemp /dev/sda 
/dev/sda: HTS548040M9AT00: 39°C

Using some grep and awk calls the output can be shortened to just show the temperatures:

$ sshpass -p pete ssh pete@192.168.0.116 sensors | grep temp1 | awk '{print $2}'
+30.5°C

$ sshpass -p pete ssh pete@192.168.0.116 hddtemp /dev/sda | awk '{print $3}'
39°C

With this basic set of commands it is now possible to use Node-Red to periodically poll Linux servers for data.

Node-Red is an web based visual programming environment. Node-Red has a wide variety of nodes that can be wired together to create logic.

NodeRed is pre-installed with the Raspbian images. To install it on other systems see : https://nodered.org/#get-started

For this project I added two nodes:

bigssh – a ssh node that saves and uses ssh keygen credentials
bigtimer – a timer node, that is used to poll for data

These components can installed either manually, or via the “Manage Palette” menu option.

A basic test circuit to manually poll a Linux server and return a temperature, would use an injector, a bigssh and a debug node.

The logic to poll a Linux server every minute and put the results on a Node-Red web dashboard would use a bigtimer, a bigssh, a gauge and a chart node.

The bigssh node can pass all those useful parsed commands that we worked on earlier to a remote node. For example the temperature value on the temp1 (on acpitz-virtual-0) is:

sensors | grep temp1 | awk '{print#2}'

The bigtimer node has a good selection of scheduling and timing function. By default the middle output pin will generate a pulse every minute.

After the logic is complete click on the “Deploy” button on the right side of the menu bar. The Node-Red web dashboard is available at: http://node-red:1880/ui/

Final Comments

In this blog I only looked at three command line utilities there are many others that could use this technique.

Home Assistant with Node-Red

March 23, 2020March 24, 2020Posted in IoT, Node-Red7 Comments

Home Assistant is an open source home automation platform that can monitor and control smart home devices and it integrates with many of other common systems.

Home Assistant installation is targeted for Raspberry Pi’s but other hardware options are available.

I was very impressed how easy it was to install Home Assistant and get a basic home integration system up and running.

There is a huge number of integration solutions (1500+) that connect to most of the mainstream products. However if you want to do some custom Arduino or Raspberry Pi connections there isn’t an easy “out of the box” solution. To solve this requirement Home Assistant has included Node-Red as an add-on.

Node-RED is a visual programming tool for wiring together hardware devices, APIs and online services.

I found that getting the Node-Red integration was a little tricky. This blog will show how to get Node-Red integration working and it includes a simple simulator circuit.

Getting Started

The installation instructions are very straightforward. I would recommend using a wired connection for your Raspberry Pi. A wireless network connection is 100% possible but it is not in the base installation directions.

After the basic installation is complete, add-ons can be installed under the Supervisor->Dashboard. I would recommend installing “File editor” and “Terminal & SSH” add-ons along with Node-Red.

I found that the Node-Red installed without any problems but it required some configuration changes before it would run.

In the Node Red add-on you will need to add a credential_secret and a password.

If Node-Red doesn’t start look at the log for errors (it’s at the bottom of the same page).

The base Node-Red installation has a very good selection of pre-installed nodes. If you wish to add more nodes see the “Manage Pallet” option that is accessed from the top right options icon.

At this stage Node-Red is somewhat standalone and it is not fully integrated with Home Assistant.

Integrating Node-Red with Home Assistant

The directions and files for Node-Red integration can be downloaded to your PC.

Specifically you want to custom_components/nodered directory and files, which will need to be moved to the Raspberry Pi. The Home Assistant “File editor” add-on can be used to create Pi directories and move files from your PC.

The following directories and file should now exist:

/root/config/custom_components/nodered/__init__.py
/root/config/custom_components/nodered/__pycache__
/root/config/custom_components/nodered/binary_sensor.py
/root/config/custom_components/nodered/config_flow.py
/root/config/custom_components/nodered/const.py
/root/config/custom_components/nodered/discovery.py
/root/config/custom_components/nodered/manifest.json
/root/config/custom_components/nodered/save.txt
/root/config/custom_components/nodered/sensor.py
/root/config/custom_components/nodered/services.yaml
/root/config/custom_components/nodered/switch.py
/root/config/custom_components/nodered/websocket.py

/root/config/custom_components/nodered/.translations/en.json

Once this is complete Home Assistant will need to be restarted.

Including Node-Red Integrations

The next step is to create sensors and switches in Node-Red that can be accessed in Home Assistant. Below is a simple circuit that sends a random number (0-100) to a HA entity.

This logic uses a Big Timer node, that generates a pulse every minute from the middle output pin. An injector node allow you to force a new value. A random node will output a new random number whenever the Big Timer or Inject nodes are triggered.

Double-click on the HA entity to configure the HA server and other properties.

Once the logic is complete click the “Deploy” button to make the logic active.

Node-Red integration is enabled by adding it in the Configuration->Integration page.

Overview Dashboard with Node-Red Data

The final step is to modify the Overview Dashboard to include the Node-Red Entity.

For this example I added a gauge component using the Orange-Plus at the bottom right of this Configure UI page.

On the live Overview page it is possible to click on the gauge card and get more information about this sensor.

Final Thoughts

Home Assistant is a very well structured home automation solution that offers a number of excellent approaches to bring in data.

Node-Red is a very flexible programming environment that help expands connectivity to Arduino, Raspberry Pi and other 3rd party services that are not in the base Home Assistant software.

For information on how to connect an Arduino module to Node-Red see:

Arduino talking MQTT to Node-Red

Arduino talking TCP to Node-Red and Python

Micro:bits and Node-Red

February 3, 2020June 29, 2023Posted in Node-Red, Raspberry PiTagged dashboard, IoT, micro:bit, Node-Red, NodeRed2 Comments

BBC Micro Bit, (micro:bit) is an open source hardware ARM-based embedded system designed by the BBC for use in computer education in the UK. The device is half the size of a credit card and has an ARM Cortex-M0 processor, accelerometer and magnetometer sensors, Bluetooth and USB connectivity, a display consisting of 25 LEDs, and two programmable button.

Depending on where you purchase it the price ranges between $15-$20. So this is a very attractive module for the beginning programmer.

The micro:bit module has 2 buttons to interface to it and a small 5×5 LED screen. This is good for small tests but its a little limiting.

For the most part micro:bit is a standalone unit so in this blog I wanted to show how to put micro:bits information on to a Node-Red web dashboard that could be viewed from a smart phone, tablet or PC.

Micro:bits Setup

The micro:bits has a USB connection that can be used for communications to PCs or Raspberry Pi’s. For my setup I used a Raspberry Pi Zero W, with a microUSB-to-USB adapter to connect into the micro:bit.

The micro:bit can be programmed via a nice Web Interface, for details see: https://microbit.org/guide/quick/. For this application I programmed with blocks.

My logic had the temperature and light sensor values written out ever 10 seconds, in the format of: T=xxx, L=xxx, I used a comma separator between the data pieces. Button presses were sent as either A=1, or B=1, .

Node-Red Setup

Node-Red is pre-install on the Raspberry Pi image, if you want to use a PC instead see the Node-Red installation documentation.

A Node-Red has a Serial port component (https://flows.nodered.org/node/node-red-node-serialport) that can be loaded manually or via the Palette Manager.

The first step is to insert a serial input node and define the serial interface. Double-click on the serial input node and edit the serial connection. The interface will vary with your setup but node-red will show a list of possible USB ports. The default baud rate of the micro:bits USB port is 115200. I used a timeout of 200ms to get the messages, but you could also look for a terminating character (the comma “,” could be used).

The logic used 4 Javascript function nodes to parse the micro:bit message

“Get Temp Value” Function:


// Pull out the temperature
//
var themsg = msg.payload;

if (themsg.indexOf("T=") &gt; -1) {

var msgitems = themsg.split(",");

var temp = msgitems[0];
temp = temp.substring(2,4)
msg.payload = temp;
return msg;
}


“Get Light Value” Function:
// Pull out the Light Sensor Value
//
var themsg = msg.payload;

if (themsg.indexOf("T=") > -1) {

var msgitems = themsg.split(",");

var light = msgitems[1];

light = light.substring(2,5)

msg.payload = light;

return msg;

}


“Check Button A” Function:
// If the message is Button A pressed
// "A=1,"
if (msg.payload == "A=1,") {
msg.payload =  1;
return msg;
}

“Check Button B” Function:
// If the message is Button B pressed
// "B=1,"
if (msg.payload == "B=1,") {
msg.payload =  1;
return msg;
}

Chart nodes are used to show the results. (Note: you’ll need to create a dashboard name).
For the button presses a 1-0 transition is needed after a button press, otherwise the chart will always show a value of 1. The 0-1 transition is done using a trigger node.
The final web dashboard is available at: http://your_node_red_ip:1880/UI.

Final Comments
The next step will be to add the ability to have Node-Red write values to the micro:bit. This would be done with the Node-Red serial output node. Micro:bit’s have a serial read function that would then process the command.

InfluxDB with Node-Red

February 1, 2020April 7, 2021Posted in Node-Red, Raspberry PiTagged Influxdb, IoT, Node-Red, NodeRed26 Comments

There are a lot of excellent databases out there. Almost all databases can support time tagged information and if you have regularly sampled data everything works well. However if you have irregularly sampled data things can get a little more challenging.

InfluxDB is an open-source time series database (TSDB). It is written in Go and optimized for fast, high-availability storage and retrieval of time series data in fields such as operations monitoring, application metrics, Internet of Things sensor data, and real-time analytics.

InfluxDB has a number of great features:

when data is added, a time stamp is automatically added if it’s already incluced.
InfluxDB manages aggregation of times (i.e. means over the hour)
Open Source Web Trending packages like Grafana and Chronograf will talk directly to InfluxDB
an SQL language with a time based syntax

In this blog I wanted to document my notes on:

How to add sampled data from Node-Red to Influx
How to view Influx historical data in a Node-Red chart

Why Use Node-Red with Influx

With the great Web trending interfaces like Grafana and Chronograf why use Node-Red?

I really like Grafana, but I didn’t find it to be 100% mobile friendly, whereas Node-Red is designed for mobile use.
if you’re inputting data or doing logic in Node-Red it makes sense to keep the interface logic there also.

The downside of using Node-Red is that you will have to make your own charting controls.

Getting Started with InfluxDB

The official installation document lists the various options based on your OS. For a simple Raspberry Pi or Ubuntu installation I used:

sudo apt-get install influxdb

The influxdb configuration/setup is modified by:

sudo nano /etc/influxdb/influxdb.conf

After configuration changes Influx can be restarted by:

sudo service influx restart

The Influx command line interface (CLI) is useful for getting started and checking queries. It is started by entering: influx (Note: it might be slow to initially come up).

Below I’ve opened the influx CLI and created a new database called nrdb.

~$ influx
Connected to http://localhost:8086 version 1.7.9
InfluxDB shell version: 1.7.9
> create database nrdb
> show databases
name: databases
name
----
_internal
pidata
nrdb
>

Node-Red and Influx

Node-Red is pre-installed on Raspberry Pi. If you need to install Node-Red on a Window, MacOS or Linux node see the installation instructions.

For my testing I used the following definitions:

nrdb – the InfluxDB database
mytemps – the measurement variable for my temperatures
Burlington, Hamilton – two locations for the temperatures
temperatures – the actual temperatures

Two Node-Red libraries were installed:

BigTimer – https://flows.nodered.org/node/node-red-contrib-bigtimer
InfluxDB – https://flows.nodered.org/node/node-red-contrib-influxdb

These libraries can either be installed using npm or within Node-Red using the “Manage Pallet” option.

For this project I create two sets of logic. The first set used the BigTimer to write a new simulated input every minute (via the middle output pin of BigTimer), or manual push in a value. The second part of the logic used a selected time to query the data and present it to a chart and table.

The first step is to drop a InfluxDB outpt and then configure the Influx server, table and measurements.

A Javascript function node (“Simulate an Input”) is used to format the fields and values. The first passed item is the key item, and the second parameter is a tagged value. Note: there are a number of different ways to use this node.

The Big Timer middle output will send a value out every minute. I added an Inject Node (“Force Test”) so I could see more values.

To test that things are running, the influx cli can be used:

> use nrdb
Using database nrdb
> show measurements
name: measurements
name
----
mytemps

> select * from mytemps
name: mytemps
time location temperature
---- -------- -----------
1580584703785817412 Burlington 17
1580584706364427345 Burlington 5
1580584761862704310 Burlington 8

Show Influx Data in a Node-Red Dashboard

For a simple Dashboard I wanted to use a dropdown node (as a time selector), a chart and a table.

The drop down node has a selection of different times.

The payload from the dropdown node would be something like: 1m, 5m, 15m. A Javascript function node (“New Time Scale”) used this payload and created an InfluxDB query.

This syntax can be tested in the influx cli:

> select time,temperature from mytemps where location='Burlington' and time > now() - 5m
name: mytemps
time temperature
---- -----------
1580588829859372644 12
1580588889896729245 6
1580588949931621672 17
1580589009972333308 8
1580589069980649689 12

The InfluxDB input node only has the InfluxDB server information. The query is passed in from the Javascript function node (“New Time Scale”) .

A Javascript function node (“Javascript function node (“Format Influx Results”) is used to put the msg.payload into a format that the chart node can use.


//
// Format the InfluxDB results to match the charts JSON format
//

var series = ["temp DegC"];
var labels = ["Data Values"];
var data = "[[";
var thetime;

for (var i=0; i < msg.payload.length; i++) {
    thetime = Number(msg.payload[i].time); // Some manipulation of the time may be required
    data += '{ "x":' + thetime + ', "y":' + msg.payload[i].temperature + '}';
    if (i < (msg.payload.length - 1)) {
        data += ","
    } else {
        data += "]]"
    }
}
var jsondata = JSON.parse(data);
msg.payload = [{"series": series, "data": jsondata, "labels": labels}];
return msg;


Once all the logic has been updated, click on the Deploy button. The Node-Red dashboard can be accessed at: http://node-red_ip:1880/ui. Below is an example:

Final Comments
This project was not 100% there are still some cleanup items to do, such as:

use real I/O
make the times a little cleaner in the table
a better time selections for the chart.

Also to better explain things I only used 1 location but multiple data points could be inserted, queried and charted.

Sqlite and Node-Red

December 26, 2019May 27, 2021Posted in Node-RedTagged chart, Node-Red, NodeRed, Raspberry Pi, sqlite23 Comments

Sqlite is an extremely light weight database that does not run a server component.

In this blog I wanted to document how I used Node-Red to create, insert and view SQL data on a Raspberry Pi. I also wanted to show how to reformat the SQL output so that it could be viewed in a Node-Red Dashboard line chart.

Installation

Node-Red is pre-installed on the Pi Raspian image. I wasn’t able to install the Sqlite node using the Node-Red palette manager. Instead I did a manual install as per the directions at: https://flows.nodered.org/node/node-red-node-sqlite .

cd ~/.node-red npm i --unsafe-perm node-red-node-sqlite npm rebuild

Create a Database and Table

It is possible to create a database and table structures totally in Node-Red.

I connected a manual inject node to a sqlite node.

In the sqlite node an SQL create table command is used to make a new table. Note: the database file is automatically created.

For my example I used a 2 column table with a timestamp and a value

Insert Data into Sqlite

Data can be inserted into Sqlite a number of different ways. A good approach for a Rasp Pi is to pass some parameters into an SQL statement.

The sqlite node can use a “Prepared Statement” with a msg.params item to pass in data. For my example I created two variable $thetime and $thevalue.

A function node can be used to format a msg.params item.


// Create a Params variable
// with a time and value component
//
msg.params = { $thetime:Date.now(), $thevalue:msg.payload }
return msg;

Viewing Sqlite Data

A “select” statement is used in an sqlite node to view the data.

A simple SQL statement to get all the data for all the rows in this example would be:

select * from temps;

A debug node can used to view the output.

Custom Line Chart

Node-Red has a nice dashboard component that is well formatted for web pages on mobile devices.

To add the dashboard components use the Node-Red palette manager and search for: node-red-dashboard.

By default the chart node will create its own data vs. time storage. For many applications this is fine however if you want long term storage or customized historical plots then you will need to pass all the trend data to the chart node.

For some details on passing data into charts see: https://github.com/node-red/node-red-dashboard/blob/master/Charts.md#stored-data

Below is an example flow for creating a custom chart with 3 values with times.

The JavaScript code needs to create a structure with: series, data and labels definitions


msg.payload = [{
"series": ["A"],
"data": [
[{ "x": 1577229315152, "y": 5 },
{ "x": 1577229487133, "y": 4 },
{ "x": 1577232484872, "y": 6 }
]
],
"labels": ["Data Values"]
}];

return msg;

This will create a simple chart:

For reference, below is an example of the data structure for three I/O points with timestamps:


// Data Structure for: Three data points with timestamps

msg.payload = [{
"series": ["A", "B", "C"],
"data": [
[{ "x": 1577229315152, "y": 5 },
{ "x": 1577229487133, "y": 4 },
{ "x": 1577232484872, "y": 2 }
],
[{ "x": 1577229315152, "y": 8 },
{ "x": 1577229487133, "y": 2 },
{ "x": 1577232484872, "y": 11 }
],
[{ "x": 1577229315152, "y": 15 },
{ "x": 1577229487133, "y": 14 },
{ "x": 1577232484872, "y": 12 }
]
],
"labels": ["Data Values"]
}];

Sqlite Data in a Line Chart

To manually update a line chart with some Sqlite data I used the following nodes:

The SQL select statement will vary based on which time period or aggregate data is required. For the last 8 values I used:

select * from temps LIMIT 8 OFFSET (SELECT COUNT(*) FROM temps)-8;

The challenging part is to format the SQL output to match the required format for the Line Chart. You will need to iterate over each data row (payload object) and format a JSON string.

 //  
 // Create a data variable   
 //  
 var series = ["temp DegC"];  
 var labels = ["Data Values"];  
 var data = "[[";  
   
 for (var i=0; i < msg.payload.length; i++) {  
   data += '{ "x":' + msg.payload[i].thetime + ', "y":' + msg.payload[i].thetemp + '}';  
   if (i < (msg.payload.length - 1)) {  
     data += ","  
   } else {  
     data += "]]"  
   }  
 }  
 var jsondata = JSON.parse(data);  
 msg.payload = [{"series": series, "data": jsondata, "labels": labels}];  
   
   
 return msg;

To view the Node-Red Dashboard enter: http://pi_address:1880/ui

Final Comments

For a small standalone Raspberry Pi project using sqlite as a database is an excellent option. Because a Pi is limited in data storage I would need to include a function to limit the amount of data stored.

Apache Kafka with Node-Red

November 6, 2019November 9, 2019Posted in IoT, Messaging, Node-RedTagged dashboard, IoT, kafka, Node-Red, NodeRedLeave a comment

Apache Kafka is a distributed streaming and messaging system. There are a number of other excellent messaging systems such as RabbitMQ and MQTT. Where Kafka is being recognized is in the areas of high volume performance, clustering and reliability.

Like RabbitMQ and MQTT, Kafka messaging are defined as topics. Topics can be produced (published) and consumed (subscribed). Where Kafka differs is in the storage of messages. Kafka stores all produced topic messages up until a defined time out.

Node-Red is an open source visual programming tool that connects to Raspberry Pi hardware and it has web dashboards that can be used for Internet of Things presentations.

In this blog I would like to look at using Node-Red with Kafka for Internet of Things type of applications.

Getting Started

Kafka can be loaded on a variety of Unix platforms and Windows. A Java installation is required for Kafka to run, and it can be installed on an Ubuntu system by:

apt-get install default-jdk

For Kafka downloads and installation instructions see: https://kafka.apache.org/quickstart. Once the software is installed and running there a number of command line utilities in the Kafka bin directory that allow you to do some testing.

To test writing messages to a topic called iot_test1, use the kafka-console-producer.sh command and enter some data (use Control-C to exit):

bin/kafka-console-producer.sh --broker-list localhost:9092 --topic iot_test1
11
22
33

To read back and listen for messages:

 bin/kafka-console-consumer.sh --bootstrap-server localhost:9092 --topic iot_test1 --from-beginning
11
22
33

The Kafka server is configured in the /config/server.properties file. A couple of the things that I tweeked in this file were:

# advertised the Kafka server node ip
advertised.listeners=PLAINTEXT://192.168.0.116:9092
# allow topics to be deleted
delete.topic.enable=true

Node-Red

Node-Red is a web browser based visual programming tool, that allows users to create logic by “wiring” node blocks together. Node-Red has a rich set of add-on components that includes things such as: Raspberry Pi hardware, Web Dash boards, email, Tweeter, SMS etc.

Node-Red has been pre-installed on Raspbian since 2015. For full installation instructions see: https://nodered.org/#get-started

To add a Node-Red component select the “Palette Manager”, and in the Install tab search for kafka. I found that the node-red-contrib-kafka-manager component to be reliable (but there are others to try).

For my test example I wanted to create a dashboard input that could be adjusted. Then read back the data from the Kafka server and show the result in a gauge.

This logic uses:

Kafka Consumer Group – to read a topic(s) from a Kafka server
Dashboard Gauge – to show the value
Dashboard Slider – allows a user to select a numeric number
Kafka Producer – sends a topic message to the Kafka server

Double-click on the Kafka nodes and in the ‘edit configuration’ dialog create and define a Kafka broker (or server). Also add the topic that you wish to read/write to.

Double-click on the gauge and slider nodes and define a Dashboard group. Also adjust the labels, range and sizing to meet your requirements.

After the logic is complete hit the Deploy button to run the logic. The web dashboard is available at: http://your_node_red_ip:1880/ui.

Final Comment

I found Node-Red and Kafka to be easy to use in a simple standalone environment. However when I tried to connect to a Cloud based Kafka service (https://www.cloudkarafka.com/) I quickly realized that there is a security component that needs to be defined in Node-Red. Depending on the cloud service that is used some serious testing will probably be required.