Thermod DB-Stats monitor

Collects statistics on Thermod operation.

It records status changes in order to track switch ON and OFF of the heating and cooling system along with timestamp.

License

Thermod DB-Stats monitor v1.1.0+dev
Copyright (C) 2018 Simone Rossetto [email protected]
GNU General Public License v3

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program.  If not, see <http://www.gnu.org/licenses/>.

How to install

Requirements

Thermod DB-Stats monitor requires Python3 (at least version 3.5) and the packages:

• thermod (>=1.2.0)
• requests (>=2.4.3)

Installation

To install the DB-Stats monitor first uncompress the tarball and run

python3 setup.py install

Building and installing on Debian-based system

A Debian package can be build using git-buildpackage.

Assuming you have already configured your system to use git-buildpackage (if not see Debian Wiki for git-pbuilder, cowbuilder and Packaging with Git) then these are the basic steps:

git clone https://github.com/droscy/thermod-monitor-dbstats.git
cd thermod-monitor-dbstats
git branch --track pristine-tar origin/pristine-tar
git checkout -b debian/master origin/debian/master
gbp buildpackage

The package can then be installed as usual:

dpkg -i thermod-monitor-dbstats_{version}_all.deb

Starting/Stopping the monitor

To start DB-Stats monitor from the same system where Thermod is running simply execute

thermod-monitor-dbstats --dbfile {file-path}

where {file-path} must be set to a file-path where to store all the information get from Thermod.

If Thermod is running on a different system, the option --host can be set to the hostname of that system.

To have the full list of available options run thermod-monitor-dbstats with --help option.

Systemd

If systemd is in use, copy the file thermod-monitor-dbstats.service to folder /lib/systemd/system, change it to meet your requirements (i.e. the path where you want to save the file) then execute

systemctl daemon-reload
systemctl enable thermod-monitor-dbstats.service

to automatically start the monitor at system startup.

The database file

The file created by DB-Stats monitor is a standard sqlite3 file that can be opened with any sqlite3 client. For example, to open a file in read-only mode using the official command line client, execute:

sqlite3 'file:{file-path}?mode=ro'

If the prompt sqlite> appears, the database is opened and ready to process queries. Type .quit to close the file and exit sqlite.

Database schema

The database file is very simple, it contains the table thermod_stats with the following fields:

• hostname
• switchon_time
• switchon_temp
• switchon_status
• switchoff_time
• switchoff_temp
• switchoff_status
• cooling

where every time is saved as a timestamp (seconds since the unix epoch) while every temperature is saved as a float number in celsius or farenheit degrees in accordance with Thermod settings. The cooling column is a boolean indicating that the record is relative to heating or cooling system.

The database file contains also the table thermod_dbstats_version which stores the current version of the database file itself.

Some queries

To get all the records in human-readble format of host mythermo recorded in the year 2018 for heating execute in sqlite> prompt:

select
  hostname,
  datetime(switchon_time, 'unixepoch', 'localtime') as switchon_time,
  switchon_temp,
  switchon_status,
  datetime(switchoff_time, 'unixepoch', 'localtime') as switchoff_time,
  switchoff_temp,
  switchoff_status
from thermod_stats
where hostname = 'mythermo'  -- change here the hostname
  and cooling = 0  -- change to 1 to have the cooling system
  and strftime('%Y', datetime(switchon_time, 'unixepoch', 'localtime')) = '2018';

To compute how many minutes the heating has been ON per each host per each month execute:

select
  hostname,
  strftime('%Y-%m', datetime(switchoff_time, 'unixepoch', 'localtime')) as month,
  round(sum((switchoff_time-switchon_time) / 60)) as heating_on_time
from thermod_stats
  where cooling = 0  -- change to 1 to have the cooling system
group by 1,2
order by 1,2;

To get... something else... ask me, I'll try to help you :-)

Clean database on shutdown

During the shutdown DB-Stats monitor cleans the database in two ways:

1. delete any record that doesn't have a switch off time because that record makes no sense from statistics point of view; if you want to keep those invalid records, start the monitor with --noclean option;

2. delete any record with switch on time older than 3 years; if you want to change this time limit, start the monitor with --timelimit {days} option, where {days} is the number of days to keep.