/**
* Install the tables for the monitor
*/
protected function installTables()
{
$tables = array(PSM_DB_PREFIX . 'config' => "CREATE TABLE `" . PSM_DB_PREFIX . "config` (\n\t\t\t\t\t\t\t`key` varchar(255) NOT NULL,\n\t\t\t\t\t\t\t`value` varchar(255) NOT NULL,\n\t\t\t\t\t\t\tPRIMARY KEY (`key`)\n\t\t\t\t\t\t) ENGINE=MyISAM DEFAULT CHARSET=utf8;", PSM_DB_PREFIX . 'users' => "CREATE TABLE IF NOT EXISTS `" . PSM_DB_PREFIX . "users` (\n\t\t\t\t\t\t\t`user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t\t\t\t\t`user_name` varchar(64) NOT NULL COMMENT 'user''s name, unique',\n\t\t\t\t\t\t\t`password` varchar(255) NOT NULL COMMENT 'user''s password in salted and hashed format',\n\t\t\t\t\t\t\t`password_reset_hash` char(40) DEFAULT NULL COMMENT 'user''s password reset code',\n\t\t\t\t\t\t\t`password_reset_timestamp` bigint(20) DEFAULT NULL COMMENT 'timestamp of the password reset request',\n\t\t\t\t\t\t\t`rememberme_token` varchar(64) DEFAULT NULL COMMENT 'user''s remember-me cookie token',\n\t\t\t\t\t\t\t`level` tinyint(2) unsigned NOT NULL DEFAULT '20',\n\t\t\t\t\t\t\t`name` varchar(255) NOT NULL,\n\t\t\t\t\t\t\t`mobile` varchar(15) NOT NULL,\n\t\t\t\t\t\t\t`pushover_key` varchar(255) NOT NULL,\n\t\t\t\t\t\t\t`pushover_device` varchar(255) NOT NULL,\n\t\t\t\t\t\t\t`email` varchar(255) NOT NULL,\n\t\t\t\t\t\t\tPRIMARY KEY (`user_id`),\n\t\t\t\t\t\t\tUNIQUE KEY `unique_username` (`user_name`)\n\t\t\t\t\t\t ) ENGINE=MyISAM DEFAULT CHARSET=utf8;", PSM_DB_PREFIX . 'users_preferences' => "CREATE TABLE IF NOT EXISTS `" . PSM_DB_PREFIX . "users_preferences` (\n\t\t\t\t\t\t\t`user_id` int(11) unsigned NOT NULL,\n\t\t\t\t\t\t\t`key` varchar(255) NOT NULL,\n\t\t\t\t\t\t\t`value` varchar(255) NOT NULL,\n\t\t\t\t\t\t\tPRIMARY KEY (`user_id`, `key`)\n\t\t\t\t\t\t ) ENGINE=MyISAM DEFAULT CHARSET=utf8;", PSM_DB_PREFIX . 'users_servers' => "CREATE TABLE `" . PSM_DB_PREFIX . "users_servers` (\n\t\t\t\t\t\t\t`user_id` INT( 11 ) UNSIGNED NOT NULL ,\n\t\t\t\t\t\t\t`server_id` INT( 11 ) UNSIGNED NOT NULL ,\n\t\t\t\t\t\t\tPRIMARY KEY ( `user_id` , `server_id` )\n\t\t\t\t\t\t\t) ENGINE = MYISAM ;", PSM_DB_PREFIX . 'log' => "CREATE TABLE `" . PSM_DB_PREFIX . "log` (\n\t\t\t\t\t\t `log_id` int(11) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t\t\t\t `server_id` int(11) unsigned NOT NULL,\n\t\t\t\t\t\t `type` enum('status','email','sms','pushover') NOT NULL,\n\t\t\t\t\t\t `message` varchar(255) NOT NULL,\n\t\t\t\t\t\t `datetime` timestamp NOT NULL default CURRENT_TIMESTAMP,\n\t\t\t\t\t\t `user_id` varchar(255) NOT NULL,\n\t\t\t\t\t\t PRIMARY KEY (`log_id`)\n\t\t\t\t\t\t) ENGINE=MyISAM DEFAULT CHARSET=utf8;", PSM_DB_PREFIX . 'servers' => "CREATE TABLE `" . PSM_DB_PREFIX . "servers` (\n\t\t\t\t\t\t `server_id` int(11) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t\t\t\t `ip` varchar(500) NOT NULL,\n\t\t\t\t\t\t `port` int(5) unsigned NOT NULL,\n\t\t\t\t\t\t `label` varchar(255) NOT NULL,\n\t\t\t\t\t\t `type` enum('service','website') NOT NULL default 'service',\n\t\t\t\t\t\t `pattern` varchar(255) NOT NULL,\n\t\t\t\t\t\t `status` enum('on','off') NOT NULL default 'on',\n\t\t\t\t\t\t `error` varchar(255) NULL,\n\t\t\t\t\t\t `rtime` FLOAT(9, 7) NULL,\n\t\t\t\t\t\t `last_online` datetime NULL,\n\t\t\t\t\t\t `last_check` datetime NULL,\n\t\t\t\t\t\t `active` enum('yes','no') NOT NULL default 'yes',\n\t\t\t\t\t\t `email` enum('yes','no') NOT NULL default 'yes',\n\t\t\t\t\t\t `sms` enum('yes','no') NOT NULL default 'no',\n\t\t\t\t\t\t `pushover` enum('yes','no') NOT NULL default 'yes',\n `warning_threshold` mediumint(1) unsigned NOT NULL DEFAULT '1',\n `warning_threshold_counter` mediumint(1) unsigned NOT NULL DEFAULT '0',\n `timeout` smallint(1) unsigned NULL DEFAULT NULL,\n\t\t\t\t\t\t PRIMARY KEY (`server_id`)\n\t\t\t\t\t\t) ENGINE=MyISAM DEFAULT CHARSET=utf8;", PSM_DB_PREFIX . 'servers_uptime' => "CREATE TABLE IF NOT EXISTS `" . PSM_DB_PREFIX . "servers_uptime` (\n\t\t\t\t\t\t`servers_uptime_id` int(11) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t\t\t\t`server_id` int(11) unsigned NOT NULL,\n\t\t\t\t\t\t`date` datetime NOT NULL,\n\t\t\t\t\t\t`status` tinyint(1) unsigned NOT NULL,\n\t\t\t\t\t\t`latency` float(9,7) DEFAULT NULL,\n\t\t\t\t\t\tPRIMARY KEY (`servers_uptime_id`),\n\t\t\t\t\t\tKEY `server_id` (`server_id`)\n\t\t\t\t\t ) ENGINE=MyISAM DEFAULT CHARSET=utf8;", PSM_DB_PREFIX . 'servers_history' => "CREATE TABLE IF NOT EXISTS `" . PSM_DB_PREFIX . "servers_history` (\n\t\t\t\t\t\t `servers_history_id` int(11) unsigned NOT NULL AUTO_INCREMENT,\n\t\t\t\t\t\t `server_id` int(11) unsigned NOT NULL,\n\t\t\t\t\t\t `date` date NOT NULL,\n\t\t\t\t\t\t `latency_min` float(9,7) NOT NULL,\n\t\t\t\t\t\t `latency_avg` float(9,7) NOT NULL,\n\t\t\t\t\t\t `latency_max` float(9,7) NOT NULL,\n\t\t\t\t\t\t `checks_total` int(11) unsigned NOT NULL,\n\t\t\t\t\t\t `checks_failed` int(11) unsigned NOT NULL,\n\t\t\t\t\t\t PRIMARY KEY (`servers_history_id`),\n\t\t\t\t\t\t UNIQUE KEY `server_id_date` (`server_id`,`date`)\n\t\t\t\t\t\t) ENGINE=MyISAM DEFAULT CHARSET=utf8;");
foreach ($tables as $name => $sql) {
$if_table_exists = $this->db->query("SHOW TABLES LIKE '{$name}'");
if (!empty($if_table_exists)) {
$this->log('Table ' . $name . ' already exists in your database!');
} else {
$this->execSQL($sql);
$this->log('Table ' . $name . ' added.');
}
}
}
/**
* Go :-)
*/
public function run()
{
// check if we need to restrict the servers to a certain user
$sql_join = '';
if ($this->user != null && $this->user->getUserLevel() > PSM_USER_ADMIN) {
// restrict by user_id
$sql_join = "JOIN `" . PSM_DB_PREFIX . "users_servers` AS `us` ON (\n\t\t\t\t\t\t`us`.`user_id`={$this->user->getUserId()}\n\t\t\t\t\t\tAND `us`.`server_id`=`s`.`server_id`\n\t\t\t\t\t\t)";
}
$sql = "SELECT `s`.`server_id`,`s`.`ip`,`s`.`port`,`s`.`label`,`s`.`type`,`s`.`pattern`,`s`.`status`,`s`.`active`,`s`.`email`,`s`.`sms`,`s`.`pushover`\n\t\t\t\tFROM `" . PSM_DB_PREFIX . "servers` AS `s`\n\t\t\t\t{$sql_join}\n\t\t\t\tWHERE `active`='yes' ";
$servers = $this->db->query($sql);
$updater = new Updater\StatusUpdater($this->db);
$notifier = new Updater\StatusNotifier($this->db);
foreach ($servers as $server) {
$status_old = $server['status'] == 'on' ? true : false;
$status_new = $updater->update($server['server_id']);
// notify the nerds if applicable
$notifier->notify($server['server_id'], $status_old, $status_new);
}
// clean-up time!! archive all records
$archive = new ArchiveManager($this->db);
$archive->archive();
$archive->cleanup();
}
/**
* Get all users for the provided server id
* @param int $server_id
* @return array
*/
public function getUsers($server_id)
{
// find all the users with this server listed
$users = $this->db->query("\n\t\t\tSELECT `u`.`user_id`, `u`.`name`,`u`.`email`, `u`.`mobile`, `u`.`pushover_key`, `u`.`pushover_device`\n\t\t\tFROM `" . PSM_DB_PREFIX . "users` AS `u`\n\t\t\tJOIN `" . PSM_DB_PREFIX . "users_servers` AS `us` ON (\n\t\t\t\t`us`.`user_id`=`u`.`user_id`\n\t\t\t\tAND `us`.`server_id` = {$server_id}\n\t\t\t)\n\t\t");
return $users;
}
