Device to Database: Grafana and Node-Red

Assignment: Use Grafana to create a dashboard using data from the sensors we set up to measure the environment in the halls of ITP. Set up a Node-Red workflow based on that same data.

Background:

In earlier assignments, we did a few things that set us up for this week:

• We built an Arduino-based temperature, humidity, and soil moisture (among other things) sensor to monitor a plant in the main hallway of ITP

• We sent the data from that sensor device to an MQTT server, and then wrote a script to write that data to a variety of databases — InfluxDB, TimescaleDB, and SQLite3

• We wrote queries in varies SQL-based querying languages to poke around at our data and get familiar with SQL

Now that we’ve done each of these steps, we’re able to use Grafana and Node Red with a better understanding of the more granular tasks they are abstracting away to the UI.

Results:

Grafana

In earlier assignments, I was interested in understanding the range of temperatures we would see at ITP, so I wrote queries to look at median temperature, standard deviation, and max/min values. The results came back in the terminal as values on my screen — interesting, but not visually easy to parse.

I wanted to implement the same sort of queries in Grafana to visually understand the variance in temperature as well as soil moisture for my sensor device.

You can see from the dashboard above that the temperature values stayed within a relatively narrow band. The biggest spread in temperatures was 6 degrees over the course of the periods measured. Soil moisture had a bit more variance, although the units of measurement are harder to place into context.

Curiously, the readings cut out multiple times. Most likely, given that the device was placed in high-traffic areas in the hallway, someone unplugged the device and re-plugged it later. When i picked up my device yesterday, I noticed that the usb cable had become frayed and the wires were exposed— so it is possible that the connection dropped when the cable was at the wrong position after becoming frayed.

Node Red

I want to create a workflow that calculates the average temperature based on MQTT messages, then send an alert if any value exceeds the average by 20%. I set up the flow as follows:

And I wrote the following code to calculate the average temperature and compare it to the most recent temperature value:

// put your phone number here
const phone = '12813006944'
const temperature = Number(msg.payload);
let total;
let tempHistory = [];
for (let i=0;i<tempHistory.length;i++) {
  tempHistory[i] = temperature
  }
function avg (array){
    for (let i=0;i<array.length;i++){
        total += array[i];
        }
let average = total/array.length;
return average;
}

a = avg(tempHistory);
console.log(a);
msg.average = a

if ((Math.abs(a-temperature)/temperature) > .2) {

// get the device id and create a message
const device = msg.topic.split('/')[1];
const alertMessage = `Temperature ${temperature}°F for device '${device}' exceeds the high temperature limit of 80°F`

// publish to the sms topic to send a message
msg.topic = `sms/send/${phone}`;
msg.payload = alertMessage;
return msg;
} else {
    return null;
}

Unfortunately, before I could test this out in production, I took my device down. I could try to inject values into the MQTT to create an average, and then inject values into the message payload to check whether the alert is created — that will be my next step. Conceptually, this should work, but I still need to debug it.