Domoticz

Open source Home Automation System
https://forum.domoticz.com/

Auto clear log (or limit) of alert sensor.

https://forum.domoticz.com/viewtopic.php?t=10741

Page 1 of 1

Auto clear log (or limit) of alert sensor.

Posted: Thursday 25 February 2016 13:35
by YellowSky
Hi everyone,
i use some alert sensor to inform me:
The level of water inside tank
The rain alert from viewtopic.php?f=38&t=5903
...

But one problem is the size of log.

I would like to know if it is possible to clear log automatically. Or limit entry number of these log.

Thank you

Re: Auto clear log (or limit) of alert sensor.

Posted: Friday 24 February 2017 14:18
by YellowSky
I always look after this json command.
Is there anybody to help me?

Re: Auto clear log (or limit) of alert sensor.

Posted: Friday 24 February 2017 15:02
by Egregius
Why not delete it from database directly?

Re: Auto clear log (or limit) of alert sensor.

Posted: Friday 24 February 2017 17:07
by YellowSky
Of course it is an alternative (how to do that with domoticz running?) but the most common use for me is to perform this action each dayfor selected "sensor"

Re: Auto clear log (or limit) of alert sensor.

Posted: Friday 24 February 2017 17:18
by Egregius
I run this script every night:

Code: Select all

<?php
$db = new SQLite3('/volume1/@appstore/domoticz/var/domoticz.db');
$tables = array( 'LightingLog',
                'MultiMeter',
                'MultiMeter_Calendar',
                'Meter',
                'Meter_Calendar',
                'Percentage',
                'Percentage_Calendar',
                'Rain',
                'Rain_Calendar',
                'Temperature',
                'Temperature_Calendar',
                'UV',
                'UV_Calendar',
                'Wind',
                'Wind_Calendar');
foreach($tables as $table){
    $query=$db->exec("DELETE FROM $table WHERE DeviceRowID not in (select ID from DeviceStatus where Used = 1)");
    if ($query){
        $rows = $db->changes();
        if($rows>0)
            echo $rows." rows removed from $table<br/>";
    }
}
$query=$db->exec("DELETE FROM LightingLog WHERE DeviceRowID in (6,9,10)");
    if ($query){
        $rows = $db->changes();
        if($rows>0)
            echo $rows." rows removed from LightingLog<br/>";
    }
$sql = 'VACUUM;';
if(!$result = $db->exec($sql))
    die('There was an error running the query [' . $db->error . ']'); 
First part: remove all data from devices that are not used. Yes, also unused devices store data in the database.
Second part: remove all data from devices with idx 6,9 or 10 as I don't care about them.
Last part: vacuum the database to free unused space.

This script runs already a year, never had a problem with it.

Re: Auto clear log (or limit) of alert sensor.

Posted: Friday 24 February 2017 18:09
by YellowSky
Wow, thank you a lot.
I guess domoticz has to be stop before to perform that?

Re: Auto clear log (or limit) of alert sensor.

Posted: Friday 24 February 2017 20:35
by Egregius
I don't stop it. :twisted:

Re: Auto clear log (or limit) of alert sensor.

Posted: Monday 06 March 2017 21:08
by YellowSky
Ok, thanks.
In my case, it does not work (unable to open it) (i think because php don't have the permission to access to the domoticz.db :-( )

Re: Auto clear log (or limit) of alert sensor.

Posted: Thursday 16 March 2017 9:04
by mayyam
Egregius wrote:I run this script every night:

Code: Select all

<?php
$db = new SQLite3('/volume1/@appstore/domoticz/var/domoticz.db');
$tables = array( 'LightingLog',
                'MultiMeter',
                'MultiMeter_Calendar',
                'Meter',
                'Meter_Calendar',
                'Percentage',
                'Percentage_Calendar',
                'Rain',
                'Rain_Calendar',
                'Temperature',
                'Temperature_Calendar',
                'UV',
                'UV_Calendar',
                'Wind',
                'Wind_Calendar');
foreach($tables as $table){
    $query=$db->exec("DELETE FROM $table WHERE DeviceRowID not in (select ID from DeviceStatus where Used = 1)");
    if ($query){
        $rows = $db->changes();
        if($rows>0)
            echo $rows." rows removed from $table<br/>";
    }
}
$query=$db->exec("DELETE FROM LightingLog WHERE DeviceRowID in (6,9,10)");
    if ($query){
        $rows = $db->changes();
        if($rows>0)
            echo $rows." rows removed from LightingLog<br/>";
    }
$sql = 'VACUUM;';
if(!$result = $db->exec($sql))
    die('There was an error running the query [' . $db->error . ']');
First part: remove all data from devices that are not used. Yes, also unused devices store data in the database.
Second part: remove all data from devices with idx 6,9 or 10 as I don't care about them.
Last part: vacuum the database to free unused space.

This script runs already a year, never had a problem with it.
I'm getting errors:
pi@domoticz:~ $ /home/pi/domoticz/scripts/php/clean_log_database.php
/home/pi/domoticz/scripts/php/clean_log_database.php: line 1: ?php: No such file or directory
/home/pi/domoticz/scripts/php/clean_log_database.php: line 2: syntax error near unexpected token `('
/home/pi/domoticz/scripts/php/clean_log_database.php: line 2: `$db = new SQLite3('/home/pi/domoticz/domoticz.db');'

Re: Auto clear log (or limit) of alert sensor.

Posted: Thursday 16 March 2017 11:24
by Egregius
Are you sure you have php and php-cli installed?

There are different ways to run a php script.

a) script starts with just <?php: host it on a webserver and use curl.
b) start the script by /usr/bin/php /path/to/script.php
c) start the script with #!/usr/bin/php before the <?php

Re: Auto clear log (or limit) of alert sensor.

Posted: Thursday 16 March 2017 12:24
by mayyam
Thanks for the asnwer.
Yes, i have the apache and php installed:
pi@domoticz:~ $ apachectl -V
AH00558: apache2: Could not reliably determine the server's fully qualified domain name, using 127.0.1.1. Set the 'ServerName' directive globally to suppress this message
Server version: Apache/2.4.10 (Raspbian)
Server built: Sep 17 2016 16:40:43
Server's Module Magic Number: 20120211:37
Server loaded: APR 1.5.1, APR-UTIL 1.5.4
Compiled using: APR 1.5.1, APR-UTIL 1.5.4
Architecture: 32-bit
Server MPM: prefork
threaded: no
forked: yes (variable process count)
Server compiled with....
-D APR_HAS_SENDFILE
-D APR_HAS_MMAP
-D APR_HAVE_IPV6 (IPv4-mapped addresses enabled)
-D APR_USE_SYSVSEM_SERIALIZE
-D APR_USE_PTHREAD_SERIALIZE
-D SINGLE_LISTEN_UNSERIALIZED_ACCEPT
-D APR_HAS_OTHER_CHILD
-D AP_HAVE_RELIABLE_PIPED_LOGS
-D DYNAMIC_MODULE_LIMIT=256
-D HTTPD_ROOT="/etc/apache2"
-D SUEXEC_BIN="/usr/lib/apache2/suexec"
-D DEFAULT_PIDLOG="/var/run/apache2.pid"
-D DEFAULT_SCOREBOARD="logs/apache_runtime_status"
-D DEFAULT_ERRORLOG="logs/error_log"
-D AP_TYPES_CONFIG_FILE="mime.types"
-D SERVER_CONFIG_FILE="apache2.conf"
pi@domoticz:~ $ php -v
PHP 5.6.29-0+deb8u1 (cli) (built: Dec 17 2016 06:04:43)
Copyright (c) 1997-2016 The PHP Group
Zend Engine v2.6.0, Copyright (c) 1998-2016 Zend Technologies
with Zend OPcache v7.0.6-dev, Copyright (c) 1999-2016, by Zend Technologies
pi@domoticz:~/domoticz $ sudo apt-get install php5-cli
Reading package lists... Done
Building dependency tree
Reading state information... Done
php5-cli is already the newest version.
php5-cli set to manually installed.
pi@domoticz:~ $ sqlite3
SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

Using second method:
pi@domoticz:~ $ sudo /usr/bin/php /home/pi/domoticz/scripts/php/clean_log_database.php
PHP Fatal error: Class 'SQLite3' not found in /home/pi/domoticz/scripts/php/clean_log_database.php on line 2
Using Third method (where first line= #!/usr/bin/php<?php):
sudo /home/pi/domoticz/scripts/php/clean_log_database.php
sudo: unable to execute /home/pi/domoticz/scripts/php/clean_log_database.php: No such file or directory
Using Third method (where first line= #!/usr/bin/php <?php):
pi@domoticz:~ $ sudo /home/pi/domoticz/scripts/php/clean_log_database.php
Could not open input file: <?php
First method i don't understand :( - just host on the webserwer results in blank page.
And there is the error in apche error.log:
[Thu Mar 16 12:36:05.184927 2017] [:error] [pid 745] [client 192.168.12.20:59203] PHP Fatal error: Class 'SQLite3' not found in /var/www/html/clean_log_database.php on line 2
And Domoticz directory looks like:
pi@domoticz:~/domoticz/scripts/php $ cd /home/pi/domoticz
pi@domoticz:~/domoticz $ ls
backups Config domocookie.txt domoticz domoticz.db domoticz.db-shm domoticz.db-wal domoticz.sh History.txt License.txt python-broadlink scripts server_cert.pem tmp updatebeta updaterelease www

EDIT:
The problem was:
pi@domoticz:~/domoticz $ sudo apache2ctl configtest
AH00558: apache2: Could not reliably determine the server's fully qualified domain name, using 127.0.1.1. Set the 'ServerName' directive globally to suppress this message
Syntax OK
I had to:
sudo nano /etc/apache2/apache2.conf
insert the line - ServerName myserver.mydomain.com - with my name of the server.
Then - apachectl configtest
pi@domoticz:~/domoticz $ apachectl configtest
Syntax OK
Then - sudo service apache2 restart
Then - sudo /usr/bin/php /home/pi/domoticz/scripts/php/clean_log_database.php
102 rows removed from LightingLog<br/>92 rows removed from MultiMeter_Calendar<br/>91 rows removed from Meter<br/>288 rows removed from Rain<br/>58 rows removed from Rain_Calendar<br/>92 rows removed from UV<br/>55 rows removed from UV_Calendar<br/>32552 rows removed from LightingLog<br/>pi@domoticz:~/domoticz $
Now its working :)
Bless You @Egregius

Re: Auto clear log (or limit) of alert sensor.

Posted: Tuesday 17 August 2021 14:50
by waltervl
What is wrong with the log history settings? Better use these than manual srcew up your database..... ;)
https://www.domoticz.com/wiki/Applicati ... og_History

Re: Auto clear log (or limit) of alert sensor.

Posted: Tuesday 17 August 2021 21:45
by waltervl
In what table are those camera log rows? If they are in Lightinglog then they will be purged after the light/switch setting days, default 30 days. So you can decrease that setting and check what changed.
All times are UTC+01:00
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited