function syslog_execute_update($syslog_exists, $options)
{
global $config;
if (isset($options['cancel'])) {
header('Location:' . $config['url_path'] . 'plugins.php?mode=uninstall&id=syslog&uninstall&uninstall_method=all');
exit;
} elseif (isset($options['return'])) {
db_execute("DELETE FROM plugin_config WHERE directory='syslog'");
db_execute("DELETE FROM plugin_realms WHERE plugin='syslog'");
db_execute("DELETE FROM plugin_db_changes WHERE plugin='syslog'");
db_execute("DELETE FROM plugin_hooks WHERE name='syslog'");
} elseif (isset($options["upgrade_type"])) {
if ($options["upgrade_type"] == "truncate") {
syslog_setup_table_new($options);
}
} else {
syslog_setup_table_new($options);
}
db_execute("REPLACE INTO settings SET name='syslog_retention', value='" . $options['days'] . "'");
}
function syslog_upgrade_pre_oneoh_tables($options = false, $isbackground = false)
{
global $config, $cnn_id, $syslog_levels, $database_default, $syslog_upgrade;
include dirname(__FILE__) . "/config.php";
$syslog_levels = array(1 => 'emerg', 2 => 'crit', 3 => 'alert', 4 => 'err', 5 => 'warn', 6 => 'notice', 7 => 'info', 8 => 'debug', 9 => 'other');
if ($isbackground) {
$table = 'syslog_pre_upgrade';
} else {
$table = 'syslog';
}
/* validate some simple information */
$mysqlVersion = syslog_get_mysql_version("syslog");
$truncate = isset($options["upgrade_type"]) && $options["upgrade_type"] == "truncate" ? true : false;
$upgrade_type = isset($options["upgrade_type"]) ? $options["upgrade_type"] : "inline";
$engine = isset($options["engine"]) && $options["engine"] == "innodb" ? "InnoDB" : "MyISAM";
$partitioned = isset($options["db_type"]) && $options["db_type"] == "part" ? true : false;
$syslogexists = sizeof(syslog_db_fetch_row("SHOW TABLES FROM `" . $syslogdb_default . "` LIKE '{$table}'"));
/* disable collection for a bit */
set_config_option('syslog_enabled', '');
if ($upgrade_type == "truncate") {
return;
}
if ($upgrade_type == "inline" || $isbackground) {
syslog_setup_table_new($options);
api_plugin_register_realm('syslog', 'syslog.php', 'Plugin -> Syslog User', 1);
api_plugin_register_realm('syslog', 'syslog_alerts.php,syslog_removal.php,syslog_reports.php', 'Plugin -> Syslog Administration', 1);
/* get the realm id's and change from old to new */
$user = db_fetch_cell("SELECT id FROM plugin_realms WHERE file='syslog.php'") + 100;
$admin = db_fetch_cell("SELECT id FROM plugin_realms WHERE file='syslog_alerts.php'") + 100;
if ($user > 100) {
$users = db_fetch_assoc("SELECT user_id FROM user_auth_realm WHERE realm_id=37");
if (sizeof($users)) {
foreach ($users as $u) {
db_execute("INSERT INTO user_auth_realm\n\t\t\t\t\t(realm_id, user_id) VALUES ({$user}, " . $u["user_id"] . ")\n\t\t\t\t\tON DUPLICATE KEY UPDATE realm_id=VALUES(realm_id)");
db_execute("DELETE FROM user_auth_realm\n\t\t\t\t\tWHERE user_id=" . $u["user_id"] . "\n\t\t\t\t\tAND realm_id=37");
}
}
}
if ($admin > 100) {
$admins = db_fetch_assoc("SELECT user_id FROM user_auth_realm WHERE realm_id=38");
if (sizeof($admins)) {
foreach ($admins as $user) {
db_execute("INSERT INTO user_auth_realm\n\t\t\t\t\t(realm_id, user_id) VALUES ({$admin}, " . $user["user_id"] . ")\n\t\t\t\t\tON DUPLICATE KEY UPDATE realm_id=VALUES(realm_id)");
db_execute("DELETE FROM user_auth_realm\n\t\t\t\t\tWHERE user_id=" . $user["user_id"] . "\n\t\t\t\t\tAND realm_id=38");
}
}
}
/* get the database table names */
$rows = syslog_db_fetch_assoc("SHOW TABLES FROM `" . $syslogdb_default . "`");
if (sizeof($rows)) {
foreach ($rows as $row) {
$tables[] = $row["Tables_in_" . $syslogdb_default];
}
}
/* create the reports table */
syslog_db_execute("CREATE TABLE IF NOT EXISTS `" . $syslogdb_default . "`.`syslog_logs` (\n\t\t\talert_id integer unsigned not null default '0',\n\t\t\tlogseq bigint unsigned NOT NULL,\n\t\t\tlogtime TIMESTAMP NOT NULL default '0000-00-00 00:00:00',\n\t\t\tlogmsg " . ($mysqlVersion > 5 ? "varchar(1024)" : "text") . " default NULL,\n\t\t\thost varchar(32) default NULL,\n\t\t\tfacility varchar(10) default NULL,\n\t\t\tpriority varchar(10) default NULL,\n\t\t\tcount integer unsigned NOT NULL default '0',\n\t\t\thtml blob default NULL,\n\t\t\tseq bigint unsigned NOT NULL auto_increment,\n\t\t\tPRIMARY KEY (seq),\n\t\t\tKEY logseq (logseq),\n\t\t\tKEY alert_id (alert_id),\n\t\t\tKEY host (host),\n\t\t\tKEY seq (seq),\n\t\t\tKEY logtime (logtime),\n\t\t\tKEY priority (priority),\n\t\t\tKEY facility (facility)) ENGINE={$engine};");
/* create the soft removal table */
syslog_db_execute("CREATE TABLE IF NOT EXISTS `" . $syslogdb_default . "`.`syslog_host_facilities` (\n\t\t\t`host_id` int(10) unsigned NOT NULL,\n\t\t\t`facility_id` int(10) unsigned NOT NULL,\n\t\t\t`last_updated` TIMESTAMP NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,\n\t\t\tPRIMARY KEY (`host_id`,`facility_id`)) ENGINE={$engine};");
/* create the host reference table */
syslog_db_execute("CREATE TABLE IF NOT EXISTS `" . $syslogdb_default . "`.`syslog_hosts` (\n\t\t\t`host_id` int(10) unsigned NOT NULL auto_increment,\n\t\t\t`host` VARCHAR(128) NOT NULL,\n\t\t\t`last_updated` TIMESTAMP NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,\n\t\t\tPRIMARY KEY (`host`),\n\t\t\tKEY host_id (`host_id`),\n\t\t\tKEY last_updated (`last_updated`)) ENGINE={$engine}\n\t\t\tCOMMENT='Contains all hosts currently in the syslog table'");
/* check upgrade of syslog_alert */
$sql = "DESCRIBE `" . $syslogdb_default . "`.`syslog_alert`";
$columns = array();
$array = syslog_db_fetch_assoc($sql);
if (sizeof($array)) {
foreach ($array as $row) {
$columns[] = $row["Field"];
}
}
if (!in_array("enabled", $columns)) {
syslog_db_execute("ALTER TABLE `" . $syslogdb_default . "`.`syslog_alert` MODIFY COLUMN message varchar(128) DEFAULT NULL, ADD COLUMN enabled CHAR(2) DEFAULT 'on' AFTER type;");
}
if (!in_array("method", $columns)) {
syslog_db_execute("ALTER TABLE `" . $syslogdb_default . "`.`syslog_alert` ADD COLUMN method int(10) unsigned NOT NULL default '0' AFTER name");
syslog_db_execute("ALTER TABLE `" . $syslogdb_default . "`.`syslog_alert` ADD COLUMN num int(10) unsigned NOT NULL default '1' AFTER method");
syslog_db_execute("ALTER TABLE `" . $syslogdb_default . "`.`syslog_alert` ADD COLUMN severity INTEGER UNSIGNED NOT NULL default '0' AFTER name");
}
if (!in_array("command", $columns)) {
syslog_db_execute("ALTER TABLE `" . $syslogdb_default . "`.`syslog_alert` ADD COLUMN command varchar(255) DEFAULT NULL AFTER email;");
}
/* check upgrade of syslog_alert */
$sql = "DESCRIBE `" . $syslogdb_default . "`.`syslog_remove`";
$columns = array();
$array = syslog_db_fetch_assoc($sql);
if (sizeof($array)) {
foreach ($array as $row) {
$columns[] = $row["Field"];
}
}
if (!in_array("enabled", $columns)) {
syslog_db_execute("ALTER TABLE `" . $syslogdb_default . "`.`syslog_remove` MODIFY COLUMN message varchar(128) DEFAULT NULL, ADD COLUMN enabled CHAR(2) DEFAULT 'on' AFTER type;");
}
if (!in_array("method", $columns)) {
syslog_db_execute("ALTER TABLE `" . $syslogdb_default . "`.`syslog_remove` ADD COLUMN method CHAR(5) DEFAULT 'del' AFTER enabled;");
}
syslog_db_execute("DROP TABLE IF EXISTS `" . $syslogdb_default . "`.`syslog_hosts`");
syslog_db_execute("CREATE TABLE IF NOT EXISTS `" . $syslogdb_default . "`.`syslog_hosts` (\n\t\t\t`host_id` int(10) unsigned NOT NULL auto_increment,\n\t\t\t`host` VARCHAR(128) NOT NULL,\n\t\t\t`last_updated` TIMESTAMP NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,\n\t\t\tPRIMARY KEY (`host`),\n\t\t\tKEY host_id (`host_id`),\n\t\t\tKEY last_updated (`last_updated`)) TYPE={$engine}\n\t\t\tCOMMENT='Contains all hosts currently in the syslog table'");
syslog_db_execute("DROP TABLE IF EXISTS `" . $syslogdb_default . "`.`syslog_facilities`");
syslog_db_execute("CREATE TABLE IF NOT EXISTS `" . $syslogdb_default . "`.`syslog_facilities` (\n\t\t\t`facility_id` int(10) unsigned NOT NULL auto_increment,\n\t\t\t`facility` varchar(10) NOT NULL,\n\t\t\t`last_updated` TIMESTAMP NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,\n\t\t\tPRIMARY KEY (`facility`),\n\t\t\tKEY facility_id (`facility_id`)) ENGINE={$engine};");
syslog_db_execute("DROP TABLE IF EXISTS `" . $syslogdb_default . "`.`syslog_priorities`");
syslog_db_execute("CREATE TABLE IF NOT EXISTS `" . $syslogdb_default . "`.`syslog_priorities` (\n\t\t\t`priority_id` int(10) unsigned NOT NULL auto_increment,\n\t\t\t`priority` varchar(10) NOT NULL,\n\t\t\t`last_updated` TIMESTAMP NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,\n\t\t\tPRIMARY KEY (`priority`),\n\t\t\tKEY priority_id (`priority_id`)) ENGINE={$engine};");
syslog_db_execute("DROP TABLE IF EXISTS `" . $syslogdb_default . "`.`syslog_host_facilities`");
syslog_db_execute("CREATE TABLE IF NOT EXISTS `" . $syslogdb_default . "`.`syslog_host_facilities` (\n\t\t\t`host_id` int(10) unsigned NOT NULL,\n\t\t\t`facility_id` int(10) unsigned NOT NULL,\n\t\t\t`last_updated` TIMESTAMP NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,\n\t\t\tPRIMARY KEY (`host_id`,`facility_id`)) ENGINE={$engine};");
/* populate the tables */
syslog_db_execute("INSERT INTO `" . $syslogdb_default . "`.`syslog_hosts` (host) SELECT DISTINCT host FROM `" . $syslogdb_default . "`.`{$table}`");
syslog_db_execute("INSERT INTO `" . $syslogdb_default . "`.`syslog_facilities` (facility) SELECT DISTINCT facility FROM `" . $syslogdb_default . "`.`{$table}`");
foreach ($syslog_levels as $id => $priority) {
syslog_db_execute("REPLACE INTO `" . $syslogdb_default . "`.`syslog_priorities` (priority_id, priority) VALUES ({$id}, '{$priority}')");
}
/* a bit more horsepower please */
syslog_db_execute("INSERT INTO `" . $syslogdb_default . "`.`syslog_host_facilities`\n\t\t\t(host_id, facility_id)\n\t\t\tSELECT host_id, facility_id\n\t\t\tFROM ((SELECT DISTINCT host, facility\n\t\t\t\tFROM `" . $syslogdb_default . "`.`{$table}`) AS s\n\t\t\t\tINNER JOIN `" . $syslogdb_default . "`.`syslog_hosts` AS sh\n\t\t\t\tON s.host=sh.host\n\t\t\t\tINNER JOIN `" . $syslogdb_default . "`.`syslog_facilities` AS sf\n\t\t\t\tON sf.facility=s.facility)");
/* change the structure of the syslog table for performance sake */
$mysqlVersion = syslog_get_mysql_version("syslog");
if ($mysqlVersion >= 5) {
syslog_db_execute("ALTER TABLE `" . $syslogdb_default . "`.`{$table}`\n\t\t\t\tMODIFY COLUMN message varchar(1024) DEFAULT NULL,\n\t\t\t\tADD COLUMN facility_id int(10) UNSIGNED NULL AFTER facility,\n\t\t\t\tADD COLUMN priority_id int(10) UNSIGNED NULL AFTER facility_id,\n\t\t\t\tADD COLUMN host_id int(10) UNSIGNED NULL AFTER priority_id,\n\t\t\t\tADD COLUMN logtime DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER priority,\n\t\t\t\tADD INDEX facility_id (facility_id),\n\t\t\t\tADD INDEX priority_id (priority_id),\n\t\t\t\tADD INDEX host_id (host_id),\n\t\t\t\tADD INDEX logtime(logtime);");
} else {
syslog_db_execute("ALTER TABLE `" . $syslogdb_default . "`.`{$table}`\n\t\t\t\tADD COLUMN facility_id int(10) UNSIGNED NULL AFTER host,\n\t\t\t\tADD COLUMN priority_id int(10) UNSIGNED NULL AFTER facility_id,\n\t\t\t\tADD COLUMN host_id int(10) UNSIGNED NULL AFTER priority_id,\n\t\t\t\tADD COLUMN logtime DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER priority,\n\t\t\t\tADD INDEX facility_id (facility_id),\n\t\t\t\tADD INDEX priority_id (priority_id),\n\t\t\t\tADD INDEX host_id (host_id),\n\t\t\t\tADD INDEX logtime(logtime);");
}
/* convert dates and times to timestamp */
syslog_db_execute("UPDATE `" . $syslogdb_default . "`.`{$table}` SET logtime=TIMESTAMP(`date`, `time`)");
/* update the host_ids */
$hosts = syslog_db_fetch_assoc("SELECT * FROM `" . $syslogdb_default . "`.`syslog_hosts`");
if (sizeof($hosts)) {
foreach ($hosts as $host) {
syslog_db_execute("UPDATE `" . $syslogdb_default . "`.`{$table}`\n\t\t\t\tSET host_id=" . $host["host_id"] . "\n\t\t\t\tWHERE host='" . $host["host"] . "'");
}
}
/* update the priority_ids */
$priorities = $syslog_levels;
if (sizeof($priorities)) {
foreach ($priorities as $id => $priority) {
syslog_db_execute("UPDATE `" . $syslogdb_default . "`.`{$table}`\n\t\t\t\tSET priority_id=" . $id . "\n\t\t\t\tWHERE priority='" . $priority . "'");
}
}
/* update the facility_ids */
$fac = syslog_db_fetch_assoc("SELECT * FROM `" . $syslogdb_default . "`.`syslog_facilities`");
if (sizeof($fac)) {
foreach ($fac as $f) {
syslog_db_execute("UPDATE `" . $syslogdb_default . "`.`{$table}`\n\t\t\t\tSET facility_id=" . $f["facility_id"] . "\n\t\t\t\tWHERE facility='" . $f["facility"] . "'");
}
}
if (!$isbackground) {
syslog_db_execute("ALTER TABLE `" . $syslogdb_default . "`.`{$table}`\n\t\t\t\tDROP COLUMN `date`,\n\t\t\t\tDROP COLUMN `time`,\n\t\t\t\tDROP COLUMN `host`,\n\t\t\t\tDROP COLUMN `facility`,\n\t\t\t\tDROP COLUMN `priority`");
} else {
while (true) {
$fetch_size = '10000';
$sequence = syslog_db_fetch_cell("SELECT max(seq) FROM (SELECT seq FROM `" . $syslogdb_default . "`.`{$table}` ORDER BY seq LIMIT {$fetch_size}) AS preupgrade");
if ($sequence > 0 && $sequence != '') {
syslog_db_execute("INSERT INTO `" . $syslogdb_default . "`.`syslog` (facility_id, priority_id, host_id, logtime, message)\n\t\t\t\t\t\tSELECT facility_id, priority_id, host_id, logtime, message\n\t\t\t\t\t\tFROM `" . $syslogdb_default . "`.`{$table}`\n\t\t\t\t\t\tWHERE seq<{$sequence}");
syslog_db_execute("DELETE FROM `" . $syslogdb_default . "`.`{$table}` WHERE seq<={$sequence}");
} else {
syslog_db_execute("DROP TABLE `" . $syslogdb_default . "`.`{$table}`");
break;
}
}
}
/* create the soft removal table */
syslog_db_execute("DROP TABLE IF EXISTS `" . $syslogdb_default . "`.`syslog_removed`");
syslog_db_execute("CREATE TABLE `" . $syslogdb_default . "`.`syslog_removed` LIKE `" . $syslogdb_default . "`.`syslog`");
} else {
include_once $config['base_path'] . "/lib/poller.php";
$p = dirname(__FILE__);
$command_string = read_config_option("path_php_binary");
$extra_args = ' -q ' . $config['base_path'] . '/plugins/syslog/syslog_upgrade.php --type=' . $options["db_type"] . ' --engine=' . $engine . ' --days=' . $options["days"];
cacti_log("SYSLOG NOTE: Launching Background Syslog Database Upgrade Process", false, "SYSTEM");
exec_background($command_string, $extra_args);
}
/* reenable syslog xferral */
set_config_option('syslog_enabled', 'on');
}
