/**
* Check if the server id exists
* @param int $server_id
* @return boolean
* @throws \InvalidArgumentException
*/
public function serverId($server_id)
{
$server = $this->db->selectRow(PSM_DB_PREFIX . 'servers', array('server_id' => $server_id), array('server_id'));
if (empty($server)) {
throw new \InvalidArgumentException('server_no_match');
}
return true;
}
/**
* Get all uptime/history records for a server
* @param string $type
* @param int $server_id
* @param \DateTime $start_time Lowest DateTime of the graph
* @param \DateTime $end_time Highest DateTime of the graph
* @return array
*/
protected function getRecords($type, $server_id, $start_time, $end_time)
{
if (!in_array($type, array('history', 'uptime'))) {
return array();
}
$records = $this->db->execute('SELECT *
FROM `' . PSM_DB_PREFIX . "servers_{$type}`\n\t\t\t\tWHERE `server_id` = :server_id AND `date` BETWEEN :start_time AND :end_time ORDER BY `date` ASC", array('server_id' => $server_id, 'start_time' => $start_time->format('Y-m-d H:i:s'), 'end_time' => $end_time->format('Y-m-d H:i:s')));
return $records;
}
/**
* The function its all about. This one checks whether the given ip and port are up and running!
* If the server check fails it will try one more time, depending on the $max_runs.
*
* Please note: if the server is down but has not met the warning threshold, this will return true
* to avoid any "we are down" events.
* @param int $server_id
* @param int $max_runs how many times should the script recheck the server if unavailable. default is 2
* @return boolean TRUE if server is up, FALSE otherwise
*/
public function update($server_id, $max_runs = 2)
{
$this->server_id = $server_id;
$this->error = '';
$this->rtime = '';
// get server info from db
$this->server = $this->db->selectRow(PSM_DB_PREFIX . 'servers', array('server_id' => $server_id), array('server_id', 'ip', 'port', 'label', 'type', 'pattern', 'status', 'active', 'warning_threshold', 'warning_threshold_counter', 'timeout', 'website_username', 'website_password'));
if (empty($this->server)) {
return false;
}
switch ($this->server['type']) {
case 'service':
$this->status_new = $this->updateService($max_runs);
break;
case 'website':
$this->status_new = $this->updateWebsite($max_runs);
break;
}
// update server status
$save = array('last_check' => date('Y-m-d H:i:s'), 'error' => $this->error, 'rtime' => $this->rtime);
// log the uptime before checking the warning threshold,
// so that the warnings can still be reviewed in the server history.
psm_log_uptime($this->server_id, (int) $this->status_new, $this->rtime);
if ($this->status_new == true) {
// if the server is on, add the last_online value and reset the error threshold counter
$save['status'] = 'on';
$save['last_online'] = date('Y-m-d H:i:s');
$save['warning_threshold_counter'] = 0;
} else {
// server is offline, increase the error counter
$save['warning_threshold_counter'] = $this->server['warning_threshold_counter'] + 1;
if ($save['warning_threshold_counter'] < $this->server['warning_threshold']) {
// the server is offline but the error threshold has not been met yet.
// so we are going to leave the status "on" for now while we are in a sort of warning state..
$save['status'] = 'on';
$this->status_new = true;
} else {
$save['status'] = 'off';
}
}
$this->db->save(PSM_DB_PREFIX . 'servers', $save, array('server_id' => $this->server_id));
return $this->status_new;
}
/**
* Open a new user service
*
* @param \psm\Service\Database $db
* @param \Symfony\Component\HttpFoundation\Session\SessionInterface $session if NULL, one will be created
*/
public function __construct(Database $db, SessionInterface $session = null)
{
$this->db_connection = $db->pdo();
if (!psm_is_cli()) {
if ($session == null) {
$session = new Session();
$session->start();
}
$this->session = $session;
if (!defined('PSM_INSTALL') || !PSM_INSTALL) {
// check the possible login actions:
// 1. login via session data (happens each time user opens a page on your php project AFTER he has successfully logged in via the login form)
// 2. login via cookie
// if user has an active session on the server
if (!$this->loginWithSessionData()) {
$this->loginWithCookieData();
}
}
}
}
/**
* 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();
}
/**
* Upgrade for v3.0.0 release
*/
protected function upgrade300()
{
$queries = array();
// language is now stored as language code (ISO 639-1) + country code (ISO 3166-1)
$queries[] = "UPDATE `" . PSM_DB_PREFIX . "config` SET `value`='bg_BG' WHERE `key`='language' AND `value`='bg';";
$queries[] = "UPDATE `" . PSM_DB_PREFIX . "config` SET `value`='de_DE' WHERE `key`='language' AND `value`='de';";
$queries[] = "UPDATE `" . PSM_DB_PREFIX . "config` SET `value`='en_US' WHERE `key`='language' AND `value`='en';";
$queries[] = "UPDATE `" . PSM_DB_PREFIX . "config` SET `value`='fr_FR' WHERE `key`='language' AND `value`='fr';";
$queries[] = "UPDATE `" . PSM_DB_PREFIX . "config` SET `value`='ko_KR' WHERE `key`='language' AND `value`='kr';";
$queries[] = "UPDATE `" . PSM_DB_PREFIX . "config` SET `value`='nl_NL' WHERE `key`='language' AND `value`='nl';";
$queries[] = "UPDATE `" . PSM_DB_PREFIX . "config` SET `value`='pt_BR' WHERE `key`='language' AND `value`='br';";
$queries[] = "INSERT INTO `" . PSM_DB_PREFIX . "config` (`key`, `value`) VALUES ('version_update_check', '" . PSM_VERSION . "');";
$queries[] = "INSERT INTO `" . PSM_DB_PREFIX . "config` (`key`, `value`) VALUES ('email_smtp', '');";
$queries[] = "INSERT INTO `" . PSM_DB_PREFIX . "config` (`key`, `value`) VALUES ('email_smtp_host', '');";
$queries[] = "INSERT INTO `" . PSM_DB_PREFIX . "config` (`key`, `value`) VALUES ('email_smtp_port', '');";
$queries[] = "INSERT INTO `" . PSM_DB_PREFIX . "config` (`key`, `value`) VALUES ('email_smtp_username', '');";
$queries[] = "INSERT INTO `" . PSM_DB_PREFIX . "config` (`key`, `value`) VALUES ('email_smtp_password', '');";
$queries[] = "ALTER TABLE `" . PSM_DB_PREFIX . "log` CHANGE `log_id` `log_id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT;";
$queries[] = "ALTER TABLE `" . PSM_DB_PREFIX . "log` CHANGE `server_id` `server_id` INT( 11 ) UNSIGNED NOT NULL;";
$queries[] = "ALTER TABLE `" . PSM_DB_PREFIX . "servers` CHANGE `server_id` `server_id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT;";
$queries[] = "ALTER TABLE `" . PSM_DB_PREFIX . "servers` ADD `warning_threshold` MEDIUMINT( 1 ) UNSIGNED NOT NULL DEFAULT '1';";
$queries[] = "ALTER TABLE `" . PSM_DB_PREFIX . "servers` ADD `warning_threshold_counter` MEDIUMINT( 1 ) UNSIGNED NOT NULL DEFAULT '0';";
$queries[] = "ALTER TABLE `" . PSM_DB_PREFIX . "users` CHANGE `user_id` `user_id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT;";
$queries[] = "ALTER TABLE `" . PSM_DB_PREFIX . "users`\n\t\t\tADD `user_name` varchar(64) COLLATE utf8_general_ci NOT NULL COMMENT 'user\\'s name, unique' AFTER `user_id`,\n\t\t\tADD `password` varchar(255) COLLATE utf8_general_ci NOT NULL COMMENT 'user\\'s password in salted and hashed format' AFTER `user_name`,\n\t\t\tADD `password_reset_hash` char(40) COLLATE utf8_general_ci DEFAULT NULL COMMENT 'user\\'s password reset code' AFTER `password`,\n\t\t\tADD `password_reset_timestamp` bigint(20) DEFAULT NULL COMMENT 'timestamp of the password reset request' AFTER `password_reset_hash`,\n\t\t\tADD `rememberme_token` varchar(64) COLLATE utf8_general_ci DEFAULT NULL COMMENT 'user\\'s remember-me cookie token' AFTER `password_reset_timestamp`,\n\t\t\tADD `level` TINYINT( 2 ) UNSIGNED NOT NULL DEFAULT '20' AFTER `rememberme_token`;";
// make sure all current users are admins (previously we didnt have non-admins):
$queries[] = "UPDATE `" . PSM_DB_PREFIX . "users` SET `user_name`=`email`, `level`=10;";
$queries[] = "ALTER TABLE `" . PSM_DB_PREFIX . "users` ADD UNIQUE `unique_username` ( `user_name` );";
$queries[] = "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;";
$queries[] = "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;";
$queries[] = "CREATE TABLE `" . PSM_DB_PREFIX . "users_servers` (\n\t\t\t\t\t\t`user_id` INT( 11 ) UNSIGNED NOT NULL ,\n\t\t\t\t\t\t`server_id` INT( 11 ) UNSIGNED NOT NULL ,\n\t\t\t\t\t\tPRIMARY KEY ( `user_id` , `server_id` )\n\t\t\t\t\t\t) ENGINE = MYISAM ;";
$this->execSQL($queries);
// from 3.0 all user-server relations are in a separate table
$users = $this->db->select(PSM_DB_PREFIX . 'users', null, array('user_id', 'server_id'));
foreach ($users as $user) {
$idc = array();
if ($user['server_id'] == '') {
continue;
}
if (strpos($user['server_id'], ',') === false) {
$idc[] = $user['server_id'];
} else {
$idc = explode(',', $user['server_id']);
}
foreach ($idc as $id) {
$this->db->save(PSM_DB_PREFIX . 'users_servers', array('user_id' => $user['user_id'], 'server_id' => $id));
}
}
$this->execSQL("ALTER TABLE `" . PSM_DB_PREFIX . "users` DROP `server_id`;");
}
/**
* 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;
}
public function cleanup(\DateTime $retention_date, $server_id = null)
{
$sql_where_server = $server_id !== null ? ' `server_id` = ' . intval($server_id) . ' AND ' : '';
$this->db->execute("DELETE FROM `" . PSM_DB_PREFIX . "log` WHERE {$sql_where_server} `datetime` < :latest_date", array('latest_date' => $retention_date->format('Y-m-d 00:00:00')), false);
return true;
}
public function cleanup(\DateTime $retention_date, $server_id = null)
{
$sql_where_server = $this->createSQLWhereServer($server_id);
$this->db->execute("DELETE FROM `" . PSM_DB_PREFIX . "servers_history` WHERE {$sql_where_server} `date` < :latest_date", array('latest_date' => $retention_date->format('Y-m-d 00:00:00')), false);
return true;
}
