Thursday, October 18, 2018

Logging tellsticknet output to a database on ubuntu 18.04LTS

I have a wind sensor on the outside of my house, but it is not placed in an optimal location. So I decided to get some data from the sensor and try to correlate it with the meteorological forecast.

The MySQLdb package is used for MySQL access. Create a MySQL user and database named "weather_logger". My current tables are listed as python comments below.


The script listens for the sigterm signal from systemctl and has some error handlers for network failures. It will write information from these events into the "error_logger" table.

The approach is based on this older post.  It assumes one tellsticknet unit in the network. If there are more, replace the tellsticknet discovery string '<broadcast>'  with the ip address of the tellsticknet unit. It does not care about the rolling code (id) of the sensors as well, even if it puts this information into the MySQL database.

The optimal approach for this script could be to remove all sensor knowledge and just put every string collected into the "unknown_logger" table for others to process.

This script is run at startup of the system by a systemd file, "/lib/systemd/system/tellsticknet.service".



[Unit]
Description=Read oregon sensorvalues from tellsticknet and save in mysql db
After=multi-user.target

[Service]
Type=idle
ExecStart=/usr/bin/python /home/<myuser>/tellsticknet_service/tellsticknet.py

[Install]
WantedBy=multi-user.target



(html by hilite.me)

#! /usr/bin/env python2
# -*- coding: utf-8 -*-



import socket
from datetime import timedelta, datetime
import time, sys, os
import MySQLdb
import signal

from sensors import *


sigterm_exit = False
def exit_check(_signo, _stack_frame):
 global sigterm_exit
 sigterm_exit = True




#   CREATE TABLE `weather_logger`.`temperature_logger` ( `ID` INT NOT NULL AUTO_INCREMENT,
#   `sensorID` INT UNSIGNED NULL DEFAULT NULL , `datetime` DATETIME NULL DEFAULT NULL ,
#   `temperature` FLOAT NULL DEFAULT NULL , `humidity` FLOAT NULL DEFAULT NULL,
#   `battery` TINYINT NULL DEFAULT NULL, PRIMARY KEY (`ID`)) ENGINE = InnoDB;

#   CREATE TABLE `weather_logger`.`wind_logger` ( `ID` INT NOT NULL AUTO_INCREMENT,
#   `sensorID` INT UNSIGNED NULL DEFAULT NULL , `datetime` DATETIME NULL DEFAULT NULL ,
#   `wind` FLOAT NULL DEFAULT NULL , `gust` FLOAT NULL DEFAULT NULL, `direction` FLOAT NULL DEFAULT NULL,
#   `battery` TINYINT NULL DEFAULT NULL, PRIMARY KEY (`ID`)) ENGINE = InnoDB;

#   CREATE TABLE `weather_logger`.`unknown_logger` ( `ID` INT NOT NULL AUTO_INCREMENT,
#   `datetime` DATETIME NULL DEFAULT NULL , `data` VARCHAR(2048) NULL DEFAULT NULL,
#   PRIMARY KEY (`ID`)) ENGINE = InnoDB;

#   CREATE TABLE `weather_logger`.`error_logger` ( `ID` INT NOT NULL AUTO_INCREMENT,
#   `datetime` DATETIME NULL DEFAULT NULL , `data` VARCHAR(2048) NULL DEFAULT NULL,
#   PRIMARY KEY (`ID`)) ENGINE = InnoDB;

#
#   CREATE TABLE `weather_logger`.`raw` ( `ID` INT NOT NULL AUTO_INCREMENT,
#   `datetime` DATETIME NULL DEFAULT NULL , `raw_data` VARCHAR(1024) NULL DEFAULT NULL,
#   PRIMARY KEY (`ID`)) ENGINE = InnoDB;


signal.signal(signal.SIGINT, exit_check)
signal.signal(signal.SIGTERM, exit_check)

MYSQLHOST = '10.0.0.7'

SOCKET_TIMEOUT = timedelta(seconds=5)
REG_INT = timedelta(minutes = 10)



db = MySQLdb.connect(host=MYSQLHOST, user="weather_logger",
 passwd="weather_logger",  db="weather_logger")
cur = db.cursor()

date_t = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
sql = 'insert into error_logger values(NULL,\"%s\", \"%s\")'%(date_t, "Starting tellsticknet logging")
cur.execute(sql)
db.commit()


sock = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
sock.setsockopt(socket.SOL_SOCKET, socket.SO_BROADCAST, 1)
sock.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1)
sock.settimeout(SOCKET_TIMEOUT.seconds)
sock.bind(('',42314))

comm_ok = False
while not comm_ok:
 if sigterm_exit:
  db.close()
  sock.close()
  sys.exit(0)
 try:
  sock.sendto(b'D', ('<broadcast>', 30303))
  data, (tellsticknetip, port) = sock.recvfrom(2048)
  comm_ok = True
 except socket.error: # power out? try again:
  date_t = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
  sql = 'insert into error_logger values(NULL,\"%s\", \"%s\")'%(date_t, "No connection with TellstickNet from init")
  cur.execute(sql)
  db.commit()

last_reg_listener = datetime.now() - REG_INT
while not sigterm_exit:
 if datetime.now() - last_reg_listener > REG_INT:
  comm_ok = False
  while not comm_ok:
   if sigterm_exit:
    db.close()
    sock.close()
    sys.exit(0)
   try:
    sock.sendto("B:reglistener", (tellsticknetip, 42314))
    comm_ok = True
   except:
    date_t = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    sql = 'insert into error_logger values(NULL,\"%s\", \"%s\")'%(date_t, "No connection with TellstickNet from reglistener")
    cur.execute(sql)
    db.commit()
  last_reg_listener = datetime.now()
 try:
  data,(address, port) = sock.recvfrom(2048)
  if (data.split(":")[6][6:10]=="F824"):
   (rc, temp,hum, batt) = decodeF824(data.split(":")[7][5:5+14])
   date_t = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
   sql = 'insert into temperature_logger values(NULL,%d,\"%s\", %f,%f, %d)'%(rc, date_t, temp, hum, int(batt))
   #print(sql)
   cur.execute(sql)
   db.commit()
  elif (data.split(":")[6][6:10]=="1984"):
   (rc, w, wg, direction, batt) = decode1984(data.split(":")[7][5:5+16])
   date_t = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
   sql = 'insert into wind_logger values(NULL,%d,\"%s\", %f,%f,%f, %d)'%(rc, date_t, w, wg, direction, int(batt))
   cur.execute(sql)
   db.commit()
  else:
   date_t = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
   sql = 'insert into unknown_logger values(NULL,\"%s\", \"%s\")'%(date_t, data)
   cur.execute(sql)
   db.commit()
 except socket.timeout: # time out, try again
  pass
 except socket.error: #power fail?
  date_t = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
  if sigterm_exit:
   sql = 'insert into error_logger values(NULL,\"%s\", \"%s\")'%(date_t, "sigterm recived")
   cur.execute(sql)
   db.commit()
   break
  else:
   sql = 'insert into error_logger values(NULL,\"%s\", \"%s\")'%(date_t, "No connection with TellstickNet from main loop")
   cur.execute(sql)
   db.commit()
 except:
  date_t = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
  sql = 'insert into error_logger values(NULL,\"%s\", \"%s\")'%(date_t, sys.exc_info()[0])
  cur.execute(sql)
  db.commit()


#got SIGTERM
db.close()
sock.close()


The "sensors.py" file used:


#some sensors from telldus-core/service/
# this code depends on signal checking done in the tellsticknet
#
#

def decode2914(inp):
 #source:telldus-core/service/ProtocolOregon.cpp@c9567f
 #// rain
 value = int(inp, 16)

 messageChecksum1 = value & 0xF
 value  = value >> 4
 messageChecksum2 = value & 0xF

 value  = value >> 4
 totRain1 = value & 0xF
 value  = value >> 4
 totRain2 = value & 0xF
 value  = value >> 4
 totRain3 = value & 0xF
 value  = value >> 4
 totRain4 = value & 0xF
 value  = value >> 4
 totRain5 = value & 0xF
 value  = value >> 4
 totRain6 = value & 0xF
 value  = value >> 4
 rainRate1 = value & 0xF
 value  = value >> 4
 rainRate2 = value & 0xF
 value  = value >> 4
 rainRate3 = value & 0xF
 value  = value >> 4
 rainRate4 = value & 0xF

 value  = value >> 4
 battery = value & 0xF #// PROBABLY battery
 value  = value >> 4
 rollingcode = ((value >> 4) & 0xF) + (value & 0xF)
 checksum =    ((value >> 4) & 0xF) + (value & 0xF)
 value  = value >> 8
 channel = value & 0xF
 checksum = checksum + totRain1 + totRain2 + totRain3 + totRain4 + totRain5 + totRain6 +\
 rainRate1 + rainRate2 + rainRate3 + rainRate4 +\
 battery + channel + 0x2 + 0x9 + 0x1 + 0x4

 if (((checksum >> 4) & 0xF) != messageChecksum1 or (checksum & 0xF) != messageChecksum2):
  #// checksum error
  return ""

 totRain = ((totRain1 * 100000) + (totRain2 * 10000) + (totRain3 * 1000) +\
 (totRain4 * 100) + (totRain5 * 10) + totRain6)/1000.0*25.4
 rainRate = ((rainRate1 * 1000) + (rainRate2 * 100) + (rainRate3 * 10) + rainRate4)/100.0*25.4

 #std::stringstream retString;
 #retString << "class:sensor;protocol:oregon;model:2914;id:" << static_cast<int>(rollingcode)
 # << ";raintotal:" << std::fixed << std::setprecision(1) << totRain
 # << ";rainrate:" << std::fixed << std::setprecision(1) << rainRate << ";";
 #return "%d\t%f\t%f"%(rollingcode, totRain, rainRate)
 return [rollingcode, totRain, rainRate, battery]




def decode1994(inp):
 #source:telldus-core/service/ProtocolOregon.cpp@c9567f
 #wind
 value = int(inp, 16)
 crcCheck = value & 0xF
 value  = value >> 4
 messageChecksum1 = value & 0xF
 value  = value >> 4
 messageChecksum2 = value & 0xF

 value  = value >> 4
 avg1 = value & 0xF
 value  = value >> 4
 avg2 = value & 0xF
 value  = value >> 4
 avg3 = value & 0xF
 value  = value >> 4
 gust1 = value & 0xF
 value  = value >> 4
 gust2 = value & 0xF
 value  = value >> 4
 gust3 = value & 0xF
 value  = value >> 4
 unknown1 = value & 0xF
 value  = value >> 4
 unknown2 = value & 0xF
 value  = value >> 4
 direction = value & 0xF

 value  = value >> 4
 battery = value & 0xF  #// PROBABLY battery
 value  = value >> 4
 rollingcode = ((value >> 4) & 0xF) + (value & 0xF)
 checksum =    ((value >> 4) & 0xF) + (value & 0xF)
 value  = value >> 8
 channel = value & 0xF
 checksum = checksum + unknown1 + unknown2 + avg1 + avg2 + avg3 + gust1 + gust2 + gust3 + direction + battery + channel

 checksum = checksum + 0x1 + 0x9 + 0x9 + 0x4

 if (((checksum >> 4) & 0xF) != messageChecksum1 or (checksum & 0xF) != messageChecksum2):
  #// checksum error
  return ""

 avg = ((avg1 * 100) + (avg2 * 10) + avg3)/10.0
 gust = ((gust1 * 100) + (gust2 * 10) + gust3)/10.0
 directiondegree = 22.5 * direction

 #retString << "class:sensor;protocol:oregon;model:1984;id:" << static_cast<int>(rollingcode)
 # << ";winddirection:" << directiondegree
 # << ";windaverage:" << std::fixed << std::setprecision(1) << avg
 # << ";windgust:" << std::fixed << std::setprecision(1) << gust << ";";

 #return '%d\t%f\t%f\t%f'%(rollingcode, avg, gust, directiondegree)
 return [rollingcode, avg, gust, directiondegree, battery]


def decode1984(inp):
 #source:telldus-core/service/ProtocolOregon.cpp@c9567f
 #// wind
 value = int(inp, 16)
 crcCheck = value & 0xF
 value  = value >> 4
 messageChecksum1 = value & 0xF
 value  = value >> 4
 messageChecksum2 = value & 0xF

 value  = value >> 4
 avg1 = value & 0xF
 value  = value >> 4
 avg2 = value & 0xF
 value  = value >> 4
 avg3 = value & 0xF
 value  = value >> 4
 gust1 = value & 0xF
 value  = value >> 4
 gust2 = value & 0xF
 value  = value >> 4
 gust3 = value & 0xF
 value  = value >> 4
 unknown1 = value & 0xF
 value  = value >> 4
 unknown2 = value & 0xF
 value  = value >> 4
 direction = value & 0xF

 value  = value >> 4
 battery = value & 0xF  #// PROBABLY battery
 value  = value >> 4
 rollingcode = ((value >> 4) & 0xF) + (value & 0xF)
 checksum =    ((value >> 4) & 0xF) + (value & 0xF)
 value  = value >> 8
 channel = value & 0xF
 checksum = checksum + unknown1 + unknown2 + avg1 + avg2 + avg3 + gust1 + gust2 + gust3 + direction + battery + channel

 checksum = checksum + 0x1 + 0x9 + 0x8 + 0x4

 if (((checksum >> 4) & 0xF) != messageChecksum1 or (checksum & 0xF) != messageChecksum2):
  #// checksum error
  return ""

 avg = ((avg1 * 100) + (avg2 * 10) + avg3)/10.0
 gust = ((gust1 * 100) + (gust2 * 10) + gust3)/10.0
 directiondegree = 22.5 * direction

 #retString << "class:sensor;protocol:oregon;model:1984;id:" << static_cast<int>(rollingcode)
 # << ";winddirection:" << directiondegree
 # << ";windaverage:" << std::fixed << std::setprecision(1) << avg
 # << ";windgust:" << std::fixed << std::setprecision(1) << gust << ";";

 #return '%d\t%f\t%f\t%f'%(rollingcode,avg, gust, directiondegree)
 return [rollingcode, avg, gust, directiondegree, battery]


def decodeF824(inp):
 #source:telldus-core/service/ProtocolOregon.cpp@c9567f
 value = int(inp, 16)
 crcCheck = value & 0xF
 value = value>>4
 messageChecksum1 = value & 0xF
 value = value >>4
 messageChecksum2 = value & 0xF
 value = value >> 4
 unknown = value & 0xF
 value = value >> 4
 hum1 = value & 0xF
 value = value >> 4
 hum2 = value & 0xF
 value = value >> 4
 neg = value & 0xF
 value = value >> 4
 temp1 = value & 0xF
 value = value >> 4
 temp2 = value & 0xF
 value = value >> 4
 temp3 = value & 0xF
 value = value >> 4
 battery = value & 0xF
 value = value >> 4
 rollingcode = ((value >> 4) & 0xF) + (value & 0xF)
 checksum = ((value >> 4) & 0xF) + (value & 0xF)
 value = value >> 8
 channel = value & 0xF
 checksum += unknown + hum1 + hum2 + neg + temp1 + temp2 + temp3 + battery + channel + 0xF + 0x8 + 0x2 + 0x4
 if ((((checksum >> 4) & 0xF) != messageChecksum1) or ((checksum & 0xF) != messageChecksum2)):
  return ""

 temperature = ((temp1 * 100) + (temp2 * 10) + temp3)/10.0
 if (neg):
  temperature = -temperature
 humidity = (hum1 * 10.0) + hum2
 #retStr = "class:sensor;protocol:oregon;model:F824;id:"  #14;temp:0.0;humidity:46;
 #retStr = '%s%d;temp:%1.1f;humidity:%d;'%(retStr,rollingcode,temperature,humidity)
 #return retStr
 #return '%d\t%f\t%f'%(rollingcode, temperature, humidity)
 return [rollingcode, temperature, humidity, battery]




def decode1A2D(inp):
    #source:telldus-core/service/ProtocolOregon.cpp
    value = int(inp,16)

    checksum2 = value & 0xFF
    value = value>>8
    checksum1 = value & 0xFF
    value = value>>8
    checksum = ((value >> 4) & 0xF) + (value & 0xF)
    hum1 = value & 0xF;
    value >>= 8;
    checksum = checksum + ((value >> 4) & 0xF) + (value & 0xF)
    neg = value & (1 << 3)
    hum2 = (value >> 4) & 0xF;
    value >>= 8
    checksum = checksum + ((value >> 4) & 0xF) + (value & 0xF);
    temp2 = value & 0xF;
    temp1 = (value >> 4) & 0xF;
    value >>= 8;
    checksum = checksum + ((value >> 4) & 0xF) + (value & 0xF)
    temp3 = (value >> 4) & 0xF;
    value >>= 8
    checksum = checksum + ((value >> 4) & 0xF) + (value & 0xF)
    rollingcode = value & 0xFF;
    value >>= 8;
    checksum = checksum + ((value >> 4) & 0xF) + (value & 0xF)
    channel = (value >> 4) & 0x7;
    battery = value&0xF #time will tell
    checksum = checksum +  0x1 + 0xA + 0x2 + 0xD - 0xA;

    if (checksum != checksum1):
        return "No";

    temperature = ((temp1 * 100) + (temp2 * 10) + temp3)/10.0;
    if (neg): temperature = -temperature
    humidity = (hum1 * 10.0) + hum2
    #print "temp/hum ", temperature, "/", humidity

    return [rollingcode, temperature, humidity, battery]

No comments:

Post a Comment