From 23d8c6fc0c3b031aa081ee1a245f9b0792514fd3 Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Tue, 28 Nov 2017 16:41:33 +0100
Subject: [statistics] Introduce state column to get rid of complicated state
 determination

This also adds support for the STANDBY state
---
 .../locationinfo/inc/infopanel.inc.php             |   4 +-
 .../locationinfo/inc/locationinfo.inc.php          |  12 +-
 .../locationinfo/templates/frontend-default.html   |   4 +-
 .../rebootcontrol/inc/rebootqueries.inc.php        |  10 +-
 .../runmode/templates/machine-selector.html        |  15 +-
 modules-available/statistics/api.inc.php           | 190 +++++++++++++++------
 modules-available/statistics/hooks/cron.inc.php    |  18 +-
 modules-available/statistics/inc/filter.inc.php    |  20 +--
 modules-available/statistics/install.inc.php       |  20 ++-
 modules-available/statistics/page.inc.php          |  30 +---
 .../statistics/templates/clientlist.html           |  17 +-
 .../statistics/templates/machine-main.html         |  15 +-
 12 files changed, 226 insertions(+), 129 deletions(-)

diff --git a/modules-available/locationinfo/inc/infopanel.inc.php b/modules-available/locationinfo/inc/infopanel.inc.php
index 66ee0ae7..c612e518 100644
--- a/modules-available/locationinfo/inc/infopanel.inc.php
+++ b/modules-available/locationinfo/inc/infopanel.inc.php
@@ -81,7 +81,7 @@ class InfoPanel
 		}
 
 		$positionCol = $withPosition ? 'm.position,' : '';
-		$query = "SELECT m.locationid, m.machineuuid, $positionCol m.logintime, m.lastseen, m.lastboot FROM machine m
+		$query = "SELECT m.locationid, m.machineuuid, $positionCol m.logintime, m.lastseen, m.lastboot, m.state FROM machine m
 				WHERE m.locationid IN (:idlist)";
 		$dbquery = Database::simpleQuery($query, array('idlist' => $idList));
 
@@ -107,7 +107,7 @@ class InfoPanel
 				}
 			}
 			$pc['pcState'] = LocationInfo::getPcState($row);
-			//$pc['pcState'] = ['BROKEN', 'OFF', 'IDLE', 'OCCUPIED'][mt_rand(0,3)]; // XXX
+			//$pc['pcState'] = ['BROKEN', 'OFFLINE', 'IDLE', 'OCCUPIED', 'STANDBY'][mt_rand(0,4)]; // XXX
 
 			// Add the array to the machines list.
 			$array[$row['locationid']]['machines'][] = $pc;
diff --git a/modules-available/locationinfo/inc/locationinfo.inc.php b/modules-available/locationinfo/inc/locationinfo.inc.php
index 830fb050..933eaf4d 100644
--- a/modules-available/locationinfo/inc/locationinfo.inc.php
+++ b/modules-available/locationinfo/inc/locationinfo.inc.php
@@ -6,7 +6,7 @@ class LocationInfo
 	/**
 	 * Gets the pc data and returns it's state.
 	 *
-	 * @param array $pc The pc data from the db. Array('logintime' =>, 'lastseen' =>, 'lastboot' =>)
+	 * @param array $pc The pc data from the db. Array('state' => xx, 'lastseen' => xxx)
 	 * @return int pc state
 	 */
 	public static function getPcState($pc)
@@ -16,16 +16,10 @@ class LocationInfo
 		$lastboot = (int)$pc['lastboot'];
 		$NOW = time();
 
-		if ($NOW - $lastseen > 14 * 86400) {
+		if ($pc['state'] === 'OFFLINE' && $NOW - $lastseen > 21 * 86400) {
 			return "BROKEN";
-		} elseif (($NOW - $lastseen > 610) || $lastboot === 0) {
-			return "OFF";
-		} elseif ($logintime === 0) {
-			return "IDLE";
-		} elseif ($logintime > 0) {
-			return "OCCUPIED";
 		}
-		return -1;
+		return $pc['state'];
 	}
 
 	/**
diff --git a/modules-available/locationinfo/templates/frontend-default.html b/modules-available/locationinfo/templates/frontend-default.html
index fc9c3eac..e199fc36 100755
--- a/modules-available/locationinfo/templates/frontend-default.html
+++ b/modules-available/locationinfo/templates/frontend-default.html
@@ -29,7 +29,7 @@ optional:
 	<link rel='stylesheet' type='text/css' href='{{dirprefix}}modules/js_jqueryui/style.css'/>
 	<link rel='stylesheet' type='text/css' href='{{dirprefix}}modules/js_weekcalendar/style.css'/>
 
-	<style type='text/css'>
+	<style type="text/css">
 
 		body {
 			margin: 0;
@@ -1575,7 +1575,7 @@ optional:
 		for (var i = 0; i < update.length; i++) {
 			var $div = $("#pc_" + room.id + "_" + update[i].id);
 			// Pc free
-			if (update[i].pcState === "IDLE" || update[i].pcState === "OFF") {
+			if (update[i].pcState === "IDLE" || update[i].pcState === "OFFLINE" || update[i].pcState === "STANDBY") {
 				freePcs++;
 			}
 
diff --git a/modules-available/rebootcontrol/inc/rebootqueries.inc.php b/modules-available/rebootcontrol/inc/rebootqueries.inc.php
index 8f65b756..1bdcb9a2 100644
--- a/modules-available/rebootcontrol/inc/rebootqueries.inc.php
+++ b/modules-available/rebootcontrol/inc/rebootqueries.inc.php
@@ -21,7 +21,7 @@ class RebootQueries
 		}
 		$res = Database::simpleQuery("
 			SELECT machine.machineuuid, machine.hostname, machine.clientip,
-				machine.lastboot, machine.lastseen, machine.logintime,
+				machine.lastboot, machine.lastseen, machine.logintime, machine.state,
 				$sessionField, machine.currentuser, machine.locationid
 			FROM machine 
 			$leftJoin
@@ -29,12 +29,12 @@ class RebootQueries
 		$ret = $res->fetchAll(PDO::FETCH_ASSOC);
 		$NOW = time();
 		foreach ($ret as &$row) {
-			if ($row['lastboot'] == 0 || $NOW - $row['lastseen'] > 600) {
-				$row['status'] = 0;
-			} else {
+			if ($row['state'] === 'IDLE' || $row['state'] === 'OCCUPIED') {
 				$row['status'] = 1;
+			} else {
+				$row['status'] = 0;
 			}
-			if ($row['status'] === 0 || $row['logintime'] == 0) {
+			if ($row['state'] !== 'OCCUPIED') {
 				$row['currentuser'] = '';
 				$row['currentsession'] = '';
 			}
diff --git a/modules-available/runmode/templates/machine-selector.html b/modules-available/runmode/templates/machine-selector.html
index d3ff7378..7f37f5a2 100644
--- a/modules-available/runmode/templates/machine-selector.html
+++ b/modules-available/runmode/templates/machine-selector.html
@@ -108,11 +108,22 @@ function renderMachineSelected(item, escape) {
 }
 
 document.addEventListener('DOMContentLoaded', function () {
+	Selectize.define("no_bs", function (options) {
+		var original = this.deleteSelection;
+		this.deleteSelection = (function() {
+			return function (e) {
+				if (!e || e.keyCode !== 8) {
+					return original.apply(this, arguments);
+				}
+				return false;
+			};
+		})();
+	});
 	var old = {{{machines}}} || [];
 	var $box = $('#machine-sel').selectize({
 		options: old,
 		items: old.map(function(x) { return x.machineuuid; }),
-		plugins: ["remove_button"],
+		plugins: ["remove_button", "no_bs"],
 		valueField: 'machineuuid',
 		searchField: "combined",
 		openOnFocus: false,
@@ -121,7 +132,7 @@ document.addEventListener('DOMContentLoaded', function () {
 		load: loadMachines,
 		maxItems: null,
 		sortField: 'hostname',
-		sortDirection: 'asc'
+		sortDirection: 'asc',
 	});
 });
 
diff --git a/modules-available/statistics/api.inc.php b/modules-available/statistics/api.inc.php
index 022277fe..d75f4af8 100644
--- a/modules-available/statistics/api.inc.php
+++ b/modules-available/statistics/api.inc.php
@@ -34,7 +34,7 @@ if ($type{0} === '~') {
 	// External mode of operation?
 	$mode = Request::post('mode', false, 'string');
 	$NOW = time();
-	$old = Database::queryFirst('SELECT clientip, logintime, lastseen, lastboot, mbram, cpumodel FROM machine WHERE machineuuid = :uuid', array('uuid' => $uuid));
+	$old = Database::queryFirst('SELECT clientip, logintime, lastseen, lastboot, state, mbram, cpumodel FROM machine WHERE machineuuid = :uuid', array('uuid' => $uuid));
 	if ($old !== false) {
 		settype($old['logintime'], 'integer');
 		settype($old['lastseen'], 'integer');
@@ -43,7 +43,7 @@ if ($type{0} === '~') {
 	// Handle event type
 	if ($mode === false && $type === '~poweron') {
 		// Poweron & hw stats
-		$uptime = Request::post('uptime', '', 'integer');
+		$uptime = Request::post('uptime', 0, 'integer');
 		if (strlen($macaddr) > 17) die("Invalid MAC.\n");
 		if ($uptime < 0 || $uptime > 4000000) die("Implausible uptime.\n");
 		$realcores = Request::post('realcores', 0, 'integer');
@@ -64,6 +64,66 @@ if ($type{0} === '~') {
 			$hostname = '';
 		}
 		$data = Request::post('data', '', 'string');
+		// Prepare insert/update to machine table
+		$new = array(
+			'uuid'       => $uuid,
+			'macaddr'    => $macaddr,
+			'clientip'   => $ip,
+			'lastseen'   => $NOW,
+			'lastboot'   => $NOW - $uptime,
+			'realcores'  => $realcores,
+			'mbram'      => $mbram,
+			'kvmstate'   => $kvmstate,
+			'cpumodel'   => $cpumodel,
+			'systemmodel'=> $systemmodel,
+			'id44mb'     => $id44mb,
+			'badsectors' => $badsectors,
+			'data'       => $data,
+			'hostname'   => $hostname,
+			'state'      => 'IDLE',
+		);
+		// Create/update machine entry
+		if ($old === false) {
+			$new['firstseen'] = $NOW;
+			$new['hostname']   = $hostname;
+			$res = Database::exec('INSERT INTO machine '
+				. '(machineuuid, macaddr, clientip, firstseen, lastseen, logintime, position, lastboot, realcores, mbram,'
+				. ' kvmstate, cpumodel, systemmodel, id44mb, badsectors, data, hostname, state) VALUES '
+				. "(:uuid, :macaddr, :clientip, :firstseen, :lastseen, 0, '', :lastboot, :realcores, :mbram,"
+				. ' :kvmstate, :cpumodel, :systemmodel, :id44mb, :badsectors, :data, :hostname, :state)', $new, true);
+			if ($res === false) {
+				die("Concurrent insert, ignored. (RESULT=0)\n");
+			}
+		} else {
+			// Update
+			$moresql = ($uptime < 180 ? ' logintime = 0, currentuser = NULL, currentsession = NULL,' : '');
+			if (!empty($hostname)) {
+				$new['hostname']   = $hostname;
+				$moresql .= ' hostname = :hostname,';
+			}
+			$new['oldstate'] = $old['state'];
+			$new['oldlastseen'] = $old['lastseen'];
+			$res = Database::exec('UPDATE machine SET '
+				. ' macaddr = :macaddr,'
+				. ' clientip = :clientip,'
+				. ' lastseen = :lastseen,'
+				. ' lastboot = :lastboot,'
+				. $moresql
+				. ' realcores = :realcores,'
+				. ' mbram = :mbram,'
+				. ' kvmstate = :kvmstate,'
+				. ' cpumodel = :cpumodel,'
+				. ' systemmodel = :systemmodel,'
+				. ' id44mb = :id44mb,'
+				. ' badsectors = :badsectors,'
+				. ' data = :data,'
+				. ' state = :state    '
+				. " WHERE machineuuid = :uuid AND state = :oldstate AND lastseen = :oldlastseen", $new);
+			if ($res === 0) {
+				die("Concurrent update, ignored. (RESULT=0)\n");
+			}
+		}
+		// Maybe log old crashed session
 		if ($uptime < 120) {
 			// See if we have a lingering session, create statistic entry if so
 			if ($old !== false && $old['logintime'] !== 0) {
@@ -93,44 +153,6 @@ if ($type{0} === '~') {
 				}
 			}
 		}
-		$new = array(
-			'uuid'       => $uuid,
-			'macaddr'    => $macaddr,
-			'clientip'   => $ip,
-			'firstseen'  => $NOW,
-			'lastseen'   => $NOW,
-			'lastboot'   => $NOW - $uptime,
-			'realcores'  => $realcores,
-			'mbram'      => $mbram,
-			'kvmstate'   => $kvmstate,
-			'cpumodel'   => $cpumodel,
-			'systemmodel'=> $systemmodel,
-			'id44mb'     => $id44mb,
-			'badsectors' => $badsectors,
-			'data'       => $data,
-			'hostname'   => $hostname,
-		);
-		// Create/update machine entry
-		Database::exec('INSERT INTO machine '
-			. '(machineuuid, macaddr, clientip, firstseen, lastseen, logintime, position, lastboot, realcores, mbram,'
-			. ' kvmstate, cpumodel, systemmodel, id44mb, badsectors, data, hostname) VALUES '
-			. "(:uuid, :macaddr, :clientip, :firstseen, :lastseen, 0, '', :lastboot, :realcores, :mbram,"
-			. ' :kvmstate, :cpumodel, :systemmodel, :id44mb, :badsectors, :data, :hostname)'
-			. ' ON DUPLICATE KEY UPDATE'
-			. ' macaddr = VALUES(macaddr),'
-			. ' clientip = VALUES(clientip),'
-			. ' lastseen = VALUES(lastseen),'
-			. ($uptime < 180 ? ' logintime = 0, currentuser = NULL, currentsession = NULL,' : '')
-			. ' lastboot = VALUES(lastboot),'
-			. ' realcores = VALUES(realcores),'
-			. ' mbram = VALUES(mbram),'
-			. ' kvmstate = VALUES(kvmstate),'
-			. ' cpumodel = VALUES(cpumodel),'
-			. ' systemmodel = VALUES(systemmodel),'
-			. ' id44mb = VALUES(id44mb),'
-			. ' badsectors = VALUES(badsectors),'
-			. ' data = VALUES(data),'
-			. " hostname = If(VALUES(hostname) = '', hostname, VALUES(hostname))", $new);
 
 		if (($old === false || $old['clientip'] !== $ip) && Module::isAvailable('locations')) {
 			// New, or ip changed (dynamic pool?), update subnetlicationid
@@ -153,7 +175,7 @@ if ($type{0} === '~') {
 			die("Address changed.\n");
 		}
 		$used = Request::post('used', 0, 'integer');
-		if ($old['lastboot'] === 0 && $NOW - $old['lastseen'] > 300) {
+		if ($old['state'] === 'OFFLINE' && $NOW - $old['lastseen'] > 600) {
 			$strUpdateBoottime = ' lastboot = UNIX_TIMESTAMP(), ';
 		} else {
 			$strUpdateBoottime = '';
@@ -167,31 +189,42 @@ if ($type{0} === '~') {
 				}
 				$old['logintime'] = 0;
 			}
-			$old['lastboot'] = 0;
 		}
 		// Figure out what's happening - state changes
-		if ($used === 0 && $old['logintime'] !== 0) {
+		$params = array(
+			'uuid' => $uuid,
+			'oldlastseen' => $old['lastseen'],
+			'oldstate' => $old['state'],
+		);
+		if ($used === 0 && $old['state'] !== 'IDLE') {
 			// Is not in use, was in use before
 			$sessionLength = $NOW - $old['logintime'];
-			Database::exec('UPDATE machine SET lastseen = UNIX_TIMESTAMP(),'
+			$res = Database::exec('UPDATE machine SET lastseen = UNIX_TIMESTAMP(),'
 				. $strUpdateBoottime
-				. ' logintime = 0, currentuser = NULL WHERE machineuuid = :uuid', array('uuid' => $uuid));
-		} elseif ($used === 1 && $old['logintime'] === 0) {
+				. " logintime = 0, currentuser = NULL, state = 'IDLE' "
+				. " WHERE machineuuid = :uuid AND lastseen = :oldlastseen AND state = :oldstate",
+				$params);
+		} elseif ($used === 1 && $old['state'] !== 'OCCUPIED') {
 			// Machine is in use, was free before
 			if ($sessionLength !== 0 || $old['logintime'] === 0) {
 				// This event is a start of a new session, rather than an update
-				Database::exec('UPDATE machine SET lastseen = UNIX_TIMESTAMP(),'
+				$params['user'] = Request::post('user', null, 'string');
+				$res = Database::exec('UPDATE machine SET lastseen = UNIX_TIMESTAMP(),'
 					. $strUpdateBoottime
-					. ' logintime = UNIX_TIMESTAMP(), currentuser = :user WHERE machineuuid = :uuid', array(
-					'uuid' => $uuid,
-					'user' => Request::post('user', null, 'string'),
-				));
+					. " logintime = UNIX_TIMESTAMP(), currentuser = :user, state = 'OCCUPIED' "
+					. " WHERE machineuuid = :uuid AND lastseen = :oldlastseen AND state = :oldstate", $params);
+			} else {
+				$res = 0;
 			}
 		} else {
 			// Nothing changed, simple lastseen update
-			Database::exec('UPDATE machine SET '
+			$res = Database::exec('UPDATE machine SET '
 				. $strUpdateBoottime
-				. ' lastseen = UNIX_TIMESTAMP() WHERE machineuuid = :uuid', array('uuid' => $uuid));
+				. ' lastseen = UNIX_TIMESTAMP() WHERE machineuuid = :uuid AND lastseen = :oldlastseen AND state = :oldstate', $params);
+		}
+		// Did we update, or was there a concurrent update?
+		if ($res === 0) {
+			die("Concurrent update, ignored. (RESULT=0)\n");
 		}
 		// 9) Log last session length if applicable
 		if ($mode === false && $sessionLength > 0 && $sessionLength < 86400*2 && $old['logintime'] !== 0) {
@@ -221,8 +254,11 @@ if ($type{0} === '~') {
 				));
 			}
 		}
-		Database::exec('UPDATE machine SET logintime = 0, lastseen = UNIX_TIMESTAMP(), lastboot = 0 WHERE machineuuid = :uuid', array('uuid' => $uuid));
+		Database::exec("UPDATE machine SET logintime = 0, lastseen = UNIX_TIMESTAMP(), state = 'OFFLINE'
+			WHERE machineuuid = :uuid AND state = :oldstate AND lastseen = :oldlastseen",
+			array('uuid' => $uuid, 'oldlastseen' => $old['lastseen'], 'oldstate' => $old['state']));
 	} elseif ($mode === false && $type === '~screens') {
+		if ($old === false) die("Unknown machine.\n");
 		$screens = Request::post('screen', false, 'array');
 		if (is_array($screens)) {
 			// `devicetype`, `devicename`, `subid`, `machineuuid`
@@ -293,6 +329,47 @@ if ($type{0} === '~') {
 
 			}
 		}
+	} else if ($type === '~suspend') {
+		// Client entering suspend
+		if ($old === false) die("Unknown machine.\n");
+		if ($old['clientip'] !== $ip) {
+			EventLog::warning("[suspend] IP address of client $uuid seems to have changed ({$old['clientip']} -> $ip)");
+			die("Address changed.\n");
+		}
+		if ($NOW - $old['lastseen'] < 610 && $old['state'] !== 'OFFLINE') {
+			Database::exec("UPDATE machine SET lastseen = UNIX_TIMESTAMP(), state = 'STANDBY'
+				WHERE machineuuid = :uuid AND state = :oldstate AND lastseen = :oldlastseen",
+				array('uuid' => $uuid, 'oldlastseen' => $old['lastseen'], 'oldstate' => $old['state']));
+		} else {
+			EventLog::info("[suspend] Client $uuid reported switch to standby when it wasn't powered on first. Was: " . $old['state']);
+		}
+	} else if ($type === '~resume') {
+		// Waking up from suspend
+		if ($old === false) die("Unknown machine.\n");
+		if ($old['clientip'] !== $ip) {
+			EventLog::info("[resume] IP address of client $uuid seems to have changed ({$old['clientip']} -> $ip), allowed on resume.");
+		}
+		if ($old['state'] === 'STANDBY') {
+			$res = Database::exec("UPDATE machine SET state = 'IDLE', clientip = :ip, lastseen = UNIX_TIMESTAMP()
+				WHERE machineuuid = :uuid AND state = :oldstate AND lastseen = :oldlastseen",
+				array('uuid' => $uuid, 'ip' => $ip, 'oldlastseen' => $old['lastseen'], 'oldstate' => $old['state']));
+			// Write standby period length to statistic table
+			if ($mode === false && $res > 0 && $old['lastseen'] !== 0) {
+				$lastSeen = $old['lastseen'];
+				$duration = $NOW - $lastSeen;
+				if ($duration > 500 && $duration < 86400 * 14) {
+					Database::exec('INSERT INTO statistic (dateline, typeid, machineuuid, clientip, username, data)'
+						. " VALUES (:suspend, '~suspend-length', :uuid, :clientip, '', :length)", array(
+						'suspend' => $lastSeen,
+						'uuid'     => $uuid,
+						'clientip' => $ip,
+						'length'   => $duration
+					));
+				}
+			}
+		} else {
+			EventLog::info("[resume] Client $uuid reported wakeup from standby when it wasn't logged as being in standby. Was: " . $old['state']);
+		}
 	} else {
 		die("INVALID ACTION '$type'\n");
 	}
@@ -321,6 +398,7 @@ function writeStatisticLog($type, $username, $data)
 	));
 }
 
+
 // For backwards compat, we require the . prefix
 if ($type{0} === '.') {
 	if ($type === '.vmchooser-session') {
@@ -352,10 +430,10 @@ function checkHardwareChange($old, $new)
 		if ($new['mbram'] + 1000 < $old['mbram']) {
 			$ram1 = round($old['mbram'] / 512) / 2;
 			$ram2 = round($new['mbram'] / 512) / 2;
-			EventLog::warning('Client ' . $new['uuid'] . ' (' . $new['clientip'] . "): RAM decreased from {$ram1}GB to {$ram2}GB");
+			EventLog::warning('[poweron] Client ' . $new['uuid'] . ' (' . $new['clientip'] . "): RAM decreased from {$ram1}GB to {$ram2}GB");
 		}
 		if (!empty($old['cpumodel']) && !empty($new['cpumodel']) && $new['cpumodel'] !== $old['cpumodel']) {
-			EventLog::warning('Client ' . $new['uuid'] . ' (' . $new['clientip'] . "): CPU changed from '{$old['cpumodel']}' to '{$new['cpumodel']}'");
+			EventLog::warning('[poweron] Client ' . $new['uuid'] . ' (' . $new['clientip'] . "): CPU changed from '{$old['cpumodel']}' to '{$new['cpumodel']}'");
 		}
 	}
 }
diff --git a/modules-available/statistics/hooks/cron.inc.php b/modules-available/statistics/hooks/cron.inc.php
index 575ab6ba..eb88173d 100644
--- a/modules-available/statistics/hooks/cron.inc.php
+++ b/modules-available/statistics/hooks/cron.inc.php
@@ -1,18 +1,28 @@
 <?php
 
-function logstats() {
+function logstats()
+{
 	$NOW = time();
 	$cutoff = $NOW - 86400 * 30;
-	$online = $NOW - 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");
+	$on = Database::queryFirst("SELECT Count(*) AS val FROM machine WHERE state IN ('IDLE', 'OCCUPIED')");
+	$used = Database::queryFirst("SELECT Count(*) AS val FROM machine WHERE state = 'OCCUPIED'");
 	Database::exec("INSERT INTO statistic (dateline, typeid, clientip, username, data) VALUES (:now, '~stats', '', '', :vals)", array(
 		'now' => $NOW,
 		'vals' => $known['val'] . '#' . $on['val'] . '#' . $used['val'],
 	));
 }
 
+function state_cleanup()
+{
+	// Fix online state of machines that crashed
+	$standby = time() - 86400 * 2; // Reset standby machines after two days
+	$on = time() - 610; // Reset others after ~10 minutes
+	Database::exec("UPDATE machine SET state = 'OFFLINE' WHERE lastseen < If(state = 'STANDBY', $standby, $on) AND state <> 'OFFLINE'");
+}
+
+state_cleanup();
+
 logstats();
 
 if (mt_rand(1, 10) === 1) {
diff --git a/modules-available/statistics/inc/filter.inc.php b/modules-available/statistics/inc/filter.inc.php
index 0afce572..b16bd9fb 100644
--- a/modules-available/statistics/inc/filter.inc.php
+++ b/modules-available/statistics/inc/filter.inc.php
@@ -186,15 +186,13 @@ class StateFilter extends Filter
 
 	public function whereClause(&$args, &$joins)
 	{
+		$map = [ 'on' => ['IDLE', 'OCCUPIED'], 'off' => ['OFFLINE'], 'idle' => ['IDLE'], 'occupied' => ['OCCUPIED'], 'standby' => ['STANDBY'] ];
 		$neg = $this->operator == '!=' ? 'NOT ' : '';
-		if ($this->argument === 'on') {
-			return " $neg (lastseen + 600 > UNIX_TIMESTAMP() ) ";
-		} elseif ($this->argument === 'off') {
-			return " $neg (lastseen + 600 < UNIX_TIMESTAMP() ) ";
-		} elseif ($this->argument === 'idle') {
-			return " $neg (lastseen + 600 > UNIX_TIMESTAMP() AND logintime = 0 ) ";
-		} elseif ($this->argument === 'occupied') {
-			return " $neg (lastseen + 600 > UNIX_TIMESTAMP() AND logintime <> 0 ) ";
+		if (array_key_exists($this->argument, $map)) {
+			global $unique_key;
+			$key = $this->column . '_arg' . ($unique_key++);
+			$args[$key] = $map[$this->argument];
+			return " machine.state $neg IN ( :$key ) ";
 		} else {
 			Message::addError('invalid-filter-argument', 'state', $this->argument);
 			return ' 1';
@@ -216,8 +214,10 @@ class LocationFilter extends Filter
 			$neg = $this->operator === '=' ? '' : 'NOT';
 			return "machine.locationid IS $neg NULL";
 		} else {
-			$args['lid'] = $this->argument;
-			return "machine.locationid {$this->operator} :lid";
+			global $unique_key;
+			$key = $this->column . '_arg' . ($unique_key++);
+			$args[$key] = $this->argument;
+			return "machine.locationid {$this->operator} :$key";
 		}
 	}
 }
diff --git a/modules-available/statistics/install.inc.php b/modules-available/statistics/install.inc.php
index bfa342c4..4e2dfcca 100644
--- a/modules-available/statistics/install.inc.php
+++ b/modules-available/statistics/install.inc.php
@@ -36,6 +36,7 @@ $res[] = $machineCreate = tableCreate('machine', "
   `logintime` int(10) unsigned NOT NULL,
   `position` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
   `lastboot` int(10) unsigned NOT NULL,
+  `state` enum('OFFLINE', 'IDLE', 'OCCUPIED', 'STANDBY', 'IGNORED') NOT NULL DEFAULT 'OFFLINE',
   `realcores` smallint(5) unsigned NOT NULL,
   `mbram` int(10) unsigned NOT NULL,
   `kvmstate` enum('UNKNOWN','UNSUPPORTED','DISABLED','ENABLED') NOT NULL,
@@ -51,6 +52,7 @@ $res[] = $machineCreate = tableCreate('machine', "
   PRIMARY KEY (`machineuuid`),
   KEY `macaddr` (`macaddr`),
   KEY `clientip` (`clientip`),
+  KEY `state` (`state`),
   KEY `realcores` (`realcores`),
   KEY `mbram` (`mbram`),
   KEY `kvmstate` (`kvmstate`),
@@ -198,6 +200,7 @@ if ($addTrigger) {
 			finalResponse(UPDATE_RETRY, 'Locations module not installed yet, retry later');
 		}
 	}
+	$res[] = UPDATE_DONE;
 }
 
 if ($machineHwCreate === UPDATE_DONE) {
@@ -217,12 +220,19 @@ if ($machineHwCreate === UPDATE_DONE) {
 	if ($ret === false) {
 		finalResponse(UPDATE_FAILED, 'Adding constraint to statistic_hw_prop failed: ' . Database::lastError());
 	}
+	$res[] = UPDATE_DONE;
 }
 
-// Create response
-
-if (in_array(UPDATE_DONE, $res)) {
-	finalResponse(UPDATE_DONE, 'Tables created successfully');
+// 2017-11-27: Add state column
+if (!tableHasColumn('machine', 'state')) {
+	$ret = Database::exec("ALTER TABLE `machine`
+		ADD COLUMN `state` enum('OFFLINE', 'IDLE', 'OCCUPIED', 'STANDBY', 'IGNORED') NOT NULL DEFAULT 'OFFLINE' AFTER `lastboot`,
+		ADD INDEX `state` (`state`)");
+	if ($ret === false) {
+		finalResponse(UPDATE_FAILED, 'Adding state column to machine table failed: ' . Database::lastError());
+	}
+	$res[] = UPDATE_DONE;
 }
 
-finalResponse(UPDATE_NOOP, 'Everything already up to date');
+// Create response
+responseFromArray($res);
diff --git a/modules-available/statistics/page.inc.php b/modules-available/statistics/page.inc.php
index 575c5c19..4919a65f 100644
--- a/modules-available/statistics/page.inc.php
+++ b/modules-available/statistics/page.inc.php
@@ -196,8 +196,6 @@ class Page_Statistics extends Page
 		} elseif ($action === 'addprojector' || $action === 'delprojector') {
 			$this->handleProjector($action);
 		}
-		// Fix online state of machines that crashed -- TODO: Make cronjob for this
-		Database::exec("UPDATE machine SET lastboot = 0 WHERE lastseen < UNIX_TIMESTAMP() - 610");
 	}
 
 	protected function doRender()
@@ -333,8 +331,8 @@ class Page_Statistics extends Page
 		if ($known['val'] == 1) {
 			$this->redirectFirst($where, $join, $args);
 		}
-		$on = Database::queryFirst("SELECT Count(*) AS val FROM machine $join WHERE lastboot <> 0 AND ($where)", $args);
-		$used = Database::queryFirst("SELECT Count(*) AS val FROM machine $join WHERE lastboot <> 0 AND logintime <> 0 AND ($where)", $args);
+		$on = Database::queryFirst("SELECT Count(*) AS val FROM machine $join WHERE state IN ('IDLE', 'OCCUPIED') AND ($where)", $args);
+		$used = Database::queryFirst("SELECT Count(*) AS val FROM machine $join WHERE state = 'OCCUPIED' AND ($where)", $args);
 		$hdd = Database::queryFirst("SELECT Count(*) AS val FROM machine $join WHERE badsectors >= 10 AND ($where)", $args);
 		if ($on['val'] != 0) {
 			$usedpercent = round($used['val'] / $on['val'] * 100);
@@ -576,8 +574,8 @@ class Page_Statistics extends Page
 		if ($filterSet->isNoId44Filter()) {
 			$xtra = ', data';
 		}
-		$res = Database::simpleQuery('SELECT machineuuid, macaddr, clientip, firstseen, lastseen,'
-			. ' logintime, lastboot, realcores, mbram, kvmstate, cpumodel, id44mb, hostname, notes IS NOT NULL AS hasnotes,'
+		$res = Database::simpleQuery('SELECT machineuuid, macaddr, clientip, lastseen,'
+			. ' logintime, state, realcores, mbram, kvmstate, cpumodel, id44mb, hostname, notes IS NOT NULL AS hasnotes,'
 			. ' badsectors ' . $xtra . ' FROM machine'
 			. " $join WHERE $where $sort", $args);
 		$rows = array();
@@ -589,13 +587,7 @@ class Page_Statistics extends Page
 			} else {
 				$singleMachine = false;
 			}
-			if ($row['lastboot'] == 0) {
-				$row['state_off'] = true;
-			} elseif ($row['logintime'] == 0) {
-				$row['state_idle'] = true;
-			} else {
-				$row['state_occupied'] = true;
-			}
+			$row['state_' . $row['state']] = 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']);
@@ -728,7 +720,7 @@ class Page_Statistics extends Page
 
 	private function showMachine($uuid)
 	{
-		$client = Database::queryFirst('SELECT machineuuid, locationid, macaddr, clientip, firstseen, lastseen, logintime, lastboot,'
+		$client = Database::queryFirst('SELECT machineuuid, locationid, macaddr, clientip, firstseen, lastseen, logintime, lastboot, state,'
 			. ' mbram, kvmstate, cpumodel, id44mb, data, hostname, currentuser, currentsession, notes FROM machine WHERE machineuuid = :uuid',
 			array('uuid' => $uuid));
 		// Hack: Get raw collected data
@@ -738,12 +730,8 @@ class Page_Statistics extends Page
 		}
 		// Mangle fields
 		$NOW = time();
-		if ($client['lastboot'] == 0) {
-			$client['state_off'] = true;
-		} elseif ($client['logintime'] == 0) {
-			$client['state_idle'] = true;
-		} else {
-			$client['state_occupied'] = true;
+		$client['state_' . $client['state']] = true;
+		if ($client['state'] === 'OCCUPIED') {
 			$this->fillSessionInfo($client);
 		}
 		$client['firstseen_s'] = date('d.m.Y H:i', $client['firstseen']);
@@ -753,7 +741,7 @@ class Page_Statistics extends Page
 		} else {
 			$uptime = $NOW - $client['lastboot'];
 			$client['lastboot_s'] = date('d.m.Y H:i', $client['lastboot']);
-			if (!isset($client['state_off']) || !$client['state_off']) {
+			if ($client['state'] === 'IDLE' || $client['state'] === 'OCCUPIED') {
 				$client['lastboot_s'] .= ' (Up ' . floor($uptime / 86400) . 'd ' . gmdate('H:i', $uptime) . ')';
 			}
 		}
diff --git a/modules-available/statistics/templates/clientlist.html b/modules-available/statistics/templates/clientlist.html
index 73148eb8..4755c3c1 100644
--- a/modules-available/statistics/templates/clientlist.html
+++ b/modules-available/statistics/templates/clientlist.html
@@ -60,15 +60,18 @@
 	<tr>
 		<td class="text-nowrap">
 			{{#hasnotes}}<span class="glyphicon glyphicon-exclamation-sign pull-right"></span>{{/hasnotes}}
-			{{#state_off}}
+			{{#state_OFFLINE}}
 			<span class="glyphicon glyphicon-off" title="{{lang_machineOff}}"></span>
-			{{/state_off}}
-			{{#state_idle}}
+			{{/state_OFFLINE}}
+			{{#state_IDLE}}
 			<span class="glyphicon glyphicon-ok green" title="{{lang_machineIdle}}"></span>
-			{{/state_idle}}
-			{{#state_occupied}}
+			{{/state_IDLE}}
+			{{#state_OCCUPIED}}
 			<span class="glyphicon glyphicon-user red" title="{{lang_machineOccupied}}"></span>
-			{{/state_occupied}}
+			{{/state_OCCUPIED}}
+			{{#state_STANDBY}}
+			<span class="glyphicon glyphicon-off green" title="{{lang_machineStandby}}"></span>
+			{{/state_STANDBY}}
 			<a href="?do=Statistics&amp;uuid={{machineuuid}}"><b>{{hostname}}</b></a>
 			<div class="small">{{machineuuid}}</div>
 		</td>
@@ -126,4 +129,4 @@ function toggleButton(v) {
 	$queryForm.submit();
 }
 
-//--></script>
\ No newline at end of file
+//--></script>
diff --git a/modules-available/statistics/templates/machine-main.html b/modules-available/statistics/templates/machine-main.html
index 74df80c4..1854a03b 100644
--- a/modules-available/statistics/templates/machine-main.html
+++ b/modules-available/statistics/templates/machine-main.html
@@ -50,13 +50,13 @@
 					<tr>
 						<td class="text-nowrap">{{lang_usageState}}</td>
 						<td>
-							{{#state_off}}
+							{{#state_OFFLINE}}
 							<span class="glyphicon glyphicon-off"></span> {{lang_machineOff}}
-							{{/state_off}}
-							{{#state_idle}}
+							{{/state_OFFLINE}}
+							{{#state_IDLE}}
 							<span class="glyphicon glyphicon-ok green"></span> {{lang_machineIdle}}
-							{{/state_idle}}
-							{{#state_occupied}}
+							{{/state_IDLE}}
+							{{#state_OCCUPIED}}
 							{{#username}}
 							<span class="glyphicon glyphicon-user red"></span> {{lang_machineOccupiedBy}} <b>{{username}}</b>
 							{{/username}}
@@ -64,7 +64,10 @@
 							<span class="glyphicon glyphicon-user red"></span> {{lang_machineOccupied}}
 							{{/username}}
 							<div>{{logintime_s}}</div>
-							{{/state_occupied}}
+							{{/state_OCCUPIED}}
+							{{#state_STANDBY}}
+							<span class="glyphicon glyphicon-off green"></span> {{lang_machineStandby}}
+							{{/state_STANDBY}}
 							{{#session}}
 							<div>
 								{{#lectureid}}
-- 
cgit v1.2.3-55-g7522