[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.

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 @@
 <?php
 
+// locationid trigger
+$addTrigger = false;
+
 $res = array();
 
 // The main statistic table used for log entries
@@ -23,7 +26,9 @@ $res[] = tableCreate('statistic', "
 
 $res[] = tableCreate('machine', "
   `machineuuid` char(36) CHARACTER SET ascii NOT NULL,
-  `locationid` int(11) DEFAULT NULL,
+  `fixedlocationid` int(11) DEFAULT NULL           COMMENT 'Manually set location (e.g. roomplanner)',
+  `subnetlocationid` int(11) DEFAULT NULL          COMMENT 'Automatically determined location (e.g. from subnet match),
+  `locationid` int(11) DEFAULT NULL                COMMENT 'Will be automatically set to fixedlocationid if not null, subnetlocationid otherwise',
   `macaddr` char(17) CHARACTER SET ascii NOT NULL,
   `clientip` varchar(45) CHARACTER SET ascii NOT NULL,
   `firstseen` int(10) unsigned NOT NULL,
@@ -66,6 +71,10 @@ $res[] = tableCreate('pciid', "
 	PRIMARY KEY (`category`,`id`)
 ");
 
+if (in_array(UPDATE_DONE, $res)) {
+	$addTrigger = true;
+}
+
 //
 // This was added/changed later -- keep update path
 //
@@ -112,6 +121,42 @@ if ($ret === false) {
 	finalResponse(UPDATE_FAILED, 'Expanding position column failed: ' . Database::lastError());
 }
 
+// 2016-12-06:
+// Add subnetlocationid - contains automatically determined location (by subnet)
+if (!tableHasColumn('machine', 'subnetlocationid')) {
+	$ret = Database::exec('ALTER TABLE machine'
+		. ' ADD COLUMN `subnetlocationid` int(11) DEFAULT NULL AFTER `machineuuid`');
+	if ($ret === false) {
+		finalResponse(UPDATE_FAILED, 'Adding subnetlocationid to machine failed: ' . Database::lastError());
+	}
+	$res[] = UPDATE_DONE;
+	$addTrigger = true;
+}
+// And fixedlocationid - manually set location, currently used by roomplanner
+if (!tableHasColumn('machine', 'fixedlocationid')) {
+	$ret = Database::exec('ALTER TABLE machine'
+		. ' ADD COLUMN `fixedlocationid` int(11) DEFAULT NULL AFTER `machineuuid`');
+	if ($ret === false) {
+		finalResponse(UPDATE_FAILED, 'Adding fixedlocationid to machine failed: ' . Database::lastError());
+	}
+	// Now copy over the values from locationid, since this was used before
+	Database::exec("UPDATE machine SET fixedlocationid = locationid");
+	$res[] = UPDATE_DONE;
+	$addTrigger = true;
+}
+// If any of these was added, create the trigger
+if ($addTrigger) {
+	$ret = Database::exec(" 
+	CREATE TRIGGER set_automatic_locationid
+		BEFORE UPDATE ON machine FOR EACH ROW
+	BEGIN
+		SET NEW.locationid = If(NEW.fixedlocationid IS NULL, NEW.subnetlocationid, NEW.fixedlocationid);
+	END");
+	if ($ret === false) {
+		finalResponse(UPDATE_FAILED, 'Adding locationid trigger to machine failed: ' . Database::lastError());
+	}
+}
+
 // Create response
 
 if (in_array(UPDATE_DONE, $res)) {
diff --git a/modules-available/statistics/page.inc.php b/modules-available/statistics/page.inc.php
index cc8acacc..6a9acd14 100644
--- a/modules-available/statistics/page.inc.php
+++ b/modules-available/statistics/page.inc.php
@@ -714,9 +714,6 @@ class Page_Statistics extends Page
 		unset($client['data']);
 		// Get locations
 		if (Module::isAvailable('locations')) {
-			if (is_null($client['locationid'])) {
-				$client['locationid'] = Location::getFromIp($client['clientip']);
-			}
 			$locs = Location::getLocationsAssoc();
 			$next = (int)$client['locationid'];
 			$output = array();