diff options
Diffstat (limited to 'modules-available/statistics_reporting/inc/queries.inc.php')
-rw-r--r-- | modules-available/statistics_reporting/inc/queries.inc.php | 125 |
1 files changed, 87 insertions, 38 deletions
diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php index 58e9e63b..bafe80bc 100644 --- a/modules-available/statistics_reporting/inc/queries.inc.php +++ b/modules-available/statistics_reporting/inc/queries.inc.php @@ -13,29 +13,31 @@ class Queries } } - public static function getClientStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) + public static function getClientStatistics(int $from, int $to, int $lowerTimeBound = 0, int $upperTimeBound = 24): array { + $fromCutoff = $from - 86400 * 30; $res = Database::simpleQuery("SELECT m.machineuuid, m.hostname, m.clientip, m.locationid, m.firstseen -- , m.lastboot, m.logintime, m.state - FROM machine m WHERE firstseen <= $to"); // " WHERE lastseen >= :from", compact('from')); + FROM machine m WHERE firstseen <= $to AND lastseen > $fromCutoff"); // " WHERE lastseen >= :from", compact('from')); $machines = self::getStats3($res, $from, $to, $lowerTimeBound, $upperTimeBound); foreach ($machines as &$machine) { $machine['medianSessionLength'] = self::calcMedian($machine['sessions']); unset($machine['sessions']); - $machine['clientName'] = $machine['hostname'] ? $machine['hostname'] : $machine['clientip']; + $machine['clientName'] = $machine['hostname'] ?: $machine['clientip']; } return $machines; } - public static function getLocationStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) + public static function getLocationStatistics(int $from, int $to, int $lowerTimeBound = 0, int $upperTimeBound = 24): array { + $fromCutoff = $from - 86400 * 30; $res = Database::simpleQuery("SELECT m.machineuuid, m.hostname, m.clientip, m.locationid, m.firstseen -- , m.lastboot, m.logintime, m.state - FROM machine m WHERE firstseen <= $to"); // " WHERE lastseen >= :from", compact('from')); + FROM machine m WHERE firstseen <= $to AND lastseen > $fromCutoff"); // " WHERE lastseen >= :from", compact('from')); $machines = self::getStats3($res, $from, $to, $lowerTimeBound, $upperTimeBound); $locations = []; $keys = ['locationid', 'totalTime', 'totalOffTime', 'totalSessionTime', 'totalStandbyTime', 'totalIdleTime', 'totalIdleTime', 'longSessions', 'shortSessions', 'sessions']; - while ($machine = array_pop($machines)) { + while (($machine = array_pop($machines)) !== null) { if (!isset($locations[$machine['locationid']])) { self::keepKeys($machine, $keys); $locations[$machine['locationid']] = $machine; @@ -58,15 +60,16 @@ class Queries return $locations; } - public static function getOverallStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) + public static function getOverallStatistics(int $from, int $to, $lowerTimeBound = 0, $upperTimeBound = 24) { + $fromCutoff = $from - 86400 * 30; $res = Database::simpleQuery("SELECT m.machineuuid, m.hostname, m.clientip, m.locationid, m.firstseen -- , m.lastboot, m.logintime, m.state - FROM machine m WHERE firstseen <= $to"); // " WHERE lastseen >= :from", compact('from')); + FROM machine m WHERE firstseen <= $to AND lastseen > $fromCutoff"); // " WHERE lastseen >= :from", compact('from')); $machines = self::getStats3($res, $from, $to, $lowerTimeBound, $upperTimeBound); $total = false; $keys = ['totalTime', 'totalOffTime', 'totalSessionTime', 'totalStandbyTime', 'totalIdleTime', 'totalIdleTime', 'longSessions', 'shortSessions', 'sessions']; - while ($machine = array_pop($machines)) { + while (($machine = array_pop($machines)) !== null) { if ($total === false) { self::keepKeys($machine, $keys); $total = $machine; @@ -81,20 +84,12 @@ class Queries $total['sessions'] = array_merge($total['sessions'], $machine['sessions']); } } - $total['medianSessionLength'] = self::calcMedian($total['sessions']); + $total['medianSessionLength'] = $total ? self::calcMedian($total['sessions']) : 0; unset($total['sessions']); return $total; } - /** - * @param \PDOStatement $res - * @param int $from - * @param int $to - * @param int $lowerTimeBound - * @param int $upperTimeBound - * @return array - */ - private static function getStats3($res, $from, $to, $lowerTimeBound, $upperTimeBound) + private static function getStats3(PDOStatement $res, int $from, int $to, int $lowerTimeBound, int $upperTimeBound): array { //$debug = false; if ($lowerTimeBound === 0 && $upperTimeBound === 24 || $upperTimeBound <= $lowerTimeBound) { @@ -103,7 +98,7 @@ class Queries $bounds = [$lowerTimeBound, $upperTimeBound]; } $machines = array(); - while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + foreach ($res as $row) { $row['firstseen'] = max($row['firstseen'], $from); $row += array( 'totalTime' => self::timeDiff($row['firstseen'], $to, $bounds), @@ -129,7 +124,7 @@ class Queries ORDER BY dateline ASC LIMIT 1000", compact('last', 'to')); $last = false; $count = 0; - while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + foreach ($res as $row) { $count += 1; // Update count first, as we use it as a condition in outer loop. No continue before this settype($row['logid'], 'int'); // Update for next query @@ -225,8 +220,8 @@ class Queries } */ $machine[$row['typeid']] += self::timeDiff($start, $end, $bounds); - $sh = date('G', $start); } + $sh = date('G', $start); if ($row['typeid'] === 'totalSessionTime' && ($bounds === false || ($sh >= $bounds[0] && $sh < $bounds[1]))) { if ($row['data'] >= 60) { $machine['longSessions'] += 1; @@ -304,17 +299,18 @@ class Queries /** * Get median of array. - * @param int[] list of values + * @param int[] $array list of values * @return int The median */ - private static function calcMedian($array) { + private static function calcMedian(array $array): int + { if (empty($array)) return 0; sort($array, SORT_NUMERIC); $count = count($array); //total numbers in array $middleval = (int)floor(($count-1) / 2); // find the middle value, or the lowest middle value if($count % 2 === 1) { // odd number, middle is the median - return (int)$array[$middleval]; + return $array[$middleval]; } // even number, calculate avg of 2 medians $low = $array[$middleval]; @@ -324,30 +320,28 @@ class Queries // User Data: Name, Name(anonymized), Number of Logins public static function getUserStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) { - $res = Database::simpleQuery("SELECT username AS name, COUNT(*) AS 'count' + return Database::simpleQuery("SELECT username AS name, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline >= $from and dateline <= $to AND FROM_UNIXTIME(dateline, '%H') >= $lowerTimeBound AND FROM_UNIXTIME(dateline, '%H') < $upperTimeBound GROUP BY username"); - return $res; } // Virtual Machine Data: Name, Number of Usages public static function getVMStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) { - $res = Database::simpleQuery("SELECT data AS name, COUNT(*) AS 'count' + return Database::simpleQuery("SELECT data AS name, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline >= $from and dateline <= $to AND FROM_UNIXTIME(dateline, '%H') >= $lowerTimeBound AND FROM_UNIXTIME(dateline, '%H') < $upperTimeBound GROUP BY data"); - return $res; } - public static function getDozmodStats($from, $to) + public static function getDozmodStats(int $from, int $to): array { - if (!Module::isAvailable('dozmod')) - return array('disabled' => true); + if (Module::get('dozmod') === false) + return ['disabled' => true]; - $return = array(); + $return = []; $return['vms'] = Database::queryFirst("SELECT Count(*) AS `total`, Sum(If(createtime >= $from, 1, 0)) AS `new`, Sum(If(updatetime >= $from, 1, 0)) AS `updated`, Sum(If(latestversionid IS NOT NULL, 1, 0)) AS `valid` FROM sat.imagebase @@ -363,7 +357,38 @@ class Queries return $return; } - public static function getAggregatedMachineStats($from) + public static function getExamStats(int $from, int $to): array + { + if (Module::get('exams') === false) + return ['disabled' => true]; + $return = []; + $eres = Database::simpleQuery("SELECT starttime, endtime, GROUP_CONCAT(exl.locationid) AS `locs` FROM exams + LEFT JOIN exams_x_location exl USING (examid) + WHERE starttime < $to AND endtime > $from + GROUP BY examid"); + foreach ($eres as $row) { + // Get all boot events + $data = ['from' => $row['starttime'], 'to' => $row['endtime']]; + if (empty($row['locs'])) { + $exam = Database::queryFirst("SELECT Count(*) AS `event`, Avg(s.data) AS length FROM statistic s + WHERE typeid = '~session-length' + AND dateline BETWEEN :from AND :to", $data); + } else { + $data['locs'] = explode(',', $row['locs']); + $exam = Database::queryFirst("SELECT Count(*) AS `sessions`, Avg(s.data) AS length FROM statistic s + INNER JOIN machine m USING (machineuuid) + WHERE typeid = '~session-length' AND m.locationid IN (:locs) + AND dateline BETWEEN :from AND :to", $data); + } + settype($exam['length'], 'int'); + settype($exam['sessions'], 'int'); + $exam['duration'] = $row['endtime'] - $row['starttime']; + $return[] = $exam; + } + return $return; + } + + public static function getAggregatedMachineStats(int $from): array { $return = array(); $return['location'] = Database::queryAll("SELECT MD5(CONCAT(locationid, :salt)) AS `location`, Count(*) AS `count` @@ -373,7 +398,7 @@ class Queries array('salt' => GetData::$salt)); $prev = 0; $str = ' '; - foreach (array(0.5, 1, 1.5, 2, 3, 4, 6, 8, 10, 12, 16, 20, 24, 28, 32, 40, 48, 64, 72, 80, 88, 96, 128, 192, 256) as $val) { + foreach ([0.5, 1, 1.5, 2, 3, 4, 6, 8, 10, 12, 16, 20, 24, 28, 32, 40, 48, 64, 72, 80, 88, 96, 128, 192, 256, 512, 768, 1024, 1536, 2048, 3072, 4096] as $val) { $str .= 'WHEN mbram < ' . round(($val + $prev) * 512) . " THEN '" . $prev . "' "; $prev = $val; } @@ -387,6 +412,15 @@ class Queries WHERE lastseen >= $from GROUP BY $key"); } + // Legacy CPU + $f = Database::queryFirst("SELECT Count(*) AS `total` + FROM machine m + INNER JOIN machine_x_hw mxh USING (machineuuid) + INNER JOIN statistic_hw_prop hwp ON (hwp.hwid = mxh.hwid AND hwp.prop = 'vmx-legacy' AND hwp.value <> 0) + WHERE m.lastseen >= $from"); + if (is_array($f)) { + $return['vmx-legacy'] = $f['total']; + } return $return; } @@ -395,14 +429,29 @@ class Queries * @param int $to end timestamp * @return int count of user active in timespan */ - public static function getUniqueUserCount($from, $to) + public static function getUniqueUserCount(int $from, int $to): int { $res = Database::queryFirst("SELECT Count(DISTINCT username) as `total` FROM statistic - WHERE (dateline BETWEEN $from AND $to) AND typeid = '.vmchooser-session-name' - GROUP BY username"); + WHERE (dateline BETWEEN $from AND $to) AND typeid = '.vmchooser-session-name'"); return (int)$res['total']; } + public static function getBaseSystemStats(int $from, int $to) + { + return Database::queryAll("SELECT `data` AS `system`, Count(*) AS `count` + FROM statistic + WHERE (dateline BETWEEN $from AND $to) AND typeid = 'boot-system' + GROUP BY `system`"); + } + + public static function getRunmodeStats(int $from, int $to) + { + return Database::queryAll("SELECT `data` AS `mode`, Count(*) AS `count` + FROM statistic + WHERE (dateline BETWEEN $from AND $to) AND typeid = 'boot-runmode' + GROUP BY `mode`"); + } + } |