Tag Archives: ThingsBoard

ThingsBoard: Pulling Data From Multiple Devices

ThingsBoard allows you to pull the latest telemetry from a single device via the API, however if you want to pull that data into another system (e.g. a small display screen or to analyse results from multiple sensors), you have to make a different API call for each device, and also store the device ID for each in the place you are pulling the data from.

Wouldn’t it be useful if there was a way to pull the latest telemetry from multiple devices with one API call? If you write your client code in a generic way, you could dynamically add and remove devices without changing the code at all.

In an ideal world you could pull a JSON object similar to the following:

{
 "deviceOne" : {
         "field1" : 15,
         "field2" : 19
  },
 "deviceTwo" : {
         "field1" : 42,
         "field2" : 394
  },

This is possible in ThingsBoard using Assets to group devices.

Step 1: Set up two tests devices

Create two test devices in ThingsBoard and start posting telemetry from a script, as described at the start of ThingsBoard: Setting up mail alerts. Having a regular set of incoming data allows us to see instantly if this is working. Make two copies of the script, one for each test device, but also edit the script to send a second field (Temp), which we will just fix to one value:

curl -v -X POST -d "{\"$NAME\": $v, \"Temp\": 25}" http:/..........

Step 2: Create an asset

When we look at telemetry from the API, the documentation states we can use the API key:

/api/plugins/telemetry/{entityType}/{entityId}/values/timeseries

On the Entities And Relations page, the different possible entity types are listed. Two of those listed are Device and Asset. A single device is no good if we want multiple devices, so we need to combine the two test (or more when live) devices together to form an asset. There is a guide for doing similar at Data Function based on telemetry from 2 devices. This is not quite what we want, but we can adapt the principals for our needs.

In the Asset section, create a test asset, with the name and type ‘testAsset’. Under relations for this asset, select Outbound relations by picking From, relation type Contains and then select Device. You can now add the two test devices we have created.

Once saved, if you look a the latest telemetry, nothing appears. We need to populate this asset with telemetry by creating a rule chain.

Step 3: Create a rule chain

In Rule Chains, create a new chain called copyMultipleToAsset, set a meaningful description and turn on debugging. Before we do anything else with this, we want to start sending telemetry data to the new chain.

Open the Root rule chain and follow the chain Message Type Switch->Post telemetry->Save Timeseries. Add the new rule chain as a node and link it to Save Timeseries with the label Success. It does not matter if there is already a link on Success, adding another will duplicate the messages.

At this stage we have all devices sending telemetry. While we will filter these later to just the asset type, copying the data from a device to an asset can create a problem if they are both the same type of device. Both have the same keys, in the case of my test scripts, this is ‘soilPC’ and ‘Temp’. What data relates to what device? Will one field overwrite another as the latest? It would be nice if when we pull the ThingsBoard data we could get nested JSON with each device on the first level then each telemetry key underneath.

Create a script node with the following code:

var newMsg = {};
newMsg[metadata.deviceName]= {};

for(var key in msg) {
    newMsg[metadata.deviceName][key]=msg[key];
}

return {msg: newMsg, metadata: metadata, msgType: msgType};

Name it “addDevToKeys” and join this to Input. If you save the rule chain, you can check data is coming in and being transformed using the debugging events.

Next we need to only filter events for devices connected to our testAsset. Create a Check Relation filter called testAssetFilter. Set the direction to ‘From’, Type ‘Asset’, then select testAsset. Again select debug, and do the same for all nodes in this chain. Link this to the addDevToKeys script, with the label “Success”. We can add other Check Relation filters for other Assets later, by creating identical notes and also joining these with “Success”.

Add a Change Originator block, and call this ChangeToTestAsset. Originator Source: Related. Set the Relation filters to Contains, Entity Types: Asset. Join this to the Check Relation filter with the label “True”.

Finally create a Save Timeseries node, call it Save Timeseries and join it to the Change Originator block with “Success”. Save the chain.

The completed rule chain. This also shows the check relation filter added for our next asset.

Step 4: Verify

Go to Assets, pick the testAsset and inspect the latest telemetry. You should see data appear from both devices.

Following the information on my post ThingsBoard: Latest Telemetry From The API, you can check this can be obtained from the API with a command similar to:

$ curl -v -X GET http://s<SERVER>:<PORT>/api/plugins/telemetry/ASSET<ASSET IT>/values/timeseries --header "Accept:application/json" --header "X-Authorization: Bearer xxxx"

{
 "TestDevice":[{"ts":1605462644325,
      "value":"{\"TestDevice-soilPC\":40,\"TestDevice-Temp\":25}"}],
 "TestDevice2":[{"ts":1605461450186,
      "value":"{\"TestDevice2-soilPC\":49,\"TestDevice2-Temp\":25}"}]
}

We can now parse this and either access individual values using the key Device:Key or grab everything as a JSON block. Note the device types do not have to be the same.

Note that one flaw with this is, the data dump from each device is encoded in the value field as a string, this is not true JSON. You do need to pull out the value and then decode that, which can be a pain.

Alternate data model

An alternate is to keep the flat data structure, but for each device, change the key to be deviceName-key. This allows values to be graphed etc. To do this, change the telemetry transformation script to the script below. You could even combine them to have the best of both.

var newMsg = {};
newMsg[metadata.deviceName]= {};

for(var key in msg) {
    var newKey=metadata.deviceName+"-"+key;
    newMsg[newKey]=msg[key];
}

return {msg: newMsg, metadata: metadata, msgType: msgType};

ThingsBoard: Latest Telemetry From The API

To use ThingsBoard data in other applications, you can pull the latest telemetry from the API.

Details for the API and background about obtaining the JWT token can be found at ThingsBoard Rest API. The JWT token is an authentication token to secure data. You can obtain this from the command line using curl:

curl -X POST --header 'Content-Type: application/json' --header 'Accept: application/json' -d '{"username":"<TB User>", "password":"------"}' 'http://<SERVER>:<PORT>/api/auth/login'

A JSON object will be returned (much longer than that shown below):

{"token":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx","refreshToken":"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"}

The first part, just the token is the section you need.

Details of obtaining telemetry can be found at Working With Telemetry. The key we need to access is:

/api/plugins/telemetry/{entityType}/{entityId}/values/timeseries

This can be pulled using curl. The header defines JSON as the expected type and also use the JWT token as the X-Authorization. Note, you must prefix the JWT token with the word “Bearer”.

curl -v -X GET http://<SERVER>:<PORT>/api/plugins/telemetry/DEVICE/<DEVICE ID>/values/timeseries --header "Accept:application/json" --header "X-Authorization: Bearer xxxxxxx JWT Token xxxxxx"

---- output cut -----
{"temperature":[{"ts":1604646983559,"value":"2.67"}],
"humidity":[{"ts":1604646983559,"value":"100.0"}],
"battery":[{"ts":1604646983559,"value":"3.31"}],
"busVoltage":[{"ts":1604646983559,"value":"4.08"}]}

From Python

import requests 

      URL = 'http://'+tbServer+'/api/plugins/telemetry/DEVICE/'+devId+'/values/timeseries'
        APIheader = {
            "Accept": "application/json",
            "X-Authorization": "Bearer "+tbJWTtoken
        }
        try:
            r = requests.get(URL, headers=APIheader)
            #print "Response = "+str(r.status_code)
            if(r.status_code==200):
                js=r.json()
                print json.dumps(js, indent=2)
 
            else:
                print "ERROR - "+str(r.status_code)+": "+r.content
        except:
            print "Error contacting ThingsBoard server"

Ensure you set the variables tbServer, devId and tbJWTtoken appropriately.

JWT Tokens Expiring

By default, the token will expire after 2.5 hours. This will get annoying if you embed the token in your applications. While you could make this auto-refresh by automating the above method, it requires hard coding your password in your code, which is more of a security risk than sticking with the same token. You can use the refresh token, but if you want to use something like a Particle Webhook to pull telemetry, that is not going to work. The refresh token also breaks down if the device/system is offline for a while and misses the refresh Window.

You can extend the token expiration time by editing /etc/thingsboard/conf/thingsboard.yml:

    #tokenExpirationTime: "${JWT_TOKEN_EXPIRATION_TIME:9000}" # Number of seconds (2.5 hours)
    tokenExpirationTime: "${JWT_TOKEN_EXPIRATION_TIME:15768000}" # Number of seconds (6 months)

Note, this is not considered good security practice. Only do this if you are not dealing with personal data or you do not care if your telemetry can be leaked into the wild.

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.
#!/bin/bash
# ThingsBoard Telemetry Test

ACTOK='blahblahblah'
# Insert your server and port number
SRV='thingsboardserver.x.y:port'

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                                                                                                         

echo $BASE $VARI $SL $RANGE

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

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 127.0.0.1 -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)  |           |          |
Indexes:
    "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';
      key
----------------
 soilPC
 soilMoisture
 devCharge
 chargeStatus
 power
 devPower
 key1
 key4
 key2
 battery
 busVoltage
 current
 values
 key3
 pressure
 temperature
 humidity
 onBatteryPower
(18 rows)

thingsboard=# delete from ts_kv where entity_id='1ea5d8e1f450e60959cbf5261aa9fac' and key like 'key%';
DELETE 4
thingsboard=# delete from ts_kv where entity_id='1ea5d8e1f450e60959cbf5261aa9fac' and key='values';
DELETE 20
thingsboard=# select distinct key from ts_kv where entity_id='1ea5d8e1f450e60959cbf5261aa9fac';
      key
----------------
 power
 devPower
 battery
 busVoltage
 current
 pressure
 temperature
 humidity
 onBatteryPower
 soilPC
 soilMoisture
 devCharge
 chargeStatus
(13 rows)

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

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';
      key
----------------
 power
 devPower
 battery
 current
 busVoltage
 pressure
 temperature
 humidity
 onBatteryPower
 humidity'
 soilMoisture
 soilPC
 devCharge
 chargeStatus
(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';
DELETE 1

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 (%)’.