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

Leave a Reply

Your email address will not be published. Required fields are marked *