All posts by Dave Hartburn

ThingsBoard: Setting up mail alerts

As well as graphing, wouldn’t it be useful if ThingsBoard could send a mail alert when a particular parameter for a device goes above/blow a threshold, or when telemetry has not been received for a period of time?

Such things are possible, the following walks through the required configuration steps.

Set up a test device and client

It is easier if we can control the values being sent to ensure this works, so set up a test client and a script to populate it with data.

  • Login to ThingsBoard and go to Devices
  • Add new device
  • Call it TestDevice and give it a device type of GenericTest
    • This is where the ThingsBoard GUI can be frustrating. If you try to type GenericTest, it will attempt to match an existing type and keep overtyping what you are writing. It can be easier to type your new type in another window then cut and paste the whole string in one go.
  • Copy the access token then open the Latest Telemetry tab
  • Paste your server details and access token into the script below and run. You should see the Telemetry update.
# ThingsBoard Telemetry Test

# Insert your server and port number

BASE=45		# Base value
VARI=5		# +- variance around the base value
NAME='soilPC'	# Name of the key we are simulating
SL=5		# Number of seconds sleep between data

RANGE=$((VARI*2))	# Range of numbers

if [ "x$1" != "x" ] ; then                                                                                                  BASE=$1                                                                                           fi                                                                                                         


while true; do
	# Get random number
	r=$((RANDOM % RANGE))
	# Add to base value - variance
	echo Sending $v
	curl -v -X POST -d "{\"$NAME\": $v}" http://${SRV}/api/v1/${ACTOK}/telemetry --header  "Content-Type:application/json"
	sleep $SL

The above script will send telemetry every 5 seconds. It will be +/- 5 around the base value 45. It gives slightly more interesting test data. A single parameter will override the base value to give a quick way of triggering alerts.

Configure mail sending

You must have an account with a mail provider to be able to send mail from

  • Login to ThingsBoard with the sysadmin account. This is different to your ‘everyday’ account where you can set up devices.
  • Go to Settings->Outgoing Mail
  • Enter details of your outgoing mail account and mail provider
  • Click ‘Send Test Mail’
  • If a test mail is received, click ‘Save’

Alert when a value is above/below a threshold

Generating alerts in ThingsBoard is performed using Rule Chains, these are powerful ways of acting on data, but can be difficult to understand at first. Before following this section, it is worth at least a skim read of ThingsBoard Rule Engine. I followed the Create and Clear Alarms tutorial, making changes to suit.

The following example alerts when the soil moisture (soilPC) on a particular device type drops below 30 percent. The rule chain looks like:

  • In Rule Chains, add a new rule chain with the name test device thresholds.
  • Open that chain, it will already have an input node.
  • Ensure the data from the telemetry message is available in the meta data for our use
    • Add a transformation filter
    • Name: Add soilPC to metadata
    • In the script, before the return line, add metadata.soilPC = msg.soilPC;
    • Join this to input
  • Filter on the threshold
    • Add a filter script
    • Name: Soil PC low
    • Filter: return msg.soilPC < 30
    • Join it to the transformation script with type ‘Success’
  • Add a Create Alarm action:
    • Name: Raise Soil Low Alarm
    • Alarm type: SoilLow
    • Propagate selected
    • Alarm severity: Critical
    • The function counts the number of times the alarm is triggered and adds to the metadata
    • When done, join to the filter script with type True
var details = {};
details.soilPC = msg.soilPC;

if (metadata.prevAlarmDetails) {
    var prevDetails = JSON.parse(metadata.prevAlarmDetails);
    details.count = prevDetails.count + 1;
} else {
    details.count = 1;
return details;
  • Create a ‘to email’ transformation
    • Name: Mail Soil Warning
    • Set the from and to address
    • Subject: Device ${deviceName} soil moisture low
    • Body: Device ${deviceName} reporting soil moisture low, currently at ${soilPC}%
    • Join to Create Alarm with type ‘Created’
  • Add the external type ‘Send Mail’
    • Name: SendMail
    • Use system SMTP settings
    • Join to email transformation with type ‘Success’.
  • Add a ‘Clear Alarm’ action
    • Name: Clear Soil Low Alarm
    • In the function, before the return, add details.clearedSoil = msg.soilPC;
    • Alarm Type: SoilLow
    • Add to the ‘soil PC low’ filter script with type ‘False’
  • Create a ‘to email’ transformation
    • Name: Cleared Soil Warning
    • Set the from and to address
    • Subject: Device ${deviceName} soil moisture alert cleared
    • Body: Device ${deviceName} now reporting soil moisture at ${soilPC}%, alarm cleared
    • Join to Clear Alarm with type ‘Cleared’
    • Link to the same SendMail function we created for the alarm creation

The new rule chain will not be triggered unless it is linked from the root rule chain. There are a number of ways to do this. I prefer to filter on device type, then if a number of devices share the same data keys (e.g. temperature), the wrong devices to not trigger the wrong rule chain.

  • Open the Root Rule Chain
  • Create a filter switch
    • Name: DeviceTypeSwitch
    • Function: return metadata.deviceType
    • This produces the output of each type of DeviceType used when receiving telemetry.
    • Link this to the existing ‘Save Timeseries’ with type ‘Success’
    • This will save data as normal, then look at processing it.
  • Add the new rule chain we just created.
  • Join it to the new filter switch with a label that matches the device type being used, e.g. SoilSensor
  • Save and await mails, using the test script to trigger alerts.
  • If the process is not working, ‘Debug Mode’ can be turned on on any device to view events. Remember to remove debugging when finished.

Alert on no telemetry received

Has your device stopped working for some reason? You need to know ASAP, so configure ThingsBoard to send a mail. Based on the Thingsboard Inactivity Tutorial. The default time out is 10 minutes. I changed this to 1 hour by editing the thingsboard.yml file, changing defaultInactivityTimeoutInSec and restarting.

The following rule chain will be created:

  • Create a test device and make sure you can send telemetry to it, as above.
  • Under the device, go to Attributes, select server attributes and click + to add
    • Create a attribute inactivityTimeout of type integer and enter the timeout in ms.
    • For testing, make this short.
  • Create a new rule chain, called ‘Inactivity Alerts’
  • Add a filter switch:
    • Name: DeviceTypeSwitch
    • Function: return metadata.deviceType;
    • Join to the input.
    • This will return the device type for any inactive device. We can either pick different actions for a each device type or just feed all the ones we want into the next stage.
  • We need the inactivity time available to the mail sending later in the chain. This data must be copied from the message to the metadata.
    • Create a transformation script
      • Name: Add Inactivity Time To Metadata
      • Transform:
var tdiff = msg.lastInactivityAlarmTime-msg.lastActivityTime;
var tmin = Math.round(Math.floor(tdiff/1000)/60, 1);
metadata.inactiveMin = tmin;
metadata.moo = 12;
return {msg: msg, metadata: metadata, msgType: msgType};
  • Join the script to the device switch, creating a label for any device type to monitor inactivity for.
  • Add a ‘message type switch’
    • Join to transformation script with type ‘Success’
  • Add a create alarm node:
    • Name: Inactivity Alarm
    • Alarm Type: Inactivity Alarm
    • Select Propagate
    • Join to SwitchMessageType with ‘Inactivity Event’
  • Create a ‘to email’ transformation:
    • Name: Mail inactivity
    • Set to and from addresses
    • Subject: Device ${deviceName} inactivity
    • Body: Device ${deviceName} has been inactive for ${inactiveMin} minutes
    • Join to ‘create alarm’ with type ‘Created’
  • Add send mail external node and join it to the email transformation with Success
  • Create a clear alarm node:
    • Name: Clear Inactivity
    • Alarm Type: Inactivity Alarm
    • Join to SwitchMessageType with ‘Activity Event’
  • Add a ‘to email’ transformation:
    • Name: Mail device recovery
    • Set to and from addresses
    • Subject: Device ${deviceName} recovery
    • Body: Device ${deviceName} has returned to service and posted telemetry
    • Join to ‘clear alarm’ with type Cleared
    • Join output to the send email node with type Success
  • In the Root Rule Chain, add the new rule chain as a node
    • Join it to the Message Type Switch with Activity Events and Inactivity Events

ThingsBoard Data Hacking

Thingsboard is a great IoT logging platform, however some management of data can be impossible to do from the GUI and can get annoying. The following database hacks provide a workaround to some of the common issues.

Connecting To The Database

  • SSH to your ThingsBoard server
  • If you don’t remember the password:
    • cd /etc/thingsboard/conf
    • grep SPRING_DATASOURCE thingsboard.yml
  • Connect with psql -U <username> -d <database> -h -W
    • The default database name is ‘thingsboard’
  • Leave the postgres=# prompt with \q

Database Tables

Postgres does not support ‘show tables’. Use ‘\dt‘ to view tables:

thingsboard=# \dt
                List of relations
 Schema |         Name         | Type  |  Owner
 public | admin_settings       | table | postgres
 public | alarm                | table | postgres
 public | asset                | table | postgres
 public | attribute_kv         | table | postgres
 public | audit_log            | table | postgres
 public | component_descriptor | table | postgres
 public | customer             | table | postgres
 public | dashboard            | table | postgres
 public | device               | table | postgres
 public | device_credentials   | table | postgres
 public | entity_view          | table | postgres
 public | event                | table | postgres
 public | relation             | table | postgres
 public | rule_chain           | table | postgres
 public | rule_node            | table | postgres
 public | tb_user              | table | postgres
 public | tenant               | table | postgres
 public | ts_kv                | table | postgres
 public | ts_kv_latest         | table | postgres
 public | user_credentials     | table | postgres
 public | widget_type          | table | postgres
 public | widgets_bundle       | table | postgres
(22 rows)

Describe a table with ‘\d‘, e.g.:

thingsboard=# \d device
                           Table "public.device"
     Column      |          Type          | Collation | Nullable | Default
 id              | character varying(31)  |           | not null |
 additional_info | character varying      |           |          |
 customer_id     | character varying(31)  |           |          |
 type            | character varying(255) |           |          |
 name            | character varying(255) |           |          |
 search_text     | character varying(255) |           |          |
 tenant_id       | character varying(31)  |           |          |
    "device_pkey" PRIMARY KEY, btree (id)
  • device – Contains the device to ID mappings. This is a different ID than what can be found from the ThingsBoard device control panel in the GUI
  • ts_kv – Telemetry data. entity_id can be the device ID, ‘key’ is the field name.

Removing unwanted telemetry fields

ThingsBoard never forgets. If you have sent data to a device ID, it will remember this field for ever, and always show these fields as being available for graphing & reporting on the dashboards. Latest Telemetry from the GUI will show these fields have not been received for a long time.

The following session determines a device ID for a known device (SoilSensor1), then deletes the test fields key1 to key4, along with ‘values’ which was created in error during an integration configuration. For the GUI, the ts_kv_latest table also needs cleaning.

thingsboard=# select id,name from device where name='SoilSensor1';
               id                |    name
 1ea5d8e1f450e60959cbf5261aa9fac | SoilSensor1
(1 row)

thingsboard=# select distinct key from ts_kv where entity_id='1ea5d8e1f450e60959cbf5261aa9fac';
(18 rows)

thingsboard=# delete from ts_kv where entity_id='1ea5d8e1f450e60959cbf5261aa9fac' and key like 'key%';
thingsboard=# delete from ts_kv where entity_id='1ea5d8e1f450e60959cbf5261aa9fac' and key='values';
thingsboard=# select distinct key from ts_kv where entity_id='1ea5d8e1f450e60959cbf5261aa9fac';
(13 rows)

thingsboard=# delete from ts_kv_latest  where entity_id='1ea5d8e1f450e60959cbf5261aa9fac' and key like 'key%';
thingsboard=# delete from ts_kv_latest  where entity_id='1ea5d8e1f450e60959cbf5261aa9fac' and key='values';

Note, if you are viewing the Devices tab in ThingsBoard, then some details cache. Navigate to another section then return to verify the unwanted data has gone.

Renaming a field

If you send data to ThingsBoard with a mistake in the field name, when you correct it, ThingsBoard can not tie the two sets of data together. While this is perfectly reasonable, it can be quite annoying. The following example had a rogue apostrophe when sending humidity data. As a result we ended up with two datasets, humidity and humidity’. The data was joined together and verified via a graph on the dashboard:

thingsboard=# select id,name from device where name='SoilSensor2';
               id                |    name
 1eab940ff2287c0a4259322b72a0dfe | SoilSensor2
(1 row)

thingsboard=# select distinct key from ts_kv where entity_id='1eab940ff2287c0a4259322b72a0dfe';
(14 rows)

thingsboard=# update ts_kv set key='humidity' where key='humidity''' and entity_id='1eab940ff2287c0a4259322b72a0dfe';
UPDATE 54827

thingsboard=# delete from ts_kv_latest where key='humidity''' and entity_id='1eab940ff2287c0a4259322b72a0dfe';

Note the single quote needs to be doubled to escape it, and because we already have a field called humidity in the latest telemetry, we just delete rather than rename.

When changing a graph, if you edit it, click on the field, you can just rename in plain text rather than delete the old and set up the new. This is useful if you have added attributes such as colour or a custom label, e.g. ‘Humidity (%)’.

Micro:bit Keypads

Keypads make a convenient way to add lots of buttons to a Micro:bit and they come in a range of sizes. The type above are known as membrane keypads. They have a sticky back, are lightly waterproof and can be wiped clean.

4 x 1 Keypad

A 4 x 1 keypad is named as it is 4 buttons in 1 row. It comes with a ribbon connector cable, with 5 pins. There is one pin for each key and one for ground. The wiring for these can always differ, so it is worth checking. Often ground is on a strip of cable on it’s own. The connector usually had a small number on each end denoting the pin number. On the keypad pictured above, the pinout, going from left to right is:

2Key ‘2’pin0
3Key ‘1’pin1
4Key ‘4’pin2
5Key ‘3’pin8

You can see from the above the wiring does not go 1, 2, 3, 4 as you may expect. Sometimes these keypads can have different symbols or be blank.

Have a quick read up on Micro:bit buttons to see how a basic push button works on a Micro:bit. If we use a variable for each button and assign it to a pin we can set an internal pull up resistor then use read_digital() to get the state. The following code will show on the display which button is being pressed:

# 4x1 keypad

from microbit import *

# Define our wiring. The 5th connection goes to ground
key1 = pin1
key2 = pin0
key3 = pin8
key4 = pin2

# Set internal pullup resistor

while True:
    if ( key1.read_digital() == 0 ):"1")
    if ( key2.read_digital() == 0 ):"2")
    if ( key3.read_digital() == 0 ):"3")
    if ( key4.read_digital() == 0 ):"4")

There is a lot of repeated code there and the program does not scale well if we were to use a different sized keypad. We can take advantage of arrays in Python to avoid writing the same code over and over again. Read up on arrays at w3schools. Rather than use a straightforward array of single values, we use a two dimensional array. Each value in the array keypad is a small array itself, with two elements, the pin number and the value. Lists start at 0, so we can access the value of the second pin with keypad[1][1].

# 4x1 keypad

from microbit import *

# Create our pinout as a list. Use pairs of values
# of [ pin, value ]
keypad = [ [pin1, "1"],
           [pin0, "2"],
           [pin8, "3"],
           [pin2, "4"] ]

# Set internal pullup resistor, looping through the list
for k in keypad:

while True:
    # Loop through list checking for keypress
    for k in keypad:
        if ( k[0].read_digital() == 0 ):

4×4 Keypads

We learned above that smaller keypads have one pin for each button, plus an additional pin for ground. A 2 button keypad will have 3 pins, a 4 button keypad has 5 pins. A 4×4 keypad has 16 keys, so it should have 17 pins, right? No, it only has 8 pins, so what is going on here?

The image below shows a circuit diagram of a 4×4 keypad:

The wiring forms a matrix. If you trace the circuit through, you can see if the ‘5’ key is pressed, a connection is formed between pin 7 and pin 3. If we can detect these connections then we can work out what button is being pressed.

Rather than use 8 IO pins on the Micro:bit, we can use a PCF8574 module to easily plug in the keypad and use some binary or hex to quickly work out what buttons are being pressed. Read up on the PCF8574, binary and hex here. Connect the keypad to the PCF8574 board making sure pin 1 on the keypad goes to p0 on the board. P0 is labelled on the underside.

The PCF8574 has four connections, connect Vcc to 3v and GND to GND. The remaining two connections are for the I2C bus. SDA goes to pin 20 and SCL goes to pin 19. It does not matter if you already have a device plugged into these two pins on the Micro:bit. As I2C is a bus technology, it can support multiple devices, each with it’s own address. The three small switches on the PCF8574 are used to change the address of the board, leave then all as ‘Off’.

On the PCF8574, if a pin set to be high is connected to a pin set to be low, then the high pin will be dragged low. If we set all the pins to high, except one row (for example pin 5), we can detect a button press on that row by a pin 1 to 4 also going low. We can write one value to set the pins then read it back, if there are no key presses on that row the value will be the same. If the value is different then something is being pressed. Cycle through pins 5 to 8 in turn setting each to be low then inspect to see if something is pressed and return what.

We can follow the circuit diagram and make a table of what the two low values should be for each key press. As this forms an 8 bit binary string, we can also shorten each of these to give a unique hex value for each pin.


Similar to an array in Python, we can also use a data structure called a dictionary to store these hex values. An array uses an index number 0, 1, 2… etc for each entry, where as a dictionary can use anything else, even text. Read up here. If we make a dictionary with each of the above hex values as keys and the key symbol as the value, e.g. keys[0xBB] = "5", we can quickly look up what key is pressed from the value returned from the PCF8574 board.

This gives us a process we can follow to detect key presses:

  • Create a variable with all the bits high except pin 5.
  • Send variable to the PCF8574 to set the pins.
  • Read data back from the PCF8574 and store in a variable.
  • If the value sent is different to the value received, a key on that row is being pressed
    • Lookup that value in the dictionary and return the key press
  • Skip to the next row and repeat if no press detected

This is done in the readKeypad function in the following code:

# 4x4 keypad
from microbit import *

PCF_ADDR = 0x20     # Use the base address of the PCF8574

# I2C send, function to simplify writing
def sendI2C(addr, value):
    # Convert sent value to a byte array, which is
    # required for I2C
    buf = bytearray(1)
    buf[0] = value
    # Send value
    i2c.write(addr, buf)
    # Short delay to send

def readI2C(addr):
    # Read in as a single byte and return
    v =, 1)[0]
    return v
def readKeypad():
    # Define array of keypress values
    # Curly brackets are a dictionary as we are not
    # using consecutive index values
    keys = {}
    keys[0x77] = "1"
    keys[0x7B] = "2"
    keys[0x7D] = "3"
    keys[0xB7] = "4"
    keys[0xBB] = "5"
    keys[0xBD] = "6"
    keys[0xD7] = "7"
    keys[0xDB] = "8"
    keys[0xDD] = "9"
    keys[0x7E] = "A"
    keys[0xBE] = "B"
    keys[0xDE] = "C"
    keys[0xEE] = "D"
    keys[0xE7] = "*"
    keys[0xEB] = "0"
    keys[0xED] = "#"
    # Set the default keypress to be an empty string
    # to mean nothing pressed
    kp = ""
    # Start with a zero on bit 5, all the rest 1
    setState = 0xEF
    for x in range(4):
        sendI2C(PCF_ADDR, setState)
        # Read value back
        r = readI2C(PCF_ADDR)
        # If the return state is different to the set state, a
        # key press has pulled one of the pins, 1-4 low
        if(setState != r):
            # print("setState=",hex(setState),", r=",hex(r))
            kp = keys[r]
            # Return key press
            return kp
        # Move the zero up to the next pin with a left shift.
        # We want to keep the value at 8 bits, so need to AND
        # with FF. Also, a zero will be moved in from the right.
        # OR with 0x01 to set this lower bit.
        # e.g. 1110 1111 << 1
        #   = 11101 1110 & 0xFF
        #   =  1101 1110 | 0x01
        #   =  1101 1111
        setState = ( ( setState << 1) & 0xFF) | 0x01
    # Return keypress value
    return kp

while True:
        # Read from keypad
        kp = readKeypad()
        # Clear if an empty string comes back
        if( kp == "" ):
            # Otherwise show

Using a dictionary this way also allows you to change the meaning of keys. For example if you were making a calculator you could change the dictionary to return ‘+’ instead of ‘A’. Remember you can use the bin() and hex() functions with print if you want to see what is going on for any stage of the above.

Micro:bit LCD Display Screen

16×2 LCD module with I2C adapter and external power supply

A LCD screen is a great way to give more feedback to a user, either for a text message or values back from a sensor. Known as a 1602 LCD, this common display gives two rows of 16 characters to work with, and can scroll text. However it comes with two issues for the Micro:bit. First is that it needs 5v input and the second is it uses a lot of pins.

The first problem can be overcome by using an external power supply. The Micro:bit can only supply 3.3v. If you try powering a LCD screen from this, it will light up but if you can see anything at all, it will be very faint. There are a number of ways to supply 5v, one of the easiest is using a ‘3.3v 5v breadboard power supply module’. One of these comes with the Elegoo 37 sensor kit, but searching for the above description will find a number of other suppliers. These are usually quite cheap.

Supply the board with anywhere between 6.5v and 12v. A 5v and 3.3v will be supplied by the power output pins.

The second issue was the amount of pins used by the LCD module. The LCD requires 16 pins. While some of these are for power, plugging directly into the Micro:bit will not leave many free pins for other hardware. The easiest solution is to buy a I2C LCD module, pictured above the screen in the title image. To buy one, search for “I2C 1602 LCD”. You will often find screens with these already fitted.

Wiring it up

If you screen or your module has female headers, you can plug the module directly into the back of the screen. In the picture above, both the screen and the I2C module had male headers. Plug these into breadboard making sure the left most pin on the LCD (often marked ‘1’) lines up with the pin on the left of the module when it has it’s four pins on the side pointing out to the left.

From the power supply module, connect a 5v pin to Vcc on the I2C module and connect Gnd to a ground strip on the breadboard. You must connect this to Gnd on the Micro:bit. If you wish, you can connect a 3.3v pin from the power supply board to the 3v pin on the Micro:bit to power it, or you can power it via a serial cable.

The LCD interface module uses I2C, which is a common protocol that can be used to reduce connecting various modules to two wires each. So long as the devices have different addresses (don’t worry about this for now), you can connect multiple devices to I2C. The Micro:bit has two I2C pins to support this, 19 and 20. Connect SDA on the LCD module to pin 20 and SCL to pin 19.

Displaying text in your code

At the time of writing, there does not appear to be a common I2C LCD library, however ‘shaoziyang‘ has produced on at github, which works quite nicely (Thank you!).

The following code displays a hello message (to our dog) and counts up the seconds the Micro:bit has been running. If this does not work first time, check your wiring but also try changing LCD_I2C_ADDR from 63 to 39. Some modules use a different address:

from microbit import *
import time


class LCD1620():
    def __init__(self):
        self.buf = bytearray(1)
        self.BK = 0x08
        self.RS = 0x00
        self.E = 0x04

    def setReg(self, dat):
        self.buf[0] = dat
        i2c.write(LCD_I2C_ADDR, self.buf)

    def send(self, dat):

    def setcmd(self, cmd):

    def setdat(self, dat):

    def clear(self):

    def backlight(self, on):
        if on:

    def on(self):

    def off(self):

    def char(self, ch, x=-1, y=0):
        if x>=0:
            if y>0:

    def puts(self, s, x=0, y=0):
        if len(s)>0:
            for i in range(1, len(s)):

lcd = LCD1620()
lcd.puts("Hello Benji!")
while True:
    lcd.puts("Running=" + str(running_time()/1000), 0, 1)

Functions in the library

clear()Clears the display
backlight(0 or 1)Setting the backlight to 0 turns the backlight off, 1 turns it back on again.
off()Turns the LCD off, thought the backlight stays on
on()Turns the LCD on
Prints a single character at coordinates x,y
Prints the ASCII value 65 (capital A) at coordinates x,y
Using ord, prints @ at coordinates x,y
puts(“Hello World”, 0,1)
Writes a text string at coordinates x,y
Writes “Hello World” at the start of the second line.