HaDiKo Power Analysis System

The goal of this setup is to analyse the power consumption of every floor in the student dorm HaDiKo. Therefore for every flore a sdm630 power sensor is installed for every floor. For each house a raspberry-pi computer collects the data from the sensors via the modbus protocoll. The software used to collect the data via modbus and send it to a database is fhem, a well documented home automatisation software.

Requirements

The System is designed to run on several rasperry-pi computers to gather the data and a server to store data and manage the raspberrys via Ansible .

On a raspberry, you just need a minimal installation of Raspbian and ssh enabled.

The Server should be a Debian-like distribution and needs the packages

• ansible for software management
• whois
• git
• dnsmasq as DNS and DHCP server
• mariadb-server as SQL-Database-Server
• approx as apt-proxy
• ntp as NTP-proxy / server
• xinetd to start approx
• sshpass to manage ssh keys

The installation script ./INSTALL.sh in this repository will install all of the neccessary packages.

Server Setup

Packages

In the designed setupt the server acts as a management server for the raspberrys, a database-server and a frontend for the database.Therefor you'll need to install:

• Ansible version >= 2.3 (Ansible repositorys).
• MySQL or MariaDB database
• ntp server for the raspberrys clocks
• dnsmasq as a DNS and DHCP server, so that the raspberrys can be managed by hostname
• approx apt-proxy for the raspberrys

Optionally you can use Grafana to view the collected data.

The following code ist an example to install all the packages on a Debian GNU/Linux 9.3 (stretch) machine with sysvinit (NOT systemd):

# all packages from default repo
apt install telnet grafana apt-transport-https mariadb-server ntp \
dnsmasq approx xinetd sshpass

# install new repos
echo "deb http://ppa.launchpad.net/ansible/ansible/ubuntu trusty main"\
> /etc/apt/sources.list.d/ansible.list
echo "deb https://packagecloud.io/grafana/stable/debian/ jessie main"\
> /etc/apt/sources.list.d/grafana.list
apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 93C4A3FD7BB9C367
curl https://packagecloud.io/gpg.key | sudo apt-key add -
apt update
apt install ansible grafana

An example of configurations for all these packages are stored in the folder ./config-examples.

Generate Ansible Configurations

Just run INSTALL.sh and remember to save the passwords for:

• the generated raspberry-management ssh-key
• the raspberrys password for the management-user pi (default sudo user on raspbian)
• the FHEM user and password for the FHEM webinterface
• the FHEM telnet interface
• the Ansible-Vault password which stores all other passwords in the depending ansible role
• the database name, user and password

I recommend storing the credentials in a Keepass file.

The script will additionally output a DB_SETUP.sql file that you can use to setup the database. Just run mysql -u root < ./DB_SETUP.sql. Remember to delete the file after using it, because it contains the database password.

Configure Sensor Mapping

The file (symlink) SensorMapping.yml defines the sensor and modbus mapping. Each loactions entry defines a raspberry which collects data of the defined sensors entrys. Be aware that the locations entry has to match the hostname of the respective raspberry (see ./config-examples/hosts and ./config-examples/dnsmasq*). A sampe mapping is provided with this repository in SensorMapping.yml.

Remote Raspberry(s) Setup

To setup a raspberry download Raspbian-lite and copy it to the raspberrys SD-card.

# download raspbian
wget https://downloads.raspberrypi.org/raspbian_lite_latest -O raspbian.img
# copy it to SD-card
dd bs=4M if=./raspbian.img of=/dev/mmcblk0 conv=fsync status=progress

Be aware to choose the right output-device for the SD-card, in this example /dev/mmcblk0. Then remount the SD-card to a $MOUNTPOINT and run:

# default enable ssh-daemon
touch $MOUNTPOINT/boot/ssh
umount $MOUNTPOINT

Hosts Configuration and Deployment

The file ./hosts defines all the hostnames of the raspberrys which should be managed. The section [setup] defines raspberrys with a freshly deployed raspbian. To setup the raspberrys with the previously defined password and ssh-key run:

# delete default hostname in raspberry
ansible-playbook --ask-vault 01-delete-hostname.yml
# setup default
ansible-playbook --ask-vault 02-setup.yml

The section [deploy] defines all the raspberrys to setup FHEM amd send data to the server. Make sure that your ssh-agent is aware of the new ssh key, otherwise ansible won't be able to run. On a Debian system run:

# get ssh agent to work
eval `ssh-agent -s`
ssh-add $HOME/.ssh/id_rsa_pi_mgmt
# deploy fhem
ansible-playbook --ask-vault 03-deploy.yml

If you change any configuration for fhem in the Ansible role roles/03-deploy-fhem/ just run the deployment routine again.

Hosts Configuration: set sdm630 demand-timer

To set the demand timer of a sdm630-sensor, login via telnet and set the demand time to n minutes:

# login via telnet
telnet K4.lan 7072

# login
Trying 192.168.0.104...
Connected to K4.lan.
Escape character is '^]'.
Password:
        # press return twice...
fhem>       # tenet shell active
fhem> set K4_L1 Demand_Period__minutes 5
fhem> set K4_L2 Demand_Period__minutes 5
# [...]
fhem> set K4_EDV Demand_Period__minutes 5
fhem> set K4_Bar Demand_Period__minutes 5
fhem>

This procedure has to be done once per sensor. The sensor will store the setting. The default setting is 60 minutes, we use 5 minutes, and poll the value every 5 minutes. You could also set the demant timer directly at the sdm630-sensor via its touch interface (see manual for info).

Data Analysis

To analyze your data connect to the database and read the data you're interested in. The default user created will be able to read everything. A overview of the database schema is shown below. To use a pseudonyme for the floor names, use the table Floor_anonymous instead of Floor. The IDs of both will be the same, and Floor_anonymous will be updated with a new pseudonyme name after the insertion of a new entry to Floor.

Due to bad DB design choices of the fhem software, a trigger will update the database with tables in 3rd NF instead of 1st NF.

License

Author Stefan Maier Licence GNU GPLv3

Contributing

Bug reports and pull requests are welcome on my website or mail to git@univ-exp.com