DataLogger

From ago control wiki
Jump to: navigation, search

Contents

Data Logger

The data logger logs all events which are sent by devices and journal records. You can request values in a specific time range for a device and you will get them back in JSON format. The database automatically created on installation. Per default we use SQLite but you also can use a supported CppDB Database (e.g. MySQL). For CppDB you need to be on unstable tree and use version >= 2.0.3705.a468809. The SQLite database is located here:

/var/opt/agocontrol/datalogger.db

MySQL Database Setup

First step is to install the needed libs for MySQL CppDB support (e.g. Debian 8):

apt-get install libcppdb-mysql0

You can set the MySQL connection parameters in /etc/opt/agocontrol/conf.d/datalogger.conf (Linux default path):

[datalogger]
dbconnection = "mysql:database=agocontrol;user=agocontrol;password='YourPassword'"

You just need to create an empty database and allow user to access it:

create database agocontrol;
use mysql
create user 'agocontrol'@'localhost' identified by 'YourPassword';
grant all privileges on agocontrol.* to 'agocontrol'@'localhost';
FLUSH PRIVILEGES;

After a restart of the datalogger service MySQL database will be used. Please check the status output if everything correct:

systemctl restart agodatalogger.service
systemctl status agodatalogger.service

Data migration from SQLite to MySQL

You already have a fresh MySQL database from the steps above. Please check if the tables are created - e.g. data, journal. After this you should truncate the tables to have empty tables again before you start the import. So we stop the datalogger service first:

systemctl stop agodatalogger.service

First check if you have MySQL option secure_file_priv enabled:

mysql -e "SELECT @@global.secure_file_priv;"

Note the path if there is some output like "/var/lib/mysql-files/". This path is our dump folder for SQLite. Now start the SQLite Backup:

cd /var/lib/mysql-files/
sqlite3 /var/opt/agocontrol/datalogger.db
sqlite> .mode csv
sqlite> .output data.csv
sqlite> select * from data;
sqlite> .quit

Repeat this step for the journal table:

sqlite3 /var/opt/agocontrol/datalogger.db
sqlite> .mode csv
sqlite> .output journal.csv
sqlite> select * from journal;
sqlite> .quit

After you collected your dumps you can import them into MySQL database:

use agocontrol;
load data infile '../mysql-files/data.csv' into table data fields terminated by ',';
load data infile '../mysql-files/journal.csv' into table journal fields terminated by ',';

You are nearly done - just restart the datalogger service and check the startup output for errors:

 systemctl start agodatalogger.service
 systemctl status agodatalogger.service

Obsolete

Now the data logger is logging all events to the SQLite DB and you can get out data with the messagesend command:

/opt/agocontrol/bin/messagesend.py -c getloggergraph -p deviceid=000000-0000-0000-0000-000000000000 -p start="2012-12-24 12:00:00" -p end="2012-12-24 13:00:00" -p env="temperature" -p freq="5Min"

For the command getloggergraph you need this parameters:

  • deviceid = UUID of device
  • start = Start time for e.g.: 2012-12-24 12:00:00
  • end = End time e.g.: 2012-12-24 13:00:00
  • env = environment e.g.: temperature
  • freq = frequency e.g.: 5Min for 5 minutes - or 10Min, 15Min

With command getdeviceenvironments you will get back a JSON with all devices and their environment's.

curl example

Command:

curl -X POST -d '{"jsonrpc" : "2.0", "method" : "message", "params" : {"content":{ "command":"getloggergraph", "deviceid":"e0227340-2e0e-4048-be06-b1238df38c45", "start":"2013-3-23 08:00:00", "end":"2013-03-23 10:00:00", "env":"temperature", "freq":"5Min"}}, "id":1 }' http://localhost:8008/jsonrpc 

Response:

{"jsonrpc": "2.0", "result": {"unit":"degC","values":{"2013-03-23 08:00:00":["18.06"],"2013-03-23 08:05:00":["18.06"],"2013-03-23 08:10:00":["18.06"],"2013-03-23 08:15:00":["18.06"],"2013-03-23 08:20:00":["18.10"],"2013-03-23 08:25:00":["18.10"],"2013-03-23 08:30:00":["18.10"],"2013-03-23 08:35:00":["18.10"],"2013-03-23 08:40:00":["18.10"],"2013-03-23 08:45:00":["18.10"],"2013-03-23 08:50:00":["18.10"],"2013-03-23 08:55:00":["18.10"],"2013-03-23 09:00:00":["18.10"],"2013-03-23 09:05:00":["18.10"],"2013-03-23 09:10:00":["18.10"],"2013-03-23 09:15:00":["18.10"],"2013-03-23 09:20:00":["18.06"],"2013-03-23 09:25:00":["18.10"],"2013-03-23 09:30:00":["18.10"],"2013-03-23 09:35:00":["18.10"],"2013-03-23 09:40:00":["18.10"],"2013-03-23 09:45:00":["18.10"],"2013-03-23 09:50:00":["18.10"],"2013-03-23 09:55:00":["18.10"],"2013-03-23 10:00:00":["18.20"]}}, "id": 1
Personal tools