From 8fa66377a1c97d42d83d91ef0821f37f1b885617 Mon Sep 17 00:00:00 2001 From: Simon Rettberg Date: Tue, 6 Dec 2016 16:21:56 +0100 Subject: [statistics/locations/..] Cache calculated locationid of machine in machine table We now have three columns for the locationid: - subnetlocationid Updated whenever the clientip of a client changes on bootup, or when locations/subnets are edited in the locations module. - fixedlocationid Takes the role of the old locationid field - set when a machine is placed in a room via roomplanner, set to NULL otherwise. - locationid Set to fixedlocationid if it's not NULL, to subnetlocationid otherwise. The field is updated by a BEFORE UPDATE trigger. --- modules-available/locations/inc/location.inc.php | 153 ++++++++++++++++------- modules-available/locations/page.inc.php | 121 +++++++++++------- modules-available/roomplanner/page.inc.php | 7 +- modules-available/statistics/api.inc.php | 24 +++- modules-available/statistics/inc/filter.inc.php | 8 +- modules-available/statistics/install.inc.php | 47 ++++++- modules-available/statistics/page.inc.php | 3 - 7 files changed, 257 insertions(+), 106 deletions(-) diff --git a/modules-available/locations/inc/location.inc.php b/modules-available/locations/inc/location.inc.php index bde07e61..73080094 100644 --- a/modules-available/locations/inc/location.inc.php +++ b/modules-available/locations/inc/location.inc.php @@ -6,6 +6,7 @@ class Location private static $flatLocationCache = false; private static $assocLocationCache = false; private static $treeCache = false; + private static $subnetMapCache = false; private static function getTree() { @@ -57,15 +58,22 @@ class Location $output = array(); foreach ($tree as $node) { $output[(int)$node['locationid']] = array( + 'locationid' => (int)$node['locationid'], 'parentlocationid' => (int)$node['parentlocationid'], 'parents' => $parents, 'locationname' => $node['locationname'], - 'depth' => $depth + 'depth' => $depth, + 'isleaf' => true, ); if (!empty($node['children'])) { $output += self::flattenTreeAssoc($node['children'], array_merge($parents, array((int)$node['locationid'])), $depth + 1); } } + foreach ($output as &$entry) { + if (!isset($output[$entry['parentlocationid']])) + continue; + $output[$entry['parentlocationid']]['isleaf'] = false; + } return $output; } @@ -185,33 +193,31 @@ class Location if (Module::get('statistics') === false) return false; $ret = Database::queryFirst("SELECT locationid FROM machine WHERE machineuuid = :uuid", compact('uuid')); - if ($ret === false) + if ($ret === false || !$ret['locationid']) return false; return (int)$ret['locationid']; } /** * Get closest location by matching subnets. Deepest match in tree wins. + * Ignores any manually assigned locationid (fixedlocationid). * * @param string $ip IP address of client * @return bool|int locationid, or false if no match */ public static function getFromIp($ip) { - $locationId = false; - $long = sprintf('%u', ip2long($ip)); - $net = Database::simpleQuery('SELECT locationid FROM subnet' - . ' WHERE :ip BETWEEN startaddr AND endaddr', array('ip' => $long)); - while ($row = $net->fetch(PDO::FETCH_ASSOC)) { - $locations = self::getLocationsAssoc(); - $id = (int)$row['locationid']; - if (!isset($locations[$id])) - continue; - if ($locationId !== false && $locations[$id]['depth'] <= $locations[$locationId]['depth']) - continue; - $locationId = $id; + if (Module::get('statistics') !== false) { + // Shortcut - try to use subnetlocationid in machine table + $ret = Database::queryFirst("SELECT subnetlocationid FROM machine WHERE clientip = :ip", compact('ip')); + if ($ret !== false) { + if ($ret['subnetlocationid'] > 0) { + return (int)$ret['subnetlocationid']; + } + return false; + } } - return $locationId; + return self::mapIpToLocation($ip); } /** @@ -230,7 +236,9 @@ class Location if ($ipLoc !== false && $uuid !== false) { // Machine ip maps to a location, and we have a client supplied uuid $uuidLoc = self::getFromMachineUuid($uuid); - if ($uuidLoc !== false) { + if ($uuidLoc === $ipLoc) { + $locationId = $uuidLoc; + } else if ($uuidLoc !== false) { // Validate that the location the IP maps to is in the chain we get using the // location determined by the uuid $uuidLocations = self::getLocationRootChain($uuidLoc); @@ -285,40 +293,13 @@ class Location return $subnets; } - /** - * @return array|bool assoc array mapping from locationid to subnets - */ - public static function getSubnetsByLocation(&$overlapSelf, &$overlapOther, $recursive = true) + public static function getOverlappingSubnets(&$overlapSelf = false, &$overlapOther = false) { + if ($overlapSelf === false && $overlapOther === false) { + return; + } $locs = self::getLocationsAssoc(); $subnets = self::getSubnets(); - // Find locations having nets overlapping with themselves if array was passed - if ($overlapSelf === true || $overlapOther === true) { - self::findOverlap($locs, $subnets, $overlapSelf, $overlapOther); - } - // Accumulate - copy up subnet definitions - foreach ($locs as &$loc) { - $loc['subnets'] = array(); - } - unset($loc); - foreach ($subnets as $subnet) { - $lid = $subnet['locationid']; - while (isset($locs[$lid])) { - $locs[$lid]['subnets'][] = array( - 'startaddr' => $subnet['startaddr'], - 'endaddr' => $subnet['endaddr'] - ); - if (!$recursive) - break; - $lid = $locs[$lid]['parentlocationid']; - } - } - return $locs; - } - - - private static function findOverlap($locs, $subnets, &$overlapSelf, &$overlapOther) - { if ($overlapSelf) { $self = array(); } @@ -368,6 +349,84 @@ class Location } } + /** + * @return array|bool assoc array mapping from locationid to subnets + */ + public static function getSubnetsByLocation($recursive = false) + { + $locs = self::getLocationsAssoc(); + $subnets = self::getSubnets(); + // Accumulate - copy up subnet definitions + foreach ($locs as &$loc) { + $loc['subnets'] = array(); + } + unset($loc); + foreach ($subnets as $subnet) { + $lid = $subnet['locationid']; + while (isset($locs[$lid])) { + $locs[$lid]['subnets'][] = array( + 'startaddr' => $subnet['startaddr'], + 'endaddr' => $subnet['endaddr'] + ); + if (!$recursive) + break; + $lid = $locs[$lid]['parentlocationid']; + } + } + return $locs; + } + + /** + * Lookup $ip in subnets, try to find one that matches + * and return its locationid. + * If two+ subnets match, the one which is nested deeper wins. + * If two+ subnets match and have the same depth, the one which + * is smaller wins. + * If two+ subnets match and have the same depth and size, a + * random one will be returned. + * + * @param $ip IP to look up + * @return bool|int locationid ip matches, false = no match + */ + public static function mapIpToLocation($ip) + { + if (self::$subnetMapCache === false) { + self::$subnetMapCache = self::getSubnetsByLocation(); + } + $long = sprintf('%u', ip2long($ip)); + $best = false; + $bestSize = 0; + foreach (self::$subnetMapCache as $lid => $data) { + if ($best !== false && self::$subnetMapCache[$lid]['depth'] < self::$subnetMapCache[$best]['depth']) + continue; // Don't even need to take a look + foreach ($data['subnets'] as $subnet) { + if ($long < $subnet['startaddr'] || $long > $subnet['endaddr']) + continue; // Nope + if ($best !== false // Already have a best candidate + && self::$subnetMapCache[$lid]['depth'] === self::$subnetMapCache[$best]['depth'] // Same depth + && $bestSize < $subnet['endaddr'] - $subnet['startaddr']) { // Old candidate has smaller subnet + // So we ignore this one as the old one is more specific + continue; + } + $bestSize = $subnet['endaddr'] - $subnet['startaddr']; + $best = $lid; + } + } + if ($best === false) + return false; + return (int)$best; + } + + public static function updateMapIpToLocation($uuid, $ip) + { + $loc = self::mapIpToLocation($ip); + if ($loc === false) { + Database::exec("UPDATE machine SET subnetlocationid = NULL WHERE machineuuid = :uuid", compact('uuid')); + } else { + Database::exec("UPDATE machine SET subnetlocationid = :loc WHERE machineuuid = :uuid", compact('loc', 'uuid')); + } + } + private static function overlap($net1, $net2) { return ($net1['startaddr'] <= $net2['endaddr'] && $net1['endaddr'] >= $net2['startaddr']); diff --git a/modules-available/locations/page.inc.php b/modules-available/locations/page.inc.php index e002ae4c..26612099 100644 --- a/modules-available/locations/page.inc.php +++ b/modules-available/locations/page.inc.php @@ -26,6 +26,36 @@ class Page_Locations extends Page } } + private function updateAutoLocationId() + { + if (Module::get('statistics') === false) + return; // Nothing to do + $res = Database::simpleQuery("SELECT machineuuid, clientip FROM machine"); + $updates = array(); + $nulls = array(); + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $loc = Location::mapIpToLocation($row['clientip']); + if ($loc === false) { + $nulls[] = $row['machineuuid']; + } else { + if (!isset($updates[$loc])) { + $updates[$loc] = array(); + } + $updates[$loc][] = $row['machineuuid']; + } + } + error_log(print_r($updates, true)); + if (!empty($nulls)) { + $qs = '?' . str_repeat(',?', count($nulls) - 1); + Database::exec("UPDATE machine SET subnetlocationid = NULL WHERE machineuuid IN ($qs)", $nulls); + } + foreach ($updates as $lid => $machines) { + $qs = '?' . str_repeat(',?', count($machines) - 1); + $lid = (int)$lid; + Database::exec("UPDATE machine SET subnetlocationid = $lid WHERE machineuuid IN ($qs)", $machines); + } + } + private function updateSubnets() { $count = 0; @@ -55,6 +85,7 @@ class Page_Locations extends Page $count += $stmt->rowCount(); } } + $this->updateAutoLocationId(); Message::addSuccess('subnets-updated', $count); Util::redirect('?do=Locations'); } @@ -111,16 +142,22 @@ class Page_Locations extends Page Message::addError('main.value-invalid', 'locationid', $locationId); Util::redirect('?do=Locations'); } + $change = false; // Delete location? if ($locationId === $del) { $this->deleteLocation($location); + $change = true; } // Update subnets - $this->updateLocationSubnets(); + $change |= $this->updateLocationSubnets(); // Insert subnets - $this->addNewLocationSubnets($location); + $change |= $this->addNewLocationSubnets($location); // Update location! - $this->updateLocationData($location); + $change |= $this->updateLocationData($location); + if ($change) { + // In case subnets or tree layout changed, recalc this + $this->updateAutoLocationId(); + } Util::redirect('?do=Locations'); } @@ -182,10 +219,12 @@ class Page_Locations extends Page if ($ret > 0) { Message::addSuccess('location-updated', $newName); } + return $newParent != $location['parentlocationid']; } private function updateLocationSubnets() { + $change = false; // Deletion first $dels = Request::post('deletesubnet', false); if (is_array($dels)) { @@ -200,13 +239,14 @@ class Page_Locations extends Page } if ($count > 0) { Message::addInfo('subnets-deleted', $count); + $change = true; } } // Now actual updates $starts = Request::post('startaddr', false); $ends = Request::post('endaddr', false); if (!is_array($starts) || !is_array($ends)) { - return; + return $change; } $count = 0; $stmt = Database::prepare('UPDATE subnet SET startaddr = :start, endaddr = :end' @@ -225,16 +265,19 @@ class Page_Locations extends Page } if ($count > 0) { Message::addInfo('subnets-updated', $count); + $change = true; } + return $change; } private function addNewLocationSubnets($location) { + $change = false; $locationId = (int)$location['locationid']; $starts = Request::post('newstartaddr', false); $ends = Request::post('newendaddr', false); if (!is_array($starts) || !is_array($ends)) { - return; + return $change; } $count = 0; $stmt = Database::prepare('INSERT INTO subnet SET startaddr = :start, endaddr = :end, locationid = :location'); @@ -261,7 +304,9 @@ class Page_Locations extends Page } if ($count > 0) { Message::addInfo('subnets-created', $count); + $change = true; } + return $change; } /* @@ -290,51 +335,44 @@ class Page_Locations extends Page } } - private function queryMachineCount($lid, $subnets, $xtra = '') - { - if (!isset($subnets[$lid])) - return 0; - $loc =& $subnets[$lid]; - if (empty($loc['subnets'])) { - $query = "SELECT Count(*) AS cnt FROM machine WHERE (locationid = :locationid)"; - } else { - $query = "SELECT Count(*) AS cnt FROM machine WHERE (locationid = :locationid OR (locationid IS NULL AND (0"; - foreach ($loc['subnets'] as $sub) { - $query .= ' OR INET_ATON(clientip) BETWEEN ' . $sub['startaddr'] . ' AND ' . $sub['endaddr']; - } - $query .= ')))'; - } - if (!empty($xtra)) { - $query .= ' ' . $xtra; - } - $ret = Database::queryFirst($query, array('locationid' => $lid)); - return $ret['cnt']; - } - private function showLocationList() { + // Warn admin about overlapping subnet definitions $overlapSelf = $overlapOther = true; - $subnetsFlat = Location::getSubnetsByLocation($overlapSelf, $overlapOther, false); - $subnetsRecursive = Location::getSubnetsByLocation($overlapSelf, $overlapOther, true); - $locs = Location::getLocations(0, 0, false, true); + Location::getOverlappingSubnets($overlapSelf, $overlapOther); + //$locs = Location::getLocations(0, 0, false, true); + $locs = Location::getLocationsAssoc(); // Statistics: Count machines for each subnet $unassigned = false; if (Module::get('statistics') !== false) { $DL = time() - 605; - foreach ($locs as &$location) { - $lid = (int)$location['locationid']; - $location['clientCount'] = $this->queryMachineCount($lid, $subnetsFlat); - $location['clientCountSum'] = $this->queryMachineCount($lid, $subnetsRecursive); - if ($location['clientCountSum'] > 0) { - $location['clientLoad'] = round(($this->queryMachineCount($lid, $subnetsRecursive, "AND logintime <> 0 AND lastseen > $DL") / $location['clientCountSum']) * 100) . '%'; + $unassigned = 0; + $res = Database::simpleQuery("SELECT locationid, Count(*) AS cnt, Sum(If(lastseen > $DL AND logintime <> 0, 1, 0)) AS used + FROM machine GROUP BY locationid"); + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $loc = (int)$row['locationid']; + if (isset($locs[$loc])) { + $locs[$loc]['clientCount'] = $row['cnt']; + $locs[$loc]['clientLoad'] = round(100 * $row['used'] / $row['cnt']) . '%'; + } else { + $unassigned += $row['cnt']; + } + } + unset($loc); + foreach ($locs as &$loc) { + if (!isset($loc['clientCount'])) { + $loc['clientCount'] = 0; + $loc['clientLoad'] = '0%'; + } + $loc['clientCountSum'] = $loc['clientCount']; + } + unset($loc); + foreach ($locs as $loc) { + foreach ($loc['parents'] as $pid) { + $locs[(int)$pid]['clientCountSum'] += $loc['clientCount']; } } - $res = Database::queryFirst("SELECT Count(*) AS cnt FROM machine m" - . " LEFT JOIN subnet s ON (INET_ATON(m.clientip) BETWEEN s.startaddr AND s.endaddr)" - . " WHERE m.locationid IS NULL AND s.locationid IS NULL"); - $unassigned = $res['cnt']; } - unset($loc, $location); // Show currently active sysconfig for each location $defaultConfig = false; if (Module::isAvailable('sysconfig')) { @@ -445,8 +483,7 @@ class Page_Locations extends Page $count = $online = $used = 0; if (Module::get('statistics') !== false) { $mres = Database::simpleQuery("SELECT lastseen, logintime FROM machine" - . " LEFT JOIN subnet ON (INET_ATON(machine.clientip) BETWEEN startaddr AND endaddr AND machine.locationid IS NULL)" - . " WHERE (subnet.locationid = :lid) OR (machine.locationid = :lid)", array('lid' => $locationId)); + . " WHERE machine.locationid = :lid", array('lid' => $locationId)); $DL = time() - 605; while ($row = $mres->fetch(PDO::FETCH_ASSOC)) { $count++; diff --git a/modules-available/roomplanner/page.inc.php b/modules-available/roomplanner/page.inc.php index 65025c5f..c03b69d0 100644 --- a/modules-available/roomplanner/page.inc.php +++ b/modules-available/roomplanner/page.inc.php @@ -191,14 +191,14 @@ class Page_Roomplanner extends Page 'gridCol' => $computer['gridCol'], 'itemlook' => $computer['itemlook']]); - Database::exec('UPDATE machine SET position = :position, locationid = :locationid WHERE machineuuid = :muuid', + Database::exec('UPDATE machine SET position = :position, fixedlocationid = :locationid WHERE machineuuid = :muuid', ['locationid' => $this->locationid, 'muuid' => $computer['muuid'], 'position' => $position]); } $toDelete = array_diff($oldUuids, $newUuids); foreach ($toDelete as $d) { - Database::exec("UPDATE machine SET position = '', locationid = NULL WHERE machineuuid = :uuid", ['uuid' => $d]); + Database::exec("UPDATE machine SET position = '', fixedlocationid = NULL WHERE machineuuid = :uuid", ['uuid' => $d]); } } @@ -256,8 +256,7 @@ class Page_Roomplanner extends Page protected function getPotentialMachines() { $result = Database::simpleQuery('SELECT machineuuid, macaddr, clientip, hostname ' - . 'FROM machine INNER JOIN subnet ON (INET_ATON(clientip) BETWEEN startaddr AND endaddr) ' - . 'WHERE subnet.locationid = :locationid', ['locationid' => $this->locationid]); + . 'FROM machine WHERE locationid = :locationid', ['locationid' => $this->locationid]); $machines = []; diff --git a/modules-available/statistics/api.inc.php b/modules-available/statistics/api.inc.php index 5fc57194..2ac6e782 100644 --- a/modules-available/statistics/api.inc.php +++ b/modules-available/statistics/api.inc.php @@ -32,7 +32,7 @@ if ($type{0} === '~') { } } $NOW = time(); - $old = Database::queryFirst('SELECT logintime, lastseen FROM machine WHERE machineuuid = :uuid', array('uuid' => $uuid)); + $old = Database::queryFirst('SELECT clientip, logintime, lastseen FROM machine WHERE machineuuid = :uuid', array('uuid' => $uuid)); if ($old !== false) { settype($old['logintime'], 'integer'); settype($old['lastseen'], 'integer'); @@ -127,6 +127,12 @@ if ($type{0} === '~') { 'data' => $data, 'hostname' => $hostname, )); + + if (($old === false || $old['clientip'] !== $ip) && Module::isAvailable('locations')) { + // New, or ip changed (dynamic pool?), update subnetlicationid + Location::updateMapIpToLocation($uuid, $ip); + } + // Write statistics data } else if ($type === '~runstate') { @@ -134,6 +140,10 @@ if ($type{0} === '~') { $sessionLength = 0; $used = Request::post('used', 0, 'integer'); if ($old === false) die("Unknown machine.\n"); + if ($old['clientip'] !== $ip) { + EventLog::warning("[runstate] IP address of client $uuid seems to have changed ({$old['clientip']} -> $ip)"); + die("Address changed.\n"); + } // Figure out what's happening if ($used === 0) { // Is not in use @@ -173,12 +183,18 @@ if ($type{0} === '~') { )); } } elseif ($type === '~poweroff') { - if ($old !== false && $old['logintime'] !== 0) { + if ($old === false) die("Unknown machine.\n"); + if ($old['clientip'] !== $ip) { + EventLog::warning("[poweroff] IP address of client $uuid seems to have changed ({$old['clientip']} -> $ip)"); + die("Address changed.\n"); + } + if ($old['logintime'] !== 0) { $sessionLength = $old['lastseen'] - $old['logintime']; if ($sessionLength > 0 && $sessionLength < 86400*2) { - Database::exec('INSERT INTO statistic (dateline, typeid, clientip, username, data)' - . " VALUES (:start, '~session-length', :clientip, '', :length)", array( + Database::exec('INSERT INTO statistic (dateline, typeid, machineuuid, clientip, username, data)' + . " VALUES (:start, '~session-length', :uuid, :clientip, '', :length)", array( 'start' => $old['logintime'], + 'uuid' => $uuid, 'clientip' => $ip, 'length' => $sessionLength )); diff --git a/modules-available/statistics/inc/filter.inc.php b/modules-available/statistics/inc/filter.inc.php index 03dba6c3..6af6eed1 100644 --- a/modules-available/statistics/inc/filter.inc.php +++ b/modules-available/statistics/inc/filter.inc.php @@ -214,13 +214,11 @@ class LocationFilter extends Filter { settype($this->argument, 'int'); if ($this->argument === 0) { - $joins[] = 'LEFT JOIN subnet s ON (INET_ATON(machine.clientip) BETWEEN s.startaddr AND s.endaddr)'; - return 'machine.locationid IS NULL AND s.locationid IS NULL'; + $neg = $this->operator === '=' ? '' : 'NOT'; + return "machine.locationid IS $neg NULL"; } else { - $joins[] = ' LEFT JOIN subnet ON (INET_ATON(clientip) BETWEEN startaddr AND endaddr AND machine.locationid IS NULL) '; $args['lid'] = $this->argument; - $neg = $this->operator == '=' ? '' : 'NOT'; - return "$neg ((subnet.locationid = :lid) OR (machine.locationid = :lid))"; + return "machine.locationid {$this->operator} :lid"; } } } diff --git a/modules-available/statistics/install.inc.php b/modules-available/statistics/install.inc.php index be07274c..0729d676 100644 --- a/modules-available/statistics/install.inc.php +++ b/modules-available/statistics/install.inc.php @@ -1,5 +1,8 @@