Author Topic: Logging events to database for statistics -AlternateEventServer or other options  (Read 825 times)

Offline bruring

  • Sr. Newbie
  • *
  • Posts: 40
  • Karma: +0/-0
Hi all,

The last few days I've been trying to tackle a problem.
I am running a bunch of sensors in my house with Vera Plus as primary and Vera Lite as secondary controller (both UI7).
To log all the sensors in the house (temperature, light, humidity, doors/windows, motion, etc.) I use dataMine2.

dataMine2 is great, except that I miss a few options (and I know these are beyond the scope of what dataMine should be able to do):
- keeping an extended log file of every event that Vera encounters;
- sometimes my sensors disappear and I have to re-add them. I can not link these re-added sensors to the already logged dataset of the same sensor in dataMine2 (it appears as a ghost sensor) so it starts logging from scratch and I lose all historic data;
- giving "fun" statistics like what door has opened/closed most, who is away from the house most (with geofencing), what ours does the cat like to be outside etc.
- programming my own scripts to interprent the statistics;
- logging everything that happens so you can query the data for interesting results, now or in the future :-)

My solution would be to log everything into a (relational) database, like SQL. That way you can run any query and also create statistics etc. using PHP or any language I like. It is more or less like big data. Preferably this database is on my own hosted server, outside of my home network.

What I tried:

I've been using EventWatcher which I really like and gives me a great overview of the recent events that have happened. EventWatcher, however, uses files to store all data which are really hard to interprent to make some statistics. I know I can put these on a network share, but I would like to have something accessible right away. If I could somehow put the EventWatcher Variable and Event log in a database to run my queries it would be great!

EventWatcher also allows to log to a syslog server. I researched the options to run a syslog server and interprent the syslog entries into a MySQL database but I've run into the problem that syslog servers are hard to secure and I need some programming to slash up the text from syslogd into INSERT queries for the database.

I've seen http://forum.micasaverde.com/index.php?topic=15245.0 and tried to implement this. I discovered UI7 works differently with an AlternateEventServer, see http://wiki.micasaverde.com/index.php/AlternateEventServer so that doesn't work.
What I did read on the same Wiki page, is that Micasaverde changed the AlternateEventServer endpoint to /event/event/event instead of /alert, and uses the trendy JSON POST format to log the events (and expects a JSON reply back).

I've created a MySQL database with the same structure as the JSON logged files, and modified the old AlternateEventServer script from http://forum.micasaverde.com/index.php?topic=15245.0 to interprent JSON and reply with JSON, pointing to the new endpoint. Turns out that Vera probably doesn't give you valid JSON, PHP's json_decode doesn't return anything.

If I do a var_export on the _POST that vera gives me, I get this (small snippet):
array (
'json' => '{"PK_Device":########,"PK_Account":#####,"LocalDate":"2017-06-23 00:01:05","Severity":0,"EventType":1,"SourceType":1,"Argument":0,"Code":"","NewValue":"","Description":"YCAM","Users":"","Icon":"camera.png","LocalTimestamp":1498168865,"DeviceID":47,"DeviceName":"YCAM","DeviceType":"urn:schemas-upnp-org:device:DigitalSecurityCamera:2","Room":"Office","Category":6,"SubCategory":-1,"Expiration":"","PK_StorageMethod":0}',
)array (
  'json' => '{"PK_Device":########,"PK_Account":#####,"LocalDate":"2017-06-23 00:01:05","Severity":2,"EventType":4,"SourceType":4,"Argument":0,"Code":"SL_BATTERYALARM","NewValue":"0","Description":"LivingSensorDoorSys","Users":"","Icon":"","LocalTimestamp":1498168865,"DeviceID":191,"DeviceName":"LivingSensorDoorSys","DeviceType":"urn:schemas-micasaverde-com:device:ComboDevice:1","Room":"System","Category":12,"SubCategory":0,"Expiration":"","PK_StorageMethod":0}',
)

Does anyone know how I can tackle above? It seems like vera posts an array with a JSON object, but PHP seems not to be able to decode it. If PHP would be able to decode above, I can process it and put it in my MySQL database. Of course, when I finished this project I will share it here.

I'm also open to other suggestions on how to log everything offsite, maybe I'm totally on the wrong track... :-)

Offline akbooer

  • Master Member
  • *******
  • Posts: 6071
  • Karma: +268/-69
  • "Less is more"
Yes, I've been through this pain too.  I couldn't get EventWatcher to play with UI7, so eventually wrote openLuup which is an open-source emulator of Luup and in which you can do pretty much what you like.

On the way, I also wrote DataYours, as an alternative to dataMine, which can send UDP datagrams, to anywhere your network can see, for any variable change in the system. I don't use PHP, preferring Lua for all my scripting needs these days, so can't help you with your particular query.  But what's the problem?  Are you saying this is not valid JSON?  Surely PHP has all the necessary tools.
3x Vera Lite-UI5/Edge-UI7, 25x Fibaro, 23x TKB, 9x MiniMote, 2x NorthQ Power, 2x Netatmo, 1x Foscam FI9831P, 9x Philips Hue,
Razberry, MySensors Arduino, HomeWave, AltUI, AltHue, DataYours, Grafana, openLuup, ZWay, ZeroBrane Studio.

Offline bruring

  • Sr. Newbie
  • *
  • Posts: 40
  • Karma: +0/-0
Thanks for your reply akboer.

The problem is two folded - 1. the need to have better logging; and 2. the invalid JSON.
I was hoping someone already hacked one of these two. I'm sure I can fix 2. with PHP debugging though.

Also, thank you for pointing out the existence of DataYours and openLuup. I will check then both out.

Offline akbooer

  • Master Member
  • *******
  • Posts: 6071
  • Karma: +268/-69
  • "Less is more"
You may care to look at an example of a Grafana dashboard that is fed from DataYours running under openLuup...

http://forum.micasaverde.com/index.php/topic,40738.msg301351.html#msg301351
3x Vera Lite-UI5/Edge-UI7, 25x Fibaro, 23x TKB, 9x MiniMote, 2x NorthQ Power, 2x Netatmo, 1x Foscam FI9831P, 9x Philips Hue,
Razberry, MySensors Arduino, HomeWave, AltUI, AltHue, DataYours, Grafana, openLuup, ZWay, ZeroBrane Studio.

Offline VeraPlusUser

  • Newbie
  • *
  • Posts: 3
  • Karma: +0/-0
 bruring - Did you mange to debug your way through the json problem.

Otherwise give me a headsup because I have solved the issue and got my event-flow going on UI7 with AlternateEventServer and MySql.


Offline akbooer

  • Master Member
  • *******
  • Posts: 6071
  • Karma: +268/-69
  • "Less is more"
I think that several people would be interested in this solution if you care to post something here on the forum.  I would, for one!
3x Vera Lite-UI5/Edge-UI7, 25x Fibaro, 23x TKB, 9x MiniMote, 2x NorthQ Power, 2x Netatmo, 1x Foscam FI9831P, 9x Philips Hue,
Razberry, MySensors Arduino, HomeWave, AltUI, AltHue, DataYours, Grafana, openLuup, ZWay, ZeroBrane Studio.