Visualizing My Sensor Data

The previous post introduced flows in Node-Red. A post before that introduced my sensor hubs sending data to MQTT. Now to show how they all link together!

To visualise my sensor readings, I want to send them all to InfluxDB. But I don't want to have lots of different ways to write to InfluxDB, I want to have a normalised mechanism.

SmartThings

I configured my smartthings-mqtt-bridge to write to "get", "set" and "cmd" topics. The first node in this flow gets all statuses from all SmartThings-connected devices.

The second node is to split each message into component parts:

This splits the topic, and repeats the message onto the topic home/<roomName>/<sensorName>/<actionType>

Xiaomi
Each device in the Xiaomi platform reports in a different way, and no message includes the sensor name or the sensor location input into the app. So it's at a far lower level than SmartThings, so it needs a lot more processing in Node-Red to normalise sensor data:

1. Make a list of Xiaomi devices with names and locations
This can be done in many ways, and there are some Xiaomi nodes that can be imported into Node-Red for this, but I don't use those nodes - they don't necessarily support the Aqara devices I use, so I need to support them myself. I have a MySQL database on my LAN, and this is what my table looks like:

MySQL disables LAN access by default for security, so I created a PHP script to run against it:

<?php

    $con = mysqli_connect('localhost', 'myUsername', 'myPassword', 'myDatabase');

    

    if(mysqli_connect_errno()) {

        echo "1. Connection error";

        exit();

    }

    $key = $_POST['key'] ?? '000000000000';

    $deviceQuery = "SELECT id, did, type, name, room, timestamp FROM `devices`";

    //echo "Running command: " . $deviceQuery;

    $getDevices = mysqli_query($con, $deviceQuery) or die("2. Device retrieval failed");

    

    $rows = mysqli_num_rows($getDevices);

    //echo $rows . "\n";

    

    $returnString = "";

    while ($currentDevice = mysqli_fetch_assoc($getDevices)) {

        //var_dump($row);

        $id = $currentDevice["id"];

        $did = $currentDevice["did"];

        $type = $currentDevice["type"];

        $name = $currentDevice["name"];

        $room = $currentDevice["room"];

        $timestamp = $currentDevice["timestamp"];

        switch($key) {

            case "did":

                $deviceDetails = "\"" . $did . "\" : { \"type\": \"" . $type . "\", \"name\": \"" . $name . "\", \"room\": \"" . $room . "\"},";

            break;

            case "type":

                $deviceDetails = "\"" . $type . "\" : { \"did\": \"" . $did . "\", \"name\": \"" . $name . "\", \"room\": \"" . $room . "\"},";

            break;

            case "name":

                $deviceDetails = "\"" . $name . "\" : { \"did\": \"" . $did . "\", \"type\": \"" . $type . "\", \"room\": \"" . $room . "\"},";

            break;

            default:

                $deviceDetails = "\"" . $id . "\" : { \"did\": \"" . $did . "\", \"type\": \"" . $type . "\", \"name\": \"" . $name . "\", \"room\": \"" . $room . "\"},";

        }

        //echo "{\"id\": " . $id . ", \"did\": \"" . $did  . "\", \"type\": \"" . $type . "\", \"name\": \"" . $name . "\", \"added\": \"" . $timestamp . "\"}";

        //print_r($id, $did. "\t" . $type . "\t" . $name . "\t" . $timestamp . "\r\n");

        //print_r($currentDevice);

        $returnString = $returnString . $deviceDetails;

    }

    $returnString = "{ \"devices\": " . $rows . "," . substr($returnString, 0, strlen($returnString) - 1) . "}";

    echo $returnString;

    

    mysqli_close($con);

?>

I call this from Node-Red like this:

The first node causes this workflow to run every few minutes, updating the configuration The "Build DB query" node runs this script: msg.url = "http://ipAddressOfMySQLserver/directoryWhereIWroteMyPHPscipt/PHPscriptName.php"; msg.headers = { 'Content-Type' : 'application/x-www-form-urlencoded' }; return msg; The HTTP request node is a completely blank POST node, so it uses data sent to it from the previous node The "Save Xiaomi devices" node runs this script: global.set("xiaomi", msg.payload);
So all Xiaomi device data retrieved from the database is stored in a global variable in Node-Red.


2. Now for the involved bit: 

Each Xiaomi device can write to many topics. The "Route message" node changes the workflow depending on the topic:

The topics "from", "get_id", "read" and "write" are all for specific cases. For now, we'll concentrate on the "otherwise", which should allow us to get sensor data.

This section moves some properties around:

Then it splits the message into multiple messages (a single message from Xiaomi can do many things)

Now what should we do for each different type of node? We should have a different workflow for each type of node, because they all report differently!

If the action is "rgb", it'll be from the hub, reporting the colour of the light.

If the action is "illuminance", it'll be from the hub, reporting ambient light.

If the action is "illumination", it'll be from a motion sensor, reporting ambient light.

If the action is "voltage", it'll be a battery measurement.

etc.

Each type of device uses the action in a slightly different way, so we have to create a function to perform different actions depending on the type of device. An example:

The "Status" function node

// Different sensors use "status" for different things // leak sensor uses it for // cube uses it for how you're interacting with the device // (although rotation is separate) // door uses it for open/close // motion uses it for motion (but not for no_motion) switch(msg.model) { case "sensor_wleak.aq1": msg.action = "water"; break; case "sensor_cube.aqgl01": msg.action = "control"; break; case "sensor_magnet.aq2": if(msg.payload === "close") msg.payload = "Closed"; if(msg.payload === "open") msg.payload = "Open"; msg.action = "contact"; break; case "sensor_motion.aq2": msg.payload = "Active"; msg.action ="motion"; break; default: msg.payload = "Unknown sensor type " + msg.model + " for status action"; } return msg;

The final section writes the sensor status to the same normalised topics as SmartThings writes to:

The function node will look familiar, as it does the same thing as the SmartThings function node:</ br> // the topic xiaomi/to/write is used when I send a command to the gateway if(msg.cmd != "write_ack") { var sid = msg.sid; var thisSensor = global.get("xiaomi")[sid]; msg.room = thisSensor["room"]; msg.sensor = thisSensor["name"]; msg.topic = "home/" + msg.room + "/" + msg.sensor + "/" + msg.action; msg.qos = 0; msg.retain = false; return msg; }
And finally I write to the topic.
So the topics are normalised. How do I visualise them?

The "Limit message" nodes you'll see in my configurations, by the way, are a workaround for MQTT repeating messages when there are multiple subscribers.
The function node takes the MQTT message and converts it to Line Protocol: var tokens = msg.topic.split("/"); database=tokens[0] + "_stats"; room = tokens[1]; sensorName = tokens[2].replace(/\s+/g, ''); metricName = tokens[tokens.length - 1]; value = msg.payload; var typedValue; if(value.match("^[0-9]+")) { // Assume it's a number typedValue = parseFloat(value); } else { typedValue = "\"" + value + "\""; } msg.payload = typedValue; msg.URL ="http://IPaddressOfMyTelegrafGateway:8086/write?db=" + database; msg.payload = metricName + ",room=" + room + ",type=" + metricName + ",name=" + sensorName + " value=" + typedValue; return msg;

The HTTP node is a blank POST to write to my InfluxDB database.

Here are some of the cells I created to show this sensor data: