We have moved at community.getvera.com

Author Topic: HOWTO: Event logging to database  (Read 39860 times)

Offline Quinten

  • Sr. Member
  • ****
  • Posts: 273
  • Karma: +2/-0
Re: HOWTO: Event logging to database
« Reply #15 on: September 06, 2013, 05:28:03 am »
Right, am back in full swing...  Can you give me an update on where you are with this now?  Or have you abandoned it due to the lack of (uptodate) mysql/php bindings?
VeraLite - UI7 1.7.947 | 8x Fibaro FGD211 | 2x Fibaro FGS221 | 1x Fibaro FGBS321 | 3x Fibaro FGRGBW | 1x Everspring SP814 | 2x Everspring AN157-3 | 2x Heatmiser PRT-TS WiFi | 1x Logitech Harmony Hub | 1x Philips LivingColour

Offline parkerc

  • Beta Testers
  • Sr. Hero Member
  • *****
  • Posts: 2479
  • Karma: +35/-48
  • Life Moves Pretty Fast....
Re: HOWTO: Event logging to database
« Reply #16 on: September 06, 2013, 03:50:30 pm »
Hi @Quinten

I'd prefer to say it's been placed on hold ;)

Things are still as they were, the version of php I have does not seem to have mysqli , but does seem to have PDO (whatever that is). Upgrading the NAS linux version of PhP does not look straight forward to do. ?

I really like the idea of having everything captured in a relational table, (no disrespect to DataMine, as that's great). Do you know if it's possible for every change in a selected variable to be logged/captured

Offline Quinten

  • Sr. Member
  • ****
  • Posts: 273
  • Karma: +2/-0
Re: HOWTO: Event logging to database
« Reply #17 on: September 06, 2013, 04:01:11 pm »
What NAS is it?  It may just be as simple as updating a package, but it could be impossible if it is a closed system.  What I would like to say is that you can test the workings of your web server by just copy and pasting the url out of your log file into a browser window, provided you have access to the webserver from there (i.e., you can see normal pages on it)

Code: [Select]
https://<your.webservers.ip.address>/alert?PK_AccessPoint=35102036&HW_Key=BlahBlah&DeviceID=29&LocalDate=2013-08-21%2003:12:17&LocalTimestamp=1377051137&AlertType=3&SourceType=3&Argument=12&Format=&Code=CurrentTemperature&Value=18&Description=Conservatory%20Temp%20Drops%20Below%2020&Users=157707
From that, the device id and description are used (along with the timestamp).  Once you then have entries in your database, you can set the alternative event server as per the instructions...

As to your last question, anything that can be set up as a notification, can be stored in the database.  The alternative event server is informed at the same time as the event server at MicasaVerde, so it does not replace it, but mirrors it.
VeraLite - UI7 1.7.947 | 8x Fibaro FGD211 | 2x Fibaro FGS221 | 1x Fibaro FGBS321 | 3x Fibaro FGRGBW | 1x Everspring SP814 | 2x Everspring AN157-3 | 2x Heatmiser PRT-TS WiFi | 1x Logitech Harmony Hub | 1x Philips LivingColour

Offline parkerc

  • Beta Testers
  • Sr. Hero Member
  • *****
  • Posts: 2479
  • Karma: +35/-48
  • Life Moves Pretty Fast....
Re: HOWTO: Event logging to database
« Reply #18 on: September 11, 2013, 03:54:29 am »
Hi Quinten

It's an old NAS, the brand name is eTRAYZ and it looks to be running php 5.2.9 which does not seem to support mysqli, and that's why I think it is not updating the tables.

 I looked on the php site and it did not look straight forward thing to update a package  ?

Offline Quinten

  • Sr. Member
  • ****
  • Posts: 273
  • Karma: +2/-0
Re: HOWTO: Event logging to database
« Reply #19 on: September 11, 2013, 04:17:17 am »
Can you copy&paste the following into a file on the NAS and access it in a web browser?  What does it do?

Code: [Select]
<?php

// Make a MySQL Connection
mysql_connect("localhost""YOURUSERNAME""YOURPASSWORD") or die(mysql_error());
mysql_select_db("YOURDATABASENAME") or die(mysql_error());

// Retrieve all the data from the "events" table
$result mysql_query("SELECT * FROM events")
or die(
mysql_error());  

// store the record of the "events" table into $row
$row mysql_fetch_array$result );

// Print out the contents of the entry 
echo "Device: ".$row['device'];
echo 
" Description: ".$row['description'];

?>

VeraLite - UI7 1.7.947 | 8x Fibaro FGD211 | 2x Fibaro FGS221 | 1x Fibaro FGBS321 | 3x Fibaro FGRGBW | 1x Everspring SP814 | 2x Everspring AN157-3 | 2x Heatmiser PRT-TS WiFi | 1x Logitech Harmony Hub | 1x Philips LivingColour

Offline parkerc

  • Beta Testers
  • Sr. Hero Member
  • *****
  • Posts: 2479
  • Karma: +35/-48
  • Life Moves Pretty Fast....
Re: HOWTO: Event logging to database
« Reply #20 on: September 11, 2013, 06:15:44 am »
Trying what you gave me in post 17 does this.

Quote
PK_Alert:0<br />
<b>Fatal error</b>:  Class 'mysqli' not found in <b>/var/www/ibox/alert.php</b> on line <b>20</b><br />

And running the php you provided above, must gives me this?.

Code: [Select]
Device: Description:
« Last Edit: September 11, 2013, 06:18:45 am by parkerc »

Offline Quinten

  • Sr. Member
  • ****
  • Posts: 273
  • Karma: +2/-0
Re: HOWTO: Event logging to database
« Reply #21 on: September 11, 2013, 06:37:09 am »
There is hope yet then :D

I will amend my php scripts to be more compatible (I was only following the PHP recommendations) with older PHP installs.  To be continued!
VeraLite - UI7 1.7.947 | 8x Fibaro FGD211 | 2x Fibaro FGS221 | 1x Fibaro FGBS321 | 3x Fibaro FGRGBW | 1x Everspring SP814 | 2x Everspring AN157-3 | 2x Heatmiser PRT-TS WiFi | 1x Logitech Harmony Hub | 1x Philips LivingColour

Offline Quinten

  • Sr. Member
  • ****
  • Posts: 273
  • Karma: +2/-0
Re: HOWTO: Event logging to database
« Reply #22 on: September 12, 2013, 03:44:06 am »
Right, I've changed the PHP script to use the 'old' mysql functions which means that you should now be able to make it work.  Take the attached zip file and extract it (not in the same directory you've previously used!) and only copy the alert.php file out of it (that's the only file changed).
VeraLite - UI7 1.7.947 | 8x Fibaro FGD211 | 2x Fibaro FGS221 | 1x Fibaro FGBS321 | 3x Fibaro FGRGBW | 1x Everspring SP814 | 2x Everspring AN157-3 | 2x Heatmiser PRT-TS WiFi | 1x Logitech Harmony Hub | 1x Philips LivingColour

Offline parkerc

  • Beta Testers
  • Sr. Hero Member
  • *****
  • Posts: 2479
  • Karma: +35/-48
  • Life Moves Pretty Fast....
Re: HOWTO: Event logging to database
« Reply #23 on: September 12, 2013, 06:53:16 am »
First error was.

Code: [Select]
Fatal error: Call to undefined function header_remove() in /var/www/ibox/alert.php on line 3
So I removed line 3 and now it gives me this..

Code: [Select]
PK_Alert:0

Offline Quinten

  • Sr. Member
  • ****
  • Posts: 273
  • Karma: +2/-0
Re: HOWTO: Event logging to database
« Reply #24 on: September 12, 2013, 07:23:55 am »
The header_remove() is there to create the return reply for Vera, so it may or may not work now.  The reply you see in your browser is again only there for the Vera, you will not see anything else there...  Have you checked the database to see if there's an entry now?
VeraLite - UI7 1.7.947 | 8x Fibaro FGD211 | 2x Fibaro FGS221 | 1x Fibaro FGBS321 | 3x Fibaro FGRGBW | 1x Everspring SP814 | 2x Everspring AN157-3 | 2x Heatmiser PRT-TS WiFi | 1x Logitech Harmony Hub | 1x Philips LivingColour

Offline parkerc

  • Beta Testers
  • Sr. Hero Member
  • *****
  • Posts: 2479
  • Karma: +35/-48
  • Life Moves Pretty Fast....
Re: HOWTO: Event logging to database
« Reply #25 on: September 12, 2013, 07:41:51 am »
Huston we have an entry ;)

Device: 29 Description: Conservatory Temp Drops Below 20

Offline Quinten

  • Sr. Member
  • ****
  • Posts: 273
  • Karma: +2/-0
Re: HOWTO: Event logging to database
« Reply #26 on: September 12, 2013, 07:43:11 am »
\o/

And this is coming from Vera right?  Not from entering the url (somewhere above) directly into the browser?
VeraLite - UI7 1.7.947 | 8x Fibaro FGD211 | 2x Fibaro FGS221 | 1x Fibaro FGBS321 | 3x Fibaro FGRGBW | 1x Everspring SP814 | 2x Everspring AN157-3 | 2x Heatmiser PRT-TS WiFi | 1x Logitech Harmony Hub | 1x Philips LivingColour

Offline parkerc

  • Beta Testers
  • Sr. Hero Member
  • *****
  • Posts: 2479
  • Karma: +35/-48
  • Life Moves Pretty Fast....
Re: HOWTO: Event logging to database
« Reply #27 on: September 12, 2013, 04:45:28 pm »
That one was from the URL you gave me.

I have just sent the command for it to use my NAS as the alternative event server again, so watch this space. ;)

Is there a definitive list on what we can log as I'm keen to track individual temperature changes etc? The wiki talks about these.

Alert Types
1: ALERT_IMAGE
2: ALERT_VIDEO
3: ALERT_TRIGGER
4: ALERT_VARIABLE
5: ALERT_LOGON
6: ALERT_GATEWAY_CONNECTED (Sent if the external IP changes or if it's been more than 24 hours since the last report.)
7: ALERT_SYS_ERROR
8: ALERT_VALIDATE_EMAIL
9: ALERT_VALIDATE_SMS
10: ALERT_SYS_MESSAGE
11: ALERT_TEST_SMS_EMAIL
Source Types
1: SOURCE_USER
2: SOURCE_TIMER
3: SOURCE_TRIGGER
4: SOURCE_VARIABLE
File Formats
1: FORMAT_JPEG
2: FORMAT_MJPEG
3: FORMAT_MP4
« Last Edit: September 12, 2013, 04:47:41 pm by parkerc »

Offline Quinten

  • Sr. Member
  • ****
  • Posts: 273
  • Karma: +2/-0
Re: HOWTO: Event logging to database
« Reply #28 on: September 12, 2013, 04:48:52 pm »
Whatever you can notify on, can be logged.  I believe temperature devices allow logging of absolute values, rising above/dropping below thresholds...
VeraLite - UI7 1.7.947 | 8x Fibaro FGD211 | 2x Fibaro FGS221 | 1x Fibaro FGBS321 | 3x Fibaro FGRGBW | 1x Everspring SP814 | 2x Everspring AN157-3 | 2x Heatmiser PRT-TS WiFi | 1x Logitech Harmony Hub | 1x Philips LivingColour

Offline parkerc

  • Beta Testers
  • Sr. Hero Member
  • *****
  • Posts: 2479
  • Karma: +35/-48
  • Life Moves Pretty Fast....
Re: HOWTO: Event logging to database
« Reply #29 on: September 12, 2013, 05:51:27 pm »
Whatever you can notify on, can be logged.  I believe temperature devices allow logging of absolute values, rising above/dropping below thresholds...

Yep, I think I know what you mean, but that suggests I would have to create a notification for each temperature value.  Considering the URL you shared early to manually create an entry in the table, suggests you could (not that i know how) do a Luup.variable_watch on any change to variable (e.g temp) and post it to the table ?