Downsampling smart meter data with InfluxDB

This article is based on the official InfluxDB documentation on Downsampling and data retention.

I’m using a P1 (smart energy meter) database for this example.

First, change your default retention policy and create at least one additional retention policy:

CREATE RETENTION POLICY "168_hours" ON "P1_External" DURATION 168h REPLICATION 1 DEFAULT
CREATE RETENTION POLICY "2yr" ON "P1_External" DURATION 104w REPLICATION 1 

Create a test query that summarizes the data that needs to be stored in the downsampled data:

SELECT mean("current_delivery") as "current_delivery", mean("current_usage") as "current_usage", last("total_usage_gas") as "total_usage_gas", last("total_usage_t1") as "total_usage_t1", last("total_usage_t2") as "total_usage_t2", last("total_delivery_t1") as "total_delivery_t1", last("total_delivery_t2") as "total_delivery_t2" FROM energy_p1_actual GROUP BY "name", time(1h) ORDER BY time DESC LIMIT 10

Then, define a continuous query from this:

CREATE CONTINUOUS QUERY "cq_60m" on "P1_External" BEGIN SELECT mean("current_delivery") as "current_delivery", mean("current_usage") as "current_usage", last("total_usage_gas") as "total_usage_gas", last("total_usage_t1") as "total_usage_t1", last("total_usage_t2") as "total_usage_t2", last("total_delivery_t1") as "total_delivery_t1", last("total_delivery_t2") as "total_delivery_t2" INTO "2yr"."energy_p1_history" FROM energy_p1_actual GROUP BY "name", time(1h) END

As our retention policy is set to 2 hours the continuous query will run every two hours to summarize the data.

Make Proxmox VLAN aware

I’m using Proxmox as a hypervisor to run my virtual machines and use two VLANs in my home network: one for normal traffic and one separate VLAN for IoT traffic. Virtual machines should be connected to one of those networks. The normal network is typically untagged (vlan ID 20) while the IoT traffic is tagged with VLAN 21.

Configuration file: /etc/network/interfaces

auto lo
iface lo inet loopback

iface eno1 inet manual

auto vmbr0
iface vmbr0 inet manual
        bridge-ports eno1
        bridge-stp off
        bridge-fd 0
        bridge-vlan-aware yes
        bridge-vids 2-4094

auto vmbr0.20
iface vmbr0.20 inet static
        address 192.168.20.x/24
        gateway 192.168.20.1

This should result in the following Proxmox network configuration:

Proxmox host system network configuration

Now you can easily add a network adapter to a virtual machine and tag it with the correct VLAN.

Virtual machine network adapter configuration, including tagged VLAN.

Install Telegraf for monitoring purposes

sudo apt-get update
curl -sL https://repos.influxdata.com/influxdb.key | sudo apt-key add -
echo "deb https://repos.influxdata.com/debian buster stable" | sudo tee /etc/apt/sources.list.d/influxdb.list
sudo apt-get update
sudo apt-get install telegraf

The telegraf configuration is located in /etc/telegraf/

Example configuration:

[global_tags]
[agent]
  interval = "60s"
  round_interval = true
  metric_batch_size = 1000
  metric_buffer_limit = 10000
  collection_jitter = "0s"
  flush_interval = "10s"
  flush_jitter = "0s"
  precision = ""
  hostname = "DB152"
  omit_hostname = false
[[outputs.influxdb]]
  urls = ["https://192.168.21.152:8086"]
  database = "Verhaeg_Monitoring"
  username = "xxx"
  password = "xxx"
  insecure_skip_verify = true
[[inputs.cpu]]
  percpu = true
  totalcpu = true
  collect_cpu_time = false
  report_active = false
[[inputs.disk]]
  ignore_fs = ["tmpfs", "devtmpfs", "devfs", "iso9660", "overlay", "aufs", "squashfs"]
[[inputs.mem]]
[[inputs.swap]]
[[inputs.net]]
  interfaces = ["ens18"]
[[inputs.netstat]]
[[inputs.kernel]]
[[inputs.system]]
[[inputs.processes]]
[[inputs.diskio]]

Updating Eclipse Ditto

First, kill all docker containers. Then remove them, and remove their related images:

docker kill $(docker ps -q) && docker rm $(docker ps -a -q) && docker rmi $(docker images -q)

Then we get the latest version of the docker-compose from GitHub:

wget https://github.com/eclipse/ditto/archive/refs/heads/master.zip
unzip master.zip

Unzip it, browse to the right directory, and start it:

cd master/deployment/docker
docker-compose up -d

Don’t forget to adjust the password:

openssl passwd -quiet
 Password: <enter password>
 Verifying - Password: <enter password>

Append the printed hash in the nginx.httpasswd (in the same folder as docker-compose.yml) file placing the username who shall receive this password in front like this:

ditto:A6BgmB8IEtPTs

Windows 10 taskbar in Windows 11

I’m still used to using the “small icons” and “never combine” options in the taskbar. It makes it more productive to navigate through different application instances. Microsoft removed these options from Windows 11, but there is a way to get them back.

Download and install ExplorerPatcher and your taskbar will look and behave like the Windows 10 taskbar.

The Windows 10 taskbar look and feel in Windows 11.

Configuring Juniper SRX (100) as a DHCP server

Create a vlan interface:

set interfaces vlan unit 21 family inet address 192.168.x.2/24

Assign the vlan interface to a vlan :

set vlans vlan_name vlan-id 21
set vlans vlan_name l3-interface vlan.21

Assign the vlan to a physical interface:

set interfaces fe-0/0/0 unit 0 family ethernet-switching vlan members vlan_name

Assign the dhcp-local-server service to the vlan interface:

set system services dhcp-local-server group IoT interface vlan.21

Create the DHCP pool:

set access address-assignment pool Verhaeg_IoT family inet network 192.168.x.0/24
set access address-assignment pool Verhaeg_IoT family inet range r1 low 192.168.x.200
set access address-assignment pool Verhaeg_IoT family inet range r1 high 192.168.x.250
set access address-assignment pool Verhaeg_IoT family inet dhcp-attributes name-server 192.168.x.1
set access address-assignment pool Verhaeg_IoT family inet dhcp-attributes name-server 8.8.4.4
set access address-assignment pool Verhaeg_IoT family inet dhcp-attributes name-server 8.8.8.8
set access address-assignment pool Verhaeg_IoT family inet dhcp-attributes router 192.168.x.1

Don’t forget your security zone to allow dhcp traffic:

set security-zone x interfaces vlan.20 host-inbound-traffic system-services dhcp

Validate clients have received an IP address from the DHCP server:

root@ROU-02> show dhcp server binding

IP address        Session Id  Hardware address   Expires     State      Interface
192.168.x.201    2           b6:54:ca:26:51:ae  70785       BOUND      vlan.21
192.168.x.202    3           c8:34:8e:5f:a4:2d  85932       BOUND      vlan.21

Installing and configuring Mosquitto on Debian

Eclipse Mosquitto is an open source (EPL/EDL licensed) message broker that implements the MQTT protocol versions 5.0, 3.1.1 and 3.1. Mosquitto is lightweight and is suitable for use on all devices from low power single board computers to full servers. The MQTT protocol provides a lightweight method of carrying out messaging using a publish/subscribe model. This makes it suitable for Internet of Things messaging such as with low power sensors or mobile devices such as phones, embedded computers or microcontrollers. The Mosquitto project also provides a C library for implementing MQTT clients, and the very popular mosquitto_pub and mosquitto_sub command line MQTT clients. Mosquitto is part of the Eclipse Foundation, is an iot.eclipse.org project and is sponsored by cedalo.com.

https://mosquitto.org/

Installation

wget http://repo.mosquitto.org/debian/mosquitto-repo.gpg.key
sudo apt-key add mosquitto-repo.gpg.key
cd /etc/apt/sources.list.d/
sudo wget http://repo.mosquitto.org/debian/mosquitto-buster.list
apt-get update
apt-get install mosquitto

Configuration

Create file /etc/mosquitto/conf.d/listener.conf:

bind_address x.x.x.x

Create file /etc/mosquitto/conf.d/authorisation.conf:

listener 1883 x.x.x.x

Create a user and password:

mosquitto_passwd -c <password file> <username>
kill -HUP <process id of mosquitto>

Result in /var/log/mosquitto/mosquitto.log:

1636747458: New connection from x.x.x.x:59620 on port 1883.
1636747458: New client connected from x.x.x.x:59620 as y (p2, c1, k60, u'client').
1636747843: New connection from x.x.x.x:5709 on port 1883.
1636747843: New client connected from x.x.x.x:5709 as z (p2, c0, k60, u'client').

Validation

Backup and restore ArangoDB data

You can easily back-up and restore the ArangoDB database from Windows. All you need is WinSCP, Putty, and some diskspace. I’m running the batch script below every time I login to my workstation automatically to backup the configuration database:

plink -batch -pw xxx -t root@1.1.1.1 "systemctl stop arangodb3"
plink -batch -pw xxx -t root@1.1.1.1 "cd /data/backup && zip -r arangodb.zip /data/arangodb/data"
plink -batch -pw xxx -t root@1.1.1.1 "systemctl start arangodb3"
pscp -pw xxx -v -r root@1.1.1.1:/data/backup/arangodb.zip  G:\IoT\ArangoDB\arangodb.zip
plink -batch -pw xxx -t root@1.1.1.1 "rm /data/backup/arangodb.zip"

You can restore the database by installing ArangoDB on another system and copying the data back into the correct directory. Make sure you stop the ArangoDB service before you restore the data.

When the ArangoDB version on the target system is newer then the ArangoDB version on the source system you need to upgrade the database first. Stop the ArangoDB service and start it in the console with the –database.auto-upgrade parameter:

systemctl stop arangodb
/usr/sbin/arangod --uid arangodb --gid arangodb --pid-file /var/run/arangodb3/arangod.pid --temp.path /var/tmp/arangodb3 --log.foreground-tty true --database.auto-upgrade