From: arjen Date: Wed, 12 Dec 2007 09:06:21 +0000 (+0000) Subject: Added a new script gcm_maintenance.php to cleanup the database X-Git-Tag: V0_0_12~2 X-Git-Url: http://www.andromeda.nl/gitweb/?p=gnucomo.git;a=commitdiff_plain;h=c102a26280445e42bb22c41d2d6ae932c70ffd90 Added a new script gcm_maintenance.php to cleanup the database and check referential integrity. Purging old log entries is removed from the gcm_daemon script. --- diff --git a/src/gcm_daemon/classes/gnucomo.process_log.php b/src/gcm_daemon/classes/gnucomo.process_log.php index 78a8d9d..f00d232 100644 --- a/src/gcm_daemon/classes/gnucomo.process_log.php +++ b/src/gcm_daemon/classes/gnucomo.process_log.php @@ -1,5 +1,9 @@ fetch_row() == "TRUE") do { - if ($purge_date != "") - { - purge_old_logs($purge_date); - } - echo "Processing logs...\n"; process_log (); service_check(); @@ -253,53 +246,6 @@ do syslog (LOG_INFO, "gcm_daemon ended nicely"); -function purge_old_logs($purge_date) -{ - global $dbms; - - /* - * Make a temporary table with the logids of the old log entries - * We don't want to repeat a selection on the large log table itself. - */ - - echo "Purging log entries before $purge_date\n"; - - $dbms->query("CREATE TABLE gcm_deamon_old_log AS SELECT logid FROM log WHERE timestamp < '$purge_date'"); - $dbms->query("SELECT logid FROM gcm_deamon_old_log"); - echo $dbms->num_rows() . " log entries found.\n"; - $r = $dbms->query("select notificationid from log_notification where logid in - (select logid from gcm_deamon_old_log) group by notificationid"); - echo "Notifications that may be affected:\n"; - $notifications = array(); - for ($i=0; $i < $dbms->num_rows(); $i++) - { - $notif = $dbms->fetch_object($r, $i); - $notifications[] = $notif->notificationid; - echo $notif->notificationid . "\n"; - } - $dbms->query("delete from log_notification where logid in - (select logid from gcm_deamon_old_log)"); - - // Clean up any notifications that have no more logs left - foreach ($notifications as $notif) - { - $c = $dbms->fetch_object($dbms->query("select count(*) from log_notification where notificationid=$notif"), 0); - echo "Notification $notif has " . $c->count . " log entries left.\n"; - if ($c->count == 0) - { - echo "Cleaning up notification $notif.\n"; - $dbms->query("delete from action_user where notificationid=$notif"); - $dbms->query("delete from notification where notificationid=$notif"); - } - } - - $dbms->query("delete from log where logid in - (select logid from gcm_deamon_old_log)"); - - $dbms->query("drop table gcm_deamon_old_log"); -} - - function process_log () { @@ -372,66 +318,6 @@ function process_log () $local_object_os = ""; $local_object_os_version = ""; -/* - while ($local_counter < $dbms->num_rows()) - { - - $local_return_row = $dbms->fetch_row(); - if ($local_return_row == 'TRUE') - { - // Work on active rows - $local_log_id = $dbms->db_result_row[0]; - - $local_sql_findobject = "SELECT os, os_version FROM object - WHERE objectid = '".$dbms->db_result_row[1]."'"; - $local_findobject_db->query($local_sql_findobject); - $local_findobject_result = $local_findobject_db->fetch_row(); - if ($local_findobject_result == 'TRUE') - { - - // Now work on the OS again - $local_object_os = $local_findobject_db->db_result_row[0]; - if ($local_object_os == "") - { - $local_object_os = "Linux"; - $local_object_os_version = "Unknown assuming Linux"; - } - else - { - $local_object_os_version = $local_findobject_db->db_result_row[1]; - } - } - - switch (strtolower($local_object_os)) - { - case "linux": - $local_process_return = linux_log (); - break; - default: - syslog (LOG_INFO, "Couldn't find suitable OS for processing the logline"); - break; - } - - if ($local_process_return != 'TRUE') - { - $local_process_return = 'FALSE'; - } - - } - else - { - - break; - - } - $local_counter++; - } -*/ - - // Check for spam and other abuses. - - // abuse_check($last_log); - match_log_patterns($last_log); // Register that the logrecords have been processed. @@ -538,84 +424,6 @@ function GatherStatistics($objectid) UpdateStatistic($objectid, 'logs', $cnt->count); } -// Check for spam and other abuses in the log_adv tables. - -function abuse_check($logstart) -{ -return; // This function is obsolete - global $dbms; - - // notification: 'abuses exceeded'. - - $noqueue_res = $dbms->query("select logid, source_ip from log_adv_daemon_email - where event='NOQUEUE' and logid > " . $logstart); - echo "NOQUEUE abuses:\n\n"; - for ($row = 0; $row < $dbms->num_rows($noqueue_res); $row++) - { - $noqueue = $dbms->fetch_object($noqueue_res, $row); - if ($noqueue->source_ip != '') - { - $obj = $dbms->fetch_object( - $dbms->query("SELECT objectid FROM log WHERE logid = '" . $noqueue->logid . "'"),0); - record_abuse(0, $obj->objectid, $noqueue->source_ip, 2); - - // TODO: Create notification - } - } - $dbms->Free($noqueue_res); - - $noqueue_res = $dbms->query("select logid, source_ip, relay from log_adv_daemon_email - where event='SPAM' and logid > " . $logstart); - echo "SPAM abuses:\n\n"; - for ($row = 0; $row < $dbms->num_rows($noqueue_res); $row++) - { - $noqueue = $dbms->fetch_object($noqueue_res, $row); - $source = $noqueue->source_ip; - if ($source == '') - { - $source = $noqueue->relay; - } - if ($source != '') - { - $obj = $dbms->fetch_object( - $dbms->query("SELECT objectid FROM log WHERE logid = '" . $noqueue->logid . "'"),0); - record_abuse(0, $obj->objectid, $source, 1); - - // TODO: Create notification - } - } - $dbms->Free($noqueue_res); - - echo "HTTP abuses:\n\n"; - $abuse_res = $dbms->query("select logid, objectid, rawdata from log - where servicecode='httpd' and logid > " . $logstart); - for ($row = 0; $row < $dbms->num_rows($abuse_res); $row++) - { - $source = ''; - $abuse = $dbms->fetch_object($abuse_res, $row); - if (ereg("\[error\] \[client ([0-9.]+)\] request failed: URI too long", $abuse->rawdata, $parts)) - { - echo $abuse->rawdata . "\n"; - echo "Abuse on object " . $abuse->objectid . " from IP address " . $parts[1] . "\n"; - $source = $parts[1]; - } - if (ereg("\[error\] \[client ([0-9.]+)\] File does not exist: .+/MSADC", - $abuse->rawdata, $parts)) - { - echo $abuse->rawdata . "\n"; - echo "Abuse on object " . $abuse->objectid . " from IP address " . $parts[1] . "\n"; - $source = $parts[1]; - } - if ($source != '') - { - record_abuse(0, $abuse->objectid, $source, 2); - - // TODO: Create notification - } - } - $dbms->Free($abuse_res); -} - function match_log_patterns($logstart) { global $dbms; @@ -674,6 +482,9 @@ function match_log_patterns($logstart) break; case "abuse": + //echo "Recording abuse for address ", $srv_pat->argument, "\n Log entry:\n "; + //echo $logentry->rawdata, "\n Pattern:\n ", $srv_pat->pattern, "\n\n"; + if (record_abuse($logentry->logid, $logentry->objectid, $srv_pat->argument, 1) >= 32) { $source_ip = $srv_pat->argument; @@ -687,7 +498,7 @@ function match_log_patterns($logstart) // Add log entries from previously detected abuses - echo " Add log entries from previously detected abuses"; + echo " Add log entries from previously detected abuses\n"; $abuses = $dbms->query("SELECT logid FROM log_abuse WHERE objectid = '" . $logentry->objectid . "' AND source = '$source_ip'"); for ($abusenr = 0; $abusenr < $dbms->num_rows($abuses); $abusenr++) @@ -704,7 +515,7 @@ function match_log_patterns($logstart) } if (isset($notifications[$logentry->objectid][$notif][$source_ip])) { - echo "Notification $notif for object " . $logentry->objectid . " already created.\n"; + //echo "Notification $notif for object " . $logentry->objectid . " already created.\n"; $insertion = "INSERT INTO log_notification (notificationid, logid) VALUES ('"; $insertion .= $notifications[$logentry->objectid][$notif][$source_ip] . "', '"; $insertion .= $logentry->logid . "')"; diff --git a/src/gcm_daemon/gcm_maintenance.php b/src/gcm_daemon/gcm_maintenance.php new file mode 100755 index 0000000..0bbb56a --- /dev/null +++ b/src/gcm_daemon/gcm_maintenance.php @@ -0,0 +1,311 @@ +#!/usr/bin/php +read($project_name)) +{ + echo "Can not read Gnucomo configuration file for $project_name.\n"; + exit(); +} + +openlog("gnucomo", LOG_PID, LOG_DAEMON); + +//Open an connection to the database +$dbms_type = $class_settings->find_parameter("database", "type"); +$dbms_host = $class_settings->find_parameter("database", "host"); +$dbms_name = $class_settings->find_parameter("database", "name"); +$dbms_user = $class_settings->find_parameter("gcm_daemon", "user"); +$dbms_password = $class_settings->find_parameter("gcm_daemon", "password"); + +db_select($dbms_type); +$dbms = new db(); +$dbms->db_host = $dbms_host; +$dbms->db_name = $dbms_name; +$dbms->db_user = $dbms_user; +$dbms->db_password = $dbms_password; +$dbms->db_connect($class_settings->database()); + +if ($dbms->have_db_connection() == "FALSE") +{ + exit ("Database connection failed."); +} + + if ($purge_date != "") + { + purge_old_logs($purge_date); + } + + // Check the references from the abuse list to the logs + + + $abuse_result = $dbms->query("select source, nr_abuses, masklen(source) from object_abuse + where status='dropped' and masklen(source)=32 and objectid=1 order by source"); + for ($ab = 0; $ab < $dbms->num_rows($abuse_result); $ab++) + { + $logentries = 0; + $abuse = $dbms->fetch_object($abuse_result, $ab); + $nr_abuses = $abuse->nr_abuses; + $log_res = $dbms->query("select logid from log_abuse where source='" . $abuse->source . "' and objectid=1"); + + if ($dbms->num_rows($log_res) != $nr_abuses) + { + echo "Mismatch in nr of abuses. Corrected.\n"; + $dbms->query("update object_abuse set nr_abuses=" . $dbms->num_rows($log_res) . + " where source='" . $abuse->source . "' and objectid=1"); + } + if ($dbms->num_rows($log_res) == 0) + { + echo " Removing ", $abuse->source, "\n"; + $dbms->query("delete from object_abuse where source='". $abuse->source . "' and objectid=1"); + } + else + { + $logref_res = $dbms->query("select logid from log where logid in + (select logid from log_abuse where source='" . $abuse->source . "' and objectid=1)"); + $logentries = $dbms->num_rows($logref_res); + if ($logentries == 0) + { + echo "All references to log entries are lost. Purging log_abuse table.\n"; + $dbms->query("delete from log_abuse where source='" . $abuse->source . "' and objectid=1"); + + } + } + echo $abuse->source . "\t$nr_abuses\t" . $dbms->num_rows($log_res) . "\t" . $logentries . "\n"; + } + + // Gather the statistics for each object + + $obj_result = $dbms->query("SELECT objectid FROM object"); + for ($obj = 0; $obj < $dbms->num_rows($obj_result); $obj++) + { + $object = $dbms->fetch_object($obj_result, $obj); + echo "Gathering statistics for object " . $object->objectid . "\n"; + GatherStatistics($object->objectid); + } + + +function purge_old_logs($purge_date) +{ + global $dbms; + + /* + * Make a temporary table with the logids of the old log entries + * We don't want to repeat a selection on the large log table itself. + */ + + echo "Purging log entries before $purge_date\n"; + + $dbms->query("CREATE TABLE gcm_deamon_old_log AS SELECT logid FROM log WHERE timestamp < '$purge_date'"); + $dbms->query("SELECT logid FROM gcm_deamon_old_log"); + echo $dbms->num_rows() . " log entries found.\n"; + + // Clean up notifications that are left without reference to the log. + + $r = $dbms->query("select notificationid from log_notification where logid in + (select logid from gcm_deamon_old_log) group by notificationid"); + echo "Notifications that may be affected:\n"; + $notifications = array(); + for ($i=0; $i < $dbms->num_rows(); $i++) + { + $notif = $dbms->fetch_object($r, $i); + $notifications[] = $notif->notificationid; + echo $notif->notificationid . "\n"; + } + $dbms->query("delete from log_notification where logid in + (select logid from gcm_deamon_old_log)"); + + // Clean up any notifications that have no more logs left + foreach ($notifications as $notif) + { + $c = $dbms->fetch_object($dbms->query("select count(*) from log_notification where notificationid=$notif"), 0); + echo "Notification $notif has " . $c->count . " log entries left.\n"; + if ($c->count == 0) + { + echo "Cleaning up notification $notif.\n"; + $dbms->query("delete from action_user where notificationid=$notif"); + $dbms->query("delete from notification where notificationid=$notif"); + } + } + + // Clean up abuses that are left without reference to the log. + + $r = $dbms->query("select source from log_abuse where logid in + (select logid from gcm_deamon_old_log) group by source"); + echo "Abusing IP addresses that may be affected:\n"; + $abusers = array(); + for ($i = 0; $i < $dbms->num_rows(); $i++) + { + $ab = $dbms->fetch_object($r, $i); + $abusers[] = $ab->source; + echo $ab->source . "\n"; + } + $dbms->query("delete from log_abuse where logid in + (select logid from gcm_deamon_old_log)"); + + foreach ($abusers as $src) + { + $c = $dbms->fetch_object($dbms->query("select count(*) from log_abuse where source='$src'"), 0); + echo "IP address $src has " . $c->count . " log entries left.\n"; + if ($c->count == 0) + { + echo "Cleaning up abusing address $src.\n"; + $dbms->query("delete from object_abuse where source='$src'"); + } + } + + $dbms->query("delete from log where logid in + (select logid from gcm_deamon_old_log)"); + + $dbms->query("drop table gcm_deamon_old_log"); +} + + +/* + * Update a single statistic for some object. + * If it does not yet exist, it will be created. + */ + +function UpdateStatistic($objectid, $name, $value) +{ + global $dbms; + + $result = $dbms->query("SELECT objectid FROM object_statistics WHERE + objectid='$objectid' AND statname='$name'"); + if ($dbms->num_rows() == 0) + { + $dbms->query("INSERT INTO object_statistics VALUES + ('$objectid', '$name', '$value')"); + } + else + { + $dbms->query("UPDATE object_statistics SET statvalue='$value' WHERE + statname='$name' AND objectid='$objectid'"); + } +} + +/* + * Gather the statistics for a single object ($objectid). + * We count the number of parameters, removed parameters, notifications + * closed notifications and log entries. The totals of these are + * maintained in a separate table: object_statistics. + */ + +function GatherStatistics($objectid) +{ + global $dbms; + + // Gather statistics on parameters + + $r = $dbms->query("SELECT paramid FROM parameter WHERE objectid=CAST('" + . $objectid . "' AS BIGINT)"); + $nr_parameters = $dbms->num_rows($r); + + $removed_parameters = 0; + for ($p = 0; $p < $nr_parameters; $p++) + { + $param = pg_fetch_object($r, $p); + $qry ="select change_nature from history where paramid= CAST('"; + $qry .= $param->paramid . "' AS BIGINT) order by modified desc"; + $rhist = $dbms->query($qry); + if ($dbms->num_rows($rhist) == 0) + { + echo "ERROR: No history for parameter id " . $param->paramid . "\n"; + } + else + { + $hist = $dbms->fetch_object($rhist, 0); + if ($hist->change_nature == "REMOVED") + { + $removed_parameters++; + } + } + } + + UpdateStatistic($objectid, 'parameters', $nr_parameters); + UpdateStatistic($objectid, 'removed_parameters', $removed_parameters); + + // Gather statistics on notifications + + $r = $dbms->query("SELECT count(notificationid) FROM notification WHERE + objectid = CAST('" . $objectid . "' AS BIGINT)"); + $cnt = $dbms->fetch_object($r, 0); + UpdateStatistic($objectid, 'notifications', $cnt->count); + + $r = $dbms->query("SELECT count(notificationid) FROM notification WHERE + objectid = CAST('" . $objectid . "' AS BIGINT) AND statuscode ='cls'"); + $cnt = $dbms->fetch_object($r, 0); + UpdateStatistic($objectid, 'closed_notifications', $cnt->count); + + // Gather statistics on log entries + + $r = $dbms->query("SELECT count(logid) FROM log WHERE + objectid = CAST('" . $objectid . "' AS BIGINT)"); + $cnt = $dbms->fetch_object($r, 0); + UpdateStatistic($objectid, 'logs', $cnt->count); +}