From 203a8fa7acc12bfc3debf6337362f3fb7900bab9 Mon Sep 17 00:00:00 2001 From: Simon Rettberg Date: Mon, 21 Dec 2015 18:00:38 +0100 Subject: [statistics] Add table definition, bump db version --- apis/clientlog.inc.php | 38 +++++++++++++++++++++++++++++--------- apis/cron.inc.php | 6 ++++++ apis/update.inc.php | 49 +++++++++++++++++++++++++++++++++++++++++++++++-- 3 files changed, 82 insertions(+), 11 deletions(-) (limited to 'apis') diff --git a/apis/clientlog.inc.php b/apis/clientlog.inc.php index 01a470f0..842e3469 100644 --- a/apis/clientlog.inc.php +++ b/apis/clientlog.inc.php @@ -31,6 +31,7 @@ if ($type{0} === '~') { } } $NOW = time(); + $old = Database::queryFirst('SELECT logintime, lastseen FROM machine WHERE machineuuid = :uuid', array('uuid' => $uuid)); // Handle event type if ($type === '~poweron') { // Poweron & hw stats @@ -45,6 +46,7 @@ if ($type{0} === '~') { $valid = array('UNKNOWN', 'UNSUPPORTED', 'DISABLED', 'ENABLED'); if (!in_array($kvmstate, $valid)) $kvmstate = 'UNKNOWN'; $cpumodel = Request::post('cpumodel', '', 'string'); + $systemmodel = Request::post('systemmodel', '', 'string'); $id44mb = Request::post('id44mb', 0, 'integer'); if ($id44mb < 0 || $id44mb > 10240000) $id44mb = 0; $badsectors = Request::post('badsectors', 0, 'integer'); @@ -54,29 +56,43 @@ if ($type{0} === '~') { $hostname = ''; } $data = Request::post('data', '', 'string'); - // See if we have a lingering session, create statistic entry if so if ($uptime < 120) { - $old = Database::queryFirst('SELECT logintime, lastseen FROM machine WHERE machineuuid = :uuid', array('uuid' => $uuid)); + // See if we have a lingering session, create statistic entry if so if ($old !== false && (int)$old['logintime'] !== 0) { $sessionLength = $old['lastseen'] - $old['logintime']; if ($sessionLength > 0) { $start = $old['logintime']; if ($start === 0) $start = $NOW; - 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' => $start, + 'uuid' => $uuid, 'clientip' => $ip, 'length' => $sessionLength )); } } + // Write poweroff period length to statistic table + if ($old !== false) { + $lastSeen = $old['lastseen'] + 300; + $offtime = ($NOW - $uptime) - $lastSeen; + if ($offtime > 600 && $offtime < 86400 * 90) { + Database::exec('INSERT INTO statistic (dateline, typeid, machineuuid, clientip, username, data)' + . " VALUES (:shutdown, '~offline-length', :uuid, :clientip, '', :length)", array( + 'shutdown' => $lastSeen, + 'uuid' => $uuid, + 'clientip' => $ip, + 'length' => $offtime + )); + } + } } // Create/update machine entry Database::exec('INSERT INTO machine ' . '(machineuuid, macaddr, clientip, firstseen, lastseen, logintime, position, lastboot, realcores, mbram,' . ' kvmstate, cpumodel, id44mb, badsectors, data, hostname) VALUES ' . "(:uuid, :macaddr, :clientip, :firstseen, :lastseen, 0, '', :lastboot, :realcores, :mbram," - . ' :kvmstate, :cpumodel, :id44mb, :badsectors, :data, :hostname)' + . ' :kvmstate, :cpumodel, :systemmodel, :id44mb, :badsectors, :data, :hostname)' . ' ON DUPLICATE KEY UPDATE' . ' macaddr = VALUES(macaddr),' . ' clientip = VALUES(clientip),' @@ -87,6 +103,7 @@ if ($type{0} === '~') { . ' mbram = VALUES(mbram),' . ' kvmstate = VALUES(kvmstate),' . ' cpumodel = VALUES(cpumodel),' + . ' systemmodel = VALUES(systemmodel),' . ' id44mb = VALUES(id44mb),' . ' badsectors = VALUES(badsectors),' . ' data = VALUES(data),' @@ -101,16 +118,18 @@ if ($type{0} === '~') { 'mbram' => $mbram, 'kvmstate' => $kvmstate, 'cpumodel' => $cpumodel, + 'systemmodel'=> $systemmodel, 'id44mb' => $id44mb, 'badsectors' => $badsectors, 'data' => $data, 'hostname' => $hostname, )); + // Write statistics data + } else if ($type === '~runstate') { // Usage (occupied/free) $sessionLength = 0; $used = Request::post('used', 0, 'integer'); - $old = Database::queryFirst('SELECT logintime, lastseen FROM machine WHERE machineuuid = :uuid', array('uuid' => $uuid)); if ($old === false) die("Unknown machine.\n"); settype($old['logintime'], 'integer'); settype($old['lastseen'], 'integer'); @@ -146,9 +165,10 @@ if ($type{0} === '~') { if ($sessionLength > 0) { $start = $old['logintime']; if ($start === 0) $start = $NOW; - 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' => $start, + 'uuid' => $uuid, 'clientip' => $ip, 'length' => $sessionLength )); @@ -161,7 +181,7 @@ if ($type{0} === '~') { $start = $old['logintime']; if ($start === 0) $start = $NOW; Database::exec('INSERT INTO statistic (dateline, typeid, clientip, username, data)' - . " VALUES (:start, '.session-length', :clientip, '', :length)", array( + . " VALUES (:start, '~session-length', :clientip, '', :length)", array( 'start' => $start, 'clientip' => $ip, 'length' => $sessionLength diff --git a/apis/cron.inc.php b/apis/cron.inc.php index 3553abdb..318fb67f 100644 --- a/apis/cron.inc.php +++ b/apis/cron.inc.php @@ -23,6 +23,12 @@ case 3: case 4: Database::exec("DELETE FROM callback WHERE (UNIX_TIMESTAMP() - dateline) > 86400"); break; +case 5: + Database::exec("DELETE FROM statistic WHERE (UNIX_TIMESTAMP() - dateline) > 86400 * 90"); + break; +case 6: + Database::exec("DELETE FROM machine WHERE (UNIX_TIMESTAMP() - lastseen) > 86400 * 365"); + break; } Trigger::checkCallbacks(); diff --git a/apis/update.inc.php b/apis/update.inc.php index 134e7ad8..a7212961 100644 --- a/apis/update.inc.php +++ b/apis/update.inc.php @@ -226,7 +226,7 @@ function update_8() // ####################### // ##### 2015-05-21 -// Add statistics table, for future logging of eg. +// Add statistics table, for logging of session length and idle times function update_9() { Database::exec("CREATE TABLE IF NOT EXISTS `statistic` ( @@ -242,4 +242,49 @@ function update_9() KEY `clientip` (`clientip`,`dateline`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8"); return true; -} \ No newline at end of file +} + +// ####################### +// ##### 2015-12-21 +// Add machine uuid column to statistics table +function update_10() +{ + if (!tableHasColumn('statistic', 'machineuuid')) { + Database::exec('ALTER TABLE statistic' + . ' ADD COLUMN `machineuuid` varchar(36) CHARACTER SET ascii DEFAULT NULL AFTER clientip,' + . ' ADD INDEX `machineuuid` (`machineuuid`,`dateline`)'); + } + Database::exec("CREATE TABLE IF NOT EXISTS `machine` ( + `machineuuid` char(36) CHARACTER SET ascii NOT NULL, + `roomid` int(10) unsigned DEFAULT NULL, + `macaddr` char(17) CHARACTER SET ascii NOT NULL, + `clientip` varchar(45) CHARACTER SET ascii NOT NULL, + `firstseen` int(10) unsigned NOT NULL, + `lastseen` int(10) unsigned NOT NULL, + `logintime` int(10) unsigned NOT NULL, + `position` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, + `lastboot` int(10) unsigned NOT NULL, + `realcores` smallint(5) unsigned NOT NULL, + `mbram` int(10) unsigned NOT NULL, + `kvmstate` enum('UNKNOWN','UNSUPPORTED','DISABLED','ENABLED') NOT NULL, + `cpumodel` varchar(120) NOT NULL, + `systemmodel` varchar(120) NOT NULL DEFAULT '', + `id44mb` int(10) unsigned NOT NULL, + `badsectors` int(10) unsigned NOT NULL, + `data` mediumtext NOT NULL, + `hostname` varchar(200) NOT NULL DEFAULT '', + `notes` text, + PRIMARY KEY (`machineuuid`), + KEY `macaddr` (`macaddr`), + KEY `clientip` (`clientip`), + KEY `realcores` (`realcores`), + KEY `mbram` (`mbram`), + KEY `kvmstate` (`kvmstate`), + KEY `id44mb` (`id44mb`), + KEY `roomid` (`roomid`), + KEY `lastseen` (`lastseen`), + KEY `cpumodel` (`cpumodel`), + KEY `systemmodel` (`systemmodel`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8"); + return true; +} -- cgit v1.2.3-55-g7522