/** * 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; }