$uuid, 'text' => $text )); Message::addSuccess('notes-saved'); Util::redirect('?do=Statistics&uuid=' . $uuid); } } protected function doRender() { Render::setTitle(Dictionary::translate('lang_titleClientStatistics')); $uuid = Request::get('uuid', false, 'string'); if ($uuid !== false) { $this->showMachine($uuid); return; } $filter = Request::get('filter', false, 'string'); if ($filter !== false) { $argument = Request::get('argument', false, 'string'); $this->showMachineList($filter, $argument); return; } Render::addScriptBottom('chart.min'); Render::openTag('div', array('class' => 'row')); $this->showSummary(); $this->showMemory(); $this->showId44(); $this->showKvmState(); $this->showLatestMachines(); $this->showSystemModels(); Render::closeTag('div'); } private function capChart(&$json, $cutoff, $minSlice = 0.015) { $total = 0; foreach ($json as $entry) { $total += $entry['value']; } $cap = ceil($total * $cutoff); $accounted = 0; $id = 0; foreach ($json as $entry) { if (($accounted >= $cap || $entry['value'] / $total < $minSlice) && $id >= 3) break; $id++; $accounted += $entry['value']; } $json = array_slice($json, 0, $id); if ($accounted / $total < 0.99) { $json[] = array( 'color' => '#eee', 'label' => 'invalid', 'value' => ($total - $accounted) ); } } private function showSummary() { $cutoff = time() - 86400 * 30; $online = time() - 610; $known = Database::queryFirst("SELECT Count(*) AS val FROM machine WHERE lastseen > $cutoff"); $on = Database::queryFirst("SELECT Count(*) AS val FROM machine WHERE lastseen > $online"); $used = Database::queryFirst("SELECT Count(*) AS val FROM machine WHERE lastseen > $online AND logintime <> 0"); $hdd = Database::queryFirst("SELECT Count(*) AS val FROM machine WHERE badsectors > 10 AND lastseen > $cutoff"); $data = array( 'known' => $known['val'], 'online' => $on['val'], 'used' => $used['val'], 'usedpercent' => round($used['val'] / $on['val'] * 100), 'badhdd' => $hdd['val'] ); // Graph $cutoff = time() - 2*86400; $res = Database::simpleQuery("SELECT dateline, data FROM statistic WHERE typeid = '~stats' AND dateline > $cutoff ORDER BY dateline ASC"); $labels = array(); $points1 = array('data' => array(), 'label' => 'Online', 'fillColor' => '#efe', 'strokeColor' => '#aea', 'pointColor' => '#7e7', 'pointStrokeColor' => '#fff', 'pointHighlightFill' => '#fff', 'pointHighlightStroke' => '#7e7'); $points2 = array('data' => array(), 'label' => 'In use', 'fillColor' => '#fee', 'strokeColor' => '#eaa', 'pointColor' => '#e77', 'pointStrokeColor' => '#fff', 'pointHighlightFill' => '#fff', 'pointHighlightStroke' => '#e77'); $sum = 0; while ($row = $res->fetch(PDO::FETCH_ASSOC)) { $x = explode('#', $row['data']); if ($sum === 0) { $labels[] = date('H:i', $row['dateline']); } else { $x[1] = max($x[1], array_pop($points1['data'])); $x[2] = max($x[2], array_pop($points2['data'])); } $points1['data'][] = $x[1]; $points2['data'][] = $x[2]; $sum++; if ($sum === 12) { $sum = 0; } } $data['json'] = json_encode(array('labels' => $labels, 'datasets' => array($points1, $points2))); // Draw Render::addTemplate('statistics/summary', $data); } private function showSystemModels() { global $STATS_COLORS; $res = Database::simpleQuery("SELECT systemmodel, Round(AVG(realcores)) AS cores, Count(*) AS `count` FROM machine" . " GROUP BY systemmodel ORDER BY `count` DESC, systemmodel ASC"); $lines = array(); $json = array(); $id = 0; while ($row = $res->fetch(PDO::FETCH_ASSOC)) { if (empty($row['systemmodel'])) continue; settype($row['count'], 'integer'); $row['id'] = 'systemid' . $id; $row['urlsystemmodel'] = urlencode($row['systemmodel']); $lines[] = $row; $json[] = array( 'color' => $STATS_COLORS[$id % count($STATS_COLORS)], 'label' => 'systemid' . $id, 'value' => $row['count'] ); ++$id; } $this->capChart($json, 0.92); Render::addTemplate('statistics/cpumodels', array('rows' => $lines, 'json' => json_encode($json))); } private function showMemory() { global $STATS_COLORS, $SIZE_RAM; $res = Database::simpleQuery("SELECT mbram, Count(*) AS `count` FROM machine GROUP BY mbram"); $lines = array(); while ($row = $res->fetch(PDO::FETCH_ASSOC)) { $gb = ceil($row['mbram'] / 1024); for ($i = 1; $i < count($SIZE_RAM); ++$i) { if ($SIZE_RAM[$i] < $gb) continue; if ($SIZE_RAM[$i] - $gb >= $gb - $SIZE_RAM[$i-1]) --$i; $gb = $SIZE_RAM[$i]; break; } if (isset($lines[$gb])) { $lines[$gb] += $row['count']; } else { $lines[$gb] = $row['count']; } } asort($lines); $data = array('rows' => array()); $json = array(); $id = 0; foreach (array_reverse($lines, true) as $k => $v) { $data['rows'][] = array('gb' => $k, 'count' => $v, 'class' => $this->ramColorClass($k * 1024)); $json[] = array( 'color' => $STATS_COLORS[$id % count($STATS_COLORS)], 'label' => (string)$k, 'value' => $v ); ++$id; } $this->capChart($json, 0.92); $data['json'] = json_encode($json); Render::addTemplate('statistics/memory', $data); } private function showKvmState() { $colors = array('UNKNOWN' => '#666', 'UNSUPPORTED' => '#ea5', 'DISABLED' => '#e55', 'ENABLED' => '#6d6'); $res = Database::simpleQuery("SELECT kvmstate, Count(*) AS `count` FROM machine GROUP BY kvmstate ORDER BY `count` DESC"); $lines = array(); $json = array(); while ($row = $res->fetch(PDO::FETCH_ASSOC)) { $lines[] = $row; $json[] = array( 'color' => isset($colors[$row['kvmstate']]) ? $colors[$row['kvmstate']] : '#000', 'label' => $row['kvmstate'], 'value' => $row['count'] ); } Render::addTemplate('statistics/kvmstate', array('rows' => $lines, 'json' => json_encode($json))); } private function showId44() { global $STATS_COLORS, $SIZE_ID44; $res = Database::simpleQuery("SELECT id44mb, Count(*) AS `count` FROM machine GROUP BY id44mb"); $lines = array(); $total = 0; while ($row = $res->fetch(PDO::FETCH_ASSOC)) { $total += $row['count']; $gb = ceil($row['id44mb'] / 1024); for ($i = 1; $i < count($SIZE_ID44); ++$i) { if ($SIZE_ID44[$i] < $gb) continue; if ($SIZE_ID44[$i] - $gb >= $gb - $SIZE_ID44[$i-1]) --$i; $gb = $SIZE_ID44[$i]; break; } if (isset($lines[$gb])) { $lines[$gb] += $row['count']; } else { $lines[$gb] = $row['count']; } } asort($lines); $data = array('rows' => array()); $json = array(); $id = 0; foreach (array_reverse($lines, true) as $k => $v) { $data['rows'][] = array('gb' => $k, 'count' => $v, 'class' => $this->hddColorClass($k)); if ($k === 0) { $color = '#e55'; } else { $color = $STATS_COLORS[$id++ % count($STATS_COLORS)]; } $json[] = array( 'color' => $color, 'label' => (string)$k, 'value' => $v ); } $this->capChart($json, 0.95); $data['json'] = json_encode($json); Render::addTemplate('statistics/id44', $data); } private function showLatestMachines() { $data = array('cutoff' => ceil(time() / 3600) * 3600 - 86400 * 7); $res = Database::simpleQuery("SELECT machineuuid, clientip, hostname, firstseen, mbram, kvmstate, id44mb FROM machine" . " WHERE firstseen > :cutoff ORDER BY firstseen DESC LIMIT 32", $data); $rows = array(); $count = 0; while ($row = $res->fetch(PDO::FETCH_ASSOC)) { if (empty($row['hostname'])) { $row['hostname'] = $row['clientip']; } $row['firstseen'] = date('d.m. H:i', $row['firstseen']); $row['gbram'] = round(round($row['mbram'] / 500) / 2, 1); // Trial and error until we got "expected" rounding.. $row['gbtmp'] = round($row['id44mb'] / 1024); $row['ramclass'] = $this->ramColorClass($row['mbram']); $row['kvmclass'] = $this->kvmColorClass($row['kvmstate']); $row['hddclass'] = $this->hddColorClass($row['gbtmp']); $row['kvmicon'] = $row['kvmstate'] === 'ENABLED' ? '✓' : '✗'; if (++$count > 5) { $row['style'] = 'display:none'; } $rows[] = $row; } Render::addTemplate('statistics/newclients', array('rows' => $rows, 'openbutton' => $count > 5)); } private function showMachineList($filter, $argument) { global $SIZE_RAM, $SIZE_ID44; $join = ''; $filters = array('cpumodel', 'realcores', 'kvmstate', 'clientip', 'macaddr', 'machineuuid', 'systemmodel'); if (in_array($filter, $filters)) { // Simple filters mapping into db $where = " $filter = :argument"; $args = array('argument' => $argument); } elseif ($filter === 'gbram') { // Memory by rounded GB $lower = floor($this->findBestValue($SIZE_RAM, $argument, false) * 1024 - 100); $upper = ceil($this->findBestValue($SIZE_RAM, $argument, true) * 1024 + 100); $where = " mbram BETWEEN $lower AND $upper"; $args = array(); } elseif ($filter === 'hddgb') { // HDD by rounded GB $lower = floor($this->findBestValue($SIZE_ID44, $argument, false) * 1024 - 100); $upper = ceil($this->findBestValue($SIZE_ID44, $argument, true) * 1024 + 100); $where = " id44mb BETWEEN $lower AND $upper"; $args = array(); } elseif ($filter === 'subnet') { $argument = preg_replace('/[^0-9\.:]/', '', $argument); $where = " clientip LIKE '$argument%'"; $args = array(); } elseif ($filter === 'badsectors') { $where = " badsectors >= :argument "; $args = array('argument' => $argument); } elseif ($filter === 'state') { if ( $argument === 'on') { $where = " lastseen + 600 > UNIX_TIMESTAMP() "; } elseif ($argument === 'off') { $where = " lastseen + 600 < UNIX_TIMESTAMP() "; } elseif ($argument === 'idle') { $where = " lastseen + 600 > UNIX_TIMESTAMP() AND logintime = 0 "; } elseif ($argument === 'occupied') { $where = " lastseen + 600 > UNIX_TIMESTAMP() AND logintime <> 0 "; } else { Message::addError('invalid-filter'); return; } } elseif ($filter === 'location') { $where = "subnet.locationid = :lid OR machine.locationid = :lid"; $join = " INNER JOIN subnet ON (INET_ATON(clientip) BETWEEN startaddr AND endaddr) "; $args = array('lid' => (int)$argument); } else { Message::addError('invalid-filter'); return; } $res = Database::simpleQuery("SELECT machineuuid, macaddr, clientip, firstseen, lastseen," . " logintime, lastboot, realcores, mbram, kvmstate, cpumodel, id44mb, hostname, notes IS NOT NULL AS hasnotes, badsectors FROM machine" . " $join WHERE $where ORDER BY lastseen DESC, clientip ASC", $args); $rows = array(); $NOW = time(); while ($row = $res->fetch(PDO::FETCH_ASSOC)) { if ($NOW - $row['lastseen'] > 610) { $row['state_off'] = true; } elseif ($row['logintime'] == 0) { $row['state_idle'] = true; } else { $row['state_occupied'] = true; } //$row['firstseen'] = date('d.m.Y H:i', $row['firstseen']); $row['lastseen'] = date('d.m. H:i', $row['lastseen']); //$row['lastboot'] = date('d.m. H:i', $row['lastboot']); $row['gbram'] = round(round($row['mbram'] / 500) / 2, 1); // Trial and error until we got "expected" rounding.. $row['gbtmp'] = round($row['id44mb'] / 1024); $octets = explode('.', $row['clientip']); if (count($octets) === 4) { $row['subnet'] = "$octets[0].$octets[1].$octets[2]."; $row['lastoctet'] = $octets[3]; } $row['ramclass'] = $this->ramColorClass($row['mbram']); $row['kvmclass'] = $this->kvmColorClass($row['kvmstate']); $row['hddclass'] = $this->hddColorClass($row['gbtmp']); if (empty($row['hostname'])) $row['hostname'] = $row['clientip']; $rows[] = $row; } Render::addTemplate('statistics/clientlist', array('rows' => $rows, 'filter' => $filter, 'argument' => $argument)); } private function ramColorClass($mb) { if ($mb < 1500) return 'danger'; if ($mb < 2500) return 'warning'; return ''; } private function kvmColorClass($state) { if ($state === 'DISABLED') return 'danger'; if ($state === 'UNKNOWN' || $state === 'UNSUPPORTED') return 'warning'; return ''; } private function hddColorClass($gb) { if ($gb < 7) return 'danger'; if ($gb < 25) return 'warning'; return ''; } private function findBestValue($array, $value, $up) { $best = 0; for ($i = 0; $i < count($array); ++$i) { if (abs($array[$i] - $value) < abs($array[$best] - $value)) { $best = $i; } } if (!$up && $best === 0) { return $array[0]; } if ($up && $best + 1 === count($array)) { return $array[$best]; } if ($up) { return ($array[$best] + $array[$best + 1]) / 2; } return ($array[$best] + $array[$best - 1]) / 2; } private function fillSessionInfo(&$row) { $res = Database::simpleQuery("SELECT dateline, username, data FROM statistic" . " WHERE clientip = :ip AND typeid = '.vmchooser-session-name'" . " AND dateline BETWEEN :start AND :end", array( 'ip' => $row['clientip'], 'start' => $row['logintime'] - 60, 'end' => $row['logintime'] + 300 )); $session = false; while ($r = $res->fetch(PDO::FETCH_ASSOC)) { if ($session === false || abs($session['dateline'] - $row['logintime']) > abs($r['dateline'] - $row['logintime'])) { $session = $r; } } if ($session !== false) { $row['session'] = $session['data']; $row['username'] = $session['username']; } } private function showMachine($uuid) { $client = Database::queryFirst("SELECT machineuuid, macaddr, clientip, firstseen, lastseen, logintime, lastboot," . " mbram, kvmstate, cpumodel, id44mb, data, hostname, notes FROM machine WHERE machineuuid = :uuid", array('uuid' => $uuid)); // Mangle fields $NOW = time(); if ($NOW - $client['lastseen'] > 610) { $client['state_off'] = true; } elseif ($client['logintime'] == 0) { $client['state_idle'] = true; } else { $client['state_occupied'] = true; $this->fillSessionInfo($client); } $client['firstseen_s'] = date('d.m.Y H:i', $client['firstseen']); $client['lastseen_s'] = date('d.m.Y H:i', $client['lastseen']); $uptime = $NOW - $client['lastboot']; $client['lastboot_s'] = date('d.m.Y H:i', $client['lastboot']) . ' (Up ' . floor($uptime / 86400) . 'd ' . gmdate('H:i', $uptime) . ')'; $client['logintime_s'] = date('d.m.Y H:i', $client['logintime']); $client['gbram'] = round(round($client['mbram'] / 500) / 2, 1); $client['gbtmp'] = round($client['id44mb'] / 1024); $client['ramclass'] = $this->ramColorClass($client['mbram']); $client['kvmclass'] = $this->kvmColorClass($client['kvmstate']); $client['hddclass'] = $this->hddColorClass($client['gbtmp']); // Parse the giant blob of data $hdds = array(); if (preg_match_all('/##### ([^#]+) #+$(.*?)^#####/ims', $client['data'] . '########', $out, PREG_SET_ORDER)) { foreach ($out as $section) { if ($section[1] === 'CPU') { $this->parseCpu($client, $section[2]); } if ($section[1] === 'dmidecode') { $this->parseDmiDecode($client, $section[2]); } if ($section[1] === 'Partition tables') { $this->parseHdd($hdds, $section[2]); } if (isset($hdds['hdds']) && $section[1] === 'smartctl') { // This currently required that the partition table section comes first... $this->parseSmartctl($hdds['hdds'], $section[2]); } } } unset($client['data']); // Throw output at user Render::addTemplate('statistics/machine-main', $client); // Sessions $NOW = time(); $cutoff = $NOW - 86400 * 7; //if ($cutoff < $client['firstseen']) $cutoff = $client['firstseen']; $scale = 100 / ($NOW - $cutoff); $res = Database::simpleQuery("SELECT dateline, typeid, data FROM statistic" . " WHERE dateline > :cutoff AND typeid IN ('~session-length', '~offline-length') AND machineuuid = :uuid ORDER BY dateline ASC", array( 'cutoff' => $cutoff - 86400 * 14, 'uuid' => $uuid )); $spans['rows'] = array(); $spans['graph'] = ''; $last = false; $first = true; while ($row = $res->fetch(PDO::FETCH_ASSOC)) { if ($first && $row['dateline'] > $cutoff && $client['lastboot'] > $cutoff) { // Special case: offline before $spans['graph'] .= '