From 9ccef127acd6dca0971e12726b019e2beaf7e4ce Mon Sep 17 00:00:00 2001 From: Udo Walter Date: Thu, 12 Jan 2017 12:41:21 +0100 Subject: [statistics_reporting] moved some functions to a new class --- .../statistics_reporting/inc/getdata.inc.php | 98 ++++++++++++++++++++++ 1 file changed, 98 insertions(+) create mode 100644 modules-available/statistics_reporting/inc/getdata.inc.php (limited to 'modules-available/statistics_reporting/inc/getdata.inc.php') diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php new file mode 100644 index 00000000..a1df66d0 --- /dev/null +++ b/modules-available/statistics_reporting/inc/getdata.inc.php @@ -0,0 +1,98 @@ +fetch(PDO::FETCH_ASSOC); + $data = array('time' => self::formatSeconds($row['sum']), 'medianTime' => self::formatSeconds(self::calcMedian($row['median'])), 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']); + + //total time offline + $res = Queries::getTotalOfflineStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound); + $row = $res->fetch(PDO::FETCH_ASSOC); + $data = array_merge($data, array('totalOfftime' => self::formatSeconds($row['timeOff']))); + + return $data; + } + + // per location + public static function perLocation($anonymize = false) { + $res = Queries::getLocationStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound); + $data = array(); + $loc = $anonymize ? 'locHash' : 'locName'; + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $median = self::calcMedian(self::calcMedian($row['medianTime'])); + $data[] = array('location' => $row[$loc], 'time' => self::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'], + 'medianTime' => self::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => self::formatSeconds($row['offlineSum']), 'offlineTimeInSeconds' => $row['offlineSum'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']); + } + return $data; + } + + // per client + public static function perClient($anonymize = false) { + $res = Queries::getClientStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound); + $data = array(); + $name = $anonymize ? 'clientHash' : 'hostname'; + $loc = $anonymize ? 'locHash' : 'locName'; + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $median = self::calcMedian(self::calcMedian($row['medianTime'])); + $data[] = array('hostname' => $row[$name], 'time' => self::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'], + 'medianTime' => self::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => self::formatSeconds($row['offlineSum']), 'offlineTimeInSeconds' => $row['offlineSum'], 'lastStart' => date(DATE_RSS,$row['lastStart']), 'lastStartUnixtime' => $row['lastStart'], + 'lastLogout' => date(DATE_RSS,$row['lastLogout']), 'lastLogoutUnixtime' => $row['lastLogout'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions'], 'locationName' => $row[$loc]); + } + return $data; + } + + // per user + public static function perUser($anonymize = false) { + $res = Queries::getUserStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound); + $data = array(); + $user = $anonymize ? 'userHash' : 'name'; + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $data[] = array('user' => $row['name'], 'sessions' => $row['count']); + } + return $data; + } + + + // per vm + public static function perVM() { + $res = Queries::getVMStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound); + $data = array(); + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $data[] = array('vm' => $row['name'], 'sessions' => $row['count']); + } + return $data; + } + + + + // Format $seconds into ".d .h .m .s" format (day, hour, minute, second) + private static function formatSeconds($seconds) + { + return intdiv($seconds, 3600*24).'d '.intdiv($seconds%(3600*24), 3600).'h '.intdiv($seconds%3600, 60).'m '.($seconds%60).'s'; + } + + // Calculate Median + private static function calcMedian($string) { + $arr = explode(",", $string); + sort($arr, SORT_NUMERIC); + $count = count($arr); //total numbers in array + $middleval = floor(($count-1)/2); // find the middle value, or the lowest middle value + if($count % 2) { // odd number, middle is the median + $median = $arr[(int) $middleval]; + } else { // even number, calculate avg of 2 medians + $low = $arr[(int) $middleval]; + $high = $arr[(int) $middleval+1]; + $median = (($low+$high)/2); + } + return round($median); + } +} \ No newline at end of file -- cgit v1.2.3-55-g7522 From 5da76e80a9fb64bfbda5897aa08fa1bc575dbd25 Mon Sep 17 00:00:00 2001 From: Udo Walter Date: Fri, 13 Jan 2017 13:22:27 +0100 Subject: [statistics_reporting] improved cutoff in query functions + bugfix --- .../statistics_reporting/hooks/cron.inc.php | 3 + .../statistics_reporting/inc/getdata.inc.php | 17 ++-- .../statistics_reporting/inc/queries.inc.php | 91 +++++++++++++--------- .../statistics_reporting/page.inc.php | 5 +- 4 files changed, 70 insertions(+), 46 deletions(-) create mode 100644 modules-available/statistics_reporting/hooks/cron.inc.php (limited to 'modules-available/statistics_reporting/inc/getdata.inc.php') diff --git a/modules-available/statistics_reporting/hooks/cron.inc.php b/modules-available/statistics_reporting/hooks/cron.inc.php new file mode 100644 index 00000000..b79883df --- /dev/null +++ b/modules-available/statistics_reporting/hooks/cron.inc.php @@ -0,0 +1,3 @@ +fetch(PDO::FETCH_ASSOC); $data = array('time' => self::formatSeconds($row['sum']), 'medianTime' => self::formatSeconds(self::calcMedian($row['median'])), 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']); //total time offline - $res = Queries::getTotalOfflineStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound); + $res = Queries::getTotalOfflineStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $row = $res->fetch(PDO::FETCH_ASSOC); $data = array_merge($data, array('totalOfftime' => self::formatSeconds($row['timeOff']))); @@ -24,7 +25,7 @@ class GetData // per location public static function perLocation($anonymize = false) { - $res = Queries::getLocationStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound); + $res = Queries::getLocationStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $data = array(); $loc = $anonymize ? 'locHash' : 'locName'; while ($row = $res->fetch(PDO::FETCH_ASSOC)) { @@ -37,9 +38,9 @@ class GetData // per client public static function perClient($anonymize = false) { - $res = Queries::getClientStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound); + $res = Queries::getClientStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $data = array(); - $name = $anonymize ? 'clientHash' : 'hostname'; + $name = $anonymize ? 'clientHash' : 'clientName'; $loc = $anonymize ? 'locHash' : 'locName'; while ($row = $res->fetch(PDO::FETCH_ASSOC)) { $median = self::calcMedian(self::calcMedian($row['medianTime'])); @@ -52,7 +53,7 @@ class GetData // per user public static function perUser($anonymize = false) { - $res = Queries::getUserStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound); + $res = Queries::getUserStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $data = array(); $user = $anonymize ? 'userHash' : 'name'; while ($row = $res->fetch(PDO::FETCH_ASSOC)) { @@ -64,7 +65,7 @@ class GetData // per vm public static function perVM() { - $res = Queries::getVMStatistics(self::$cutOff, self::$lowerTimeBound, self::$upperTimeBound); + $res = Queries::getVMStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $data = array(); while ($row = $res->fetch(PDO::FETCH_ASSOC)) { $data[] = array('vm' => $row['name'], 'sessions' => $row['count']); diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php index 23afceed..23f5fb03 100644 --- a/modules-available/statistics_reporting/inc/queries.inc.php +++ b/modules-available/statistics_reporting/inc/queries.inc.php @@ -5,18 +5,18 @@ class Queries { // Client Data: Name, Time Online, Median Time Online, Time Offline, last start, last logout, Last Time Booted, Number of Sessions > 60Sec, Number of Sessions < 60Sec, name of location, id of location (anonymized), machine uuid (anonymized) - public static function getClientStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { + public static function getClientStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned'); $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName, MD5(locId) AS locHash, MD5(t1.uuid) AS clientHash FROM ( SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId' - FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable + FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid LEFT JOIN location ON machine.locationid = location.locationid GROUP BY machine.machineuuid ) t1 INNER JOIN ( SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum', MAX(offlineTable.dateline) AS 'lastStart' - FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable + FROM ".self::getBoundedTableQueryString('~offline-length', $from, $to, $lowerTimeBound, $upperTimeBound)." offlineTable INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid GROUP BY machine.machineuuid ) t2 @@ -25,18 +25,18 @@ class Queries } // Location Data: Name, ID (anonymized), Time Online, Median Time Online, Time Offline, Number of Sessions > 60Sec, Number of Sessions < 60Sec - public static function getLocationStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { + public static function getLocationStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned'); $res = Database::simpleQuery("SELECT t1.locName AS locName, MD5(locId) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM ( SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions' - FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable + FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid LEFT JOIN location ON machine.locationid = location.locationid GROUP BY location.locationname ) t1 INNER JOIN ( SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum' - FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable + FROM ".self::getBoundedTableQueryString('~offline-length', $from, $to, $lowerTimeBound, $upperTimeBound)." offlineTable INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid LEFT JOIN location ON machine.locationid = location.locationid GROUP BY location.locationname @@ -46,80 +46,96 @@ class Queries } // User Data: Name, Name(anonymized), Number of Logins - public static function getUserStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { - $res = Database::simpleQuery("SELECT username AS name, IF(username = 'anonymous', 'anonymous', md5(username)) AS userHash, COUNT(*) AS 'count' + public static function getUserStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) { + $res = Database::simpleQuery("SELECT username AS name, IF(username = 'anonymous', 'anonymous', md5(username)) AS userHash, COUNT(*) AS 'count' FROM statistic - WHERE typeid='.vmchooser-session-name' AND dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY) - AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) + WHERE typeid='.vmchooser-session-name' AND dateline+data >= $from and dateline <= $to AND ( + (@daysDiff := (TO_DAYS(FROM_UNIXTIME(@end := IF(dateline+data > $to, $to, dateline+data), '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start := IF(dateline < $from, $from, dateline), '%y-%m-%d'))) = 0 + and (FROM_UNIXTIME(@end, '%H') >= $lowerTimeBound) AND (FROM_UNIXTIME(@start, '%H') < $upperTimeBound)) + or + (@daysDiff = 1 and (FROM_UNIXTIME(@end, '%H') >= $lowerTimeBound) OR (FROM_UNIXTIME(@start, '%H') < $upperTimeBound)) + or + @daysDiff >= 2 + ) GROUP BY username ORDER BY 2 DESC"); return $res; } // Virtual Machine Data: Name, Number of Usages - public static function getVMStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { - $res = Database::simpleQuery("SELECT data AS name, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY) - AND ((FROM_UNIXTIME(dateline+data, '%H')*1 >= $lowerTimeBound) AND (FROM_UNIXTIME(dateline, '%H')*1 < $upperTimeBound)) + public static function getVMStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) { + $res = Database::simpleQuery("SELECT data AS name, COUNT(*) AS 'count' + FROM statistic + WHERE typeid='.vmchooser-session-name' AND dateline+data >= $from and dateline <= $to AND ( + (@daysDiff := (TO_DAYS(FROM_UNIXTIME(@end := IF(dateline+data > $to, $to, dateline+data), '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start := IF(dateline < $from, $from, dateline), '%y-%m-%d'))) = 0 + and (FROM_UNIXTIME(@end, '%H') >= $lowerTimeBound) AND (FROM_UNIXTIME(@start, '%H') < $upperTimeBound)) + or + (@daysDiff = 1 and (FROM_UNIXTIME(@end, '%H') >= $lowerTimeBound) OR (FROM_UNIXTIME(@start, '%H') < $upperTimeBound)) + or + @daysDiff >= 2 + ) GROUP BY data ORDER BY 2 DESC"); return $res; } //Total Data: Time Online, Median Time Online, Number of Sessions > 60Sec, Number of Sessions < 60Sec - public static function getOverallStatistics ($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { + public static function getOverallStatistics ($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) { $res = Database::simpleQuery("SELECT SUM(CAST(sessionTable.length AS UNSIGNED)) AS sum, GROUP_CONCAT(sessionTable.length) AS median, SUM(sessionTable.length >= 60) AS longSessions, SUM(sessionTable.length < 60) AS shortSessions - FROM ".self::getBoundedTableQueryString('~session-length', $lowerTimeBound, $upperTimeBound, $cutOff)." sessionTable"); + FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable"); return $res; } // Total Data(2): Time Offline - public static function getTotalOfflineStatistics($cutOff, $lowerTimeBound = 0, $upperTimeBound = 24) { + public static function getTotalOfflineStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) { $res = Database::simpleQuery("SELECT SUM(CAST(offlineTable.length AS UNSIGNED)) AS timeOff - FROM ".self::getBoundedTableQueryString('~offline-length', $lowerTimeBound, $upperTimeBound, $cutOff)." offlineTable"); + FROM ".self::getBoundedTableQueryString('~offline-length', $from, $to, $lowerTimeBound, $upperTimeBound)." offlineTable"); return $res; } // query string which provides table with time-cutoff and time-bounds - private static function getBoundedTableQueryString($typeid, $lowerTimeBound, $upperTimeBound, $cutOff) + private static function getBoundedTableQueryString($typeid, $from, $to, $lowerTimeBound, $upperTimeBound) { $lowerFormat = "'%y-%m-%d $lowerTimeBound:00:00'"; $upperFormat = "'%y-%m-%d ".($upperTimeBound-1).":59:59'"; $queryString = " select + @start := IF(dateline < $from, $from, dateline), + @end := IF(dateline+data > $to, $to, dateline+data), @startLower := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $lowerFormat)), @startUpper := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat)), @endLower := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat)), @endUpper := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $upperFormat)), - (CAST(data AS SIGNED) + (CAST(@end-@start AS SIGNED) - IF( - dateline > @startUpper, - UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $lowerFormat) + INTERVAL 1 DAY) - dateline, + @start > @startUpper, + UNIX_TIMESTAMP(FROM_UNIXTIME(@start, $lowerFormat) + INTERVAL 1 DAY) - @start, IF( - dateline < @startLower, - @startLower - dateline, + @start < @startLower, + @startLower - @start, 0 ) ) - IF( - dateline+data > @endUpper, - dateline+data - (@endUpper + 1), + @end > @endUpper, + @end - (@endUpper + 1), IF( - dateline+data < @endLower, - dateline+data - (UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $upperFormat) - INTERVAL 1 DAY) + 1), + @end < @endLower, + @end - (UNIX_TIMESTAMP(FROM_UNIXTIME(@end, $upperFormat) - INTERVAL 1 DAY) + 1), 0 ) ) - - ( TO_DAYS(FROM_UNIXTIME(dateline+data, '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(dateline, '%y-%m-%d')) - - 2 - + IF(dateline <= @startUpper, 1, 0) - + IF(dateline+data >= @endLower, 1, 0) + - ( TO_DAYS(FROM_UNIXTIME(@end, '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start, '%y-%m-%d')) + - 2 + + IF(@start <= @startUpper, 1, 0) + + IF(@end >= @endLower, 1, 0) ) * ((24 - ($upperTimeBound - $lowerTimeBound)) * 3600) - IF( - @leftBound := IF(dateline <= @startUpper, @startUpper, UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat) + INTERVAL 1 DAY)) - < @rightBound := IF(dateline+data >= @endLower, @endLower, UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat) - INTERVAL 1 DAY)), + @leftBound := IF(@start <= @startUpper, @startUpper, UNIX_TIMESTAMP(FROM_UNIXTIME(@start, $upperFormat) + INTERVAL 1 DAY)) + < @rightBound := IF(@end >= @endLower, @endLower, UNIX_TIMESTAMP(FROM_UNIXTIME(@end, $lowerFormat) - INTERVAL 1 DAY)), IF( @timeDiff := ( (date_format(from_unixtime(@leftBound), '%H') - @@ -133,18 +149,19 @@ class Queries ), 0 ) - ) as 'length', dateline, data, machineuuid from statistic - where dateline+data >= UNIX_TIMESTAMP(CURDATE() - INTERVAL $cutOff DAY) and typeid = '$typeid' + where dateline+data >= $from and dateline <= $to and typeid = '$typeid' and ( - (UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $lowerFormat)) <= dateline and dateline <= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat))) + (@daysDiff := (TO_DAYS(FROM_UNIXTIME(dateline+data, '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(dateline, '%y-%m-%d'))) = 0 and (dateline <= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat)) and dateline+data >= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat)))) or - (UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat)) <= dateline+data and dateline+data <= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $upperFormat))) + (@daysDiff = 1 and (dateline <= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat)) or dateline+data >= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat)))) + or + @daysDiff >= 2 ) "; return "(".$queryString.")"; diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php index 82c03acc..aa373f54 100644 --- a/modules-available/statistics_reporting/page.inc.php +++ b/modules-available/statistics_reporting/page.inc.php @@ -24,7 +24,10 @@ class Page_Statistics_Reporting extends Page { // timespan you want to see = Days selected * seconds per Day (86400) // default = 14 days - GetData::$cutOff = Request::get('cutoff', 14, 'int') - 1; + $date = new DateTime(date('Y-m-d 00:00:00')); + $date->sub(new DateInterval('P'.(Request::get('cutoff', 14, 'int') - 1).'D')); + GetData::$from = $date->getTimestamp(); + GetData::$to = time(); GetData::$lowerTimeBound = Request::get('lower', 0, 'int'); GetData::$upperTimeBound = Request::get('upper', 24, 'int'); -- cgit v1.2.3-55-g7522 From 3df046508f58bbfff51c5cad33f42af4e378bea7 Mon Sep 17 00:00:00 2001 From: Udo Walter Date: Fri, 13 Jan 2017 16:19:33 +0100 Subject: [statistics_reporting] improved anonymization --- .../statistics_reporting/inc/getdata.inc.php | 50 +++++++++++++++------- .../statistics_reporting/page.inc.php | 5 +-- 2 files changed, 36 insertions(+), 19 deletions(-) (limited to 'modules-available/statistics_reporting/inc/getdata.inc.php') diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php index 8628d9db..880256d6 100644 --- a/modules-available/statistics_reporting/inc/getdata.inc.php +++ b/modules-available/statistics_reporting/inc/getdata.inc.php @@ -13,12 +13,18 @@ class GetData // total time online, average time online, total number of logins $res = Queries::getOverallStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $row = $res->fetch(PDO::FETCH_ASSOC); - $data = array('time' => self::formatSeconds($row['sum']), 'medianTime' => self::formatSeconds(self::calcMedian($row['median'])), 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']); + $data = array('time' => $row['sum'], 'medianTime' => self::calcMedian($row['median']), 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']); //total time offline $res = Queries::getTotalOfflineStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $row = $res->fetch(PDO::FETCH_ASSOC); - $data = array_merge($data, array('totalOfftime' => self::formatSeconds($row['timeOff']))); + $data = array_merge($data, array('totalOfftime' => $row['timeOff'])); + + if (!$anonymize) { + $data["time"] = self::formatSeconds($data["time"]); + $data["medianTime"] = self::formatSeconds($data["time"]); + $data["totalOfftime"] = self::formatSeconds($data["time"]); + } return $data; } @@ -27,11 +33,19 @@ class GetData public static function perLocation($anonymize = false) { $res = Queries::getLocationStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $data = array(); - $loc = $anonymize ? 'locHash' : 'locName'; - while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $median = self::calcMedian(self::calcMedian($row['medianTime'])); - $data[] = array('location' => $row[$loc], 'time' => self::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'], - 'medianTime' => self::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => self::formatSeconds($row['offlineSum']), 'offlineTimeInSeconds' => $row['offlineSum'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']); + if (!$anonymize) { + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $median = self::calcMedian(self::calcMedian($row['medianTime'])); + $data[] = array('location' => $row['locName'], 'time' => self::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'], + 'medianTime' => self::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => self::formatSeconds($row['offlineSum']), + 'offlineTimeInSeconds' => $row['offlineSum'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']); + } + } else { + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $median = self::calcMedian(self::calcMedian($row['medianTime'])); + $data[] = array('location' => $row['locHash'], 'time' => $row['timeSum'], 'medianTime' => $median, 'offTime' => $row['offlineSum'], + 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']); + } } return $data; } @@ -40,13 +54,19 @@ class GetData public static function perClient($anonymize = false) { $res = Queries::getClientStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $data = array(); - $name = $anonymize ? 'clientHash' : 'clientName'; - $loc = $anonymize ? 'locHash' : 'locName'; - while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $median = self::calcMedian(self::calcMedian($row['medianTime'])); - $data[] = array('hostname' => $row[$name], 'time' => self::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'], - 'medianTime' => self::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => self::formatSeconds($row['offlineSum']), 'offlineTimeInSeconds' => $row['offlineSum'], 'lastStart' => date(DATE_RSS,$row['lastStart']), 'lastStartUnixtime' => $row['lastStart'], - 'lastLogout' => date(DATE_RSS,$row['lastLogout']), 'lastLogoutUnixtime' => $row['lastLogout'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions'], 'locationName' => $row[$loc]); + if (!$anonymize) { + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $median = self::calcMedian(self::calcMedian($row['medianTime'])); + $data[] = array('hostname' => $row['clientName'], 'time' => self::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'], + 'medianTime' => self::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => self::formatSeconds($row['offlineSum']), 'offlineTimeInSeconds' => $row['offlineSum'], 'lastStart' => date(DATE_RSS, $row['lastStart']), 'lastStartUnixtime' => $row['lastStart'], + 'lastLogout' => date(DATE_RSS, $row['lastLogout']), 'lastLogoutUnixtime' => $row['lastLogout'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions'], 'locationName' => $row['locName']); + } + } else { + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $median = self::calcMedian(self::calcMedian($row['medianTime'])); + $data[] = array('hostname' => $row['clientHash'], 'time' => $row['timeSum'], 'medianTime' => $median, 'offTime' => $row['offlineSum'], 'lastStart' => $row['lastStart'], + 'lastLogout' => $row['lastLogout'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions'], 'locationName' => $row['locHash']); + } } return $data; } @@ -57,7 +77,7 @@ class GetData $data = array(); $user = $anonymize ? 'userHash' : 'name'; while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $data[] = array('user' => $row['name'], 'sessions' => $row['count']); + $data[] = array('user' => $row[$user], 'sessions' => $row['count']); } return $data; } diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php index aa373f54..941bf12f 100644 --- a/modules-available/statistics_reporting/page.inc.php +++ b/modules-available/statistics_reporting/page.inc.php @@ -24,9 +24,7 @@ class Page_Statistics_Reporting extends Page { // timespan you want to see = Days selected * seconds per Day (86400) // default = 14 days - $date = new DateTime(date('Y-m-d 00:00:00')); - $date->sub(new DateInterval('P'.(Request::get('cutoff', 14, 'int') - 1).'D')); - GetData::$from = $date->getTimestamp(); + GetData::$from = strtotime("- ".(Request::get('cutoff', 14, 'int') - 1)." days 00:00:00"); GetData::$to = time(); GetData::$lowerTimeBound = Request::get('lower', 0, 'int'); GetData::$upperTimeBound = Request::get('upper', 24, 'int'); @@ -37,7 +35,6 @@ class Page_Statistics_Reporting extends Page $data['perUser'] = GetData::perUser(); $data['perVM'] = GetData::perVM(); - Render::addTemplate('columnChooser'); Render::addTemplate('_page', $data); } -- cgit v1.2.3-55-g7522 From 4910fa0b9f5ca6b6063965cc516ced8d72f8be5b Mon Sep 17 00:00:00 2001 From: Udo Walter Date: Tue, 17 Jan 2017 13:47:24 +0100 Subject: [statistics_reporting] added vm name anonymization --- modules-available/statistics_reporting/hooks/cron.inc.php | 2 +- modules-available/statistics_reporting/inc/getdata.inc.php | 5 +++-- modules-available/statistics_reporting/inc/queries.inc.php | 2 +- 3 files changed, 5 insertions(+), 4 deletions(-) (limited to 'modules-available/statistics_reporting/inc/getdata.inc.php') diff --git a/modules-available/statistics_reporting/hooks/cron.inc.php b/modules-available/statistics_reporting/hooks/cron.inc.php index 57e64a84..9b32bc96 100644 --- a/modules-available/statistics_reporting/hooks/cron.inc.php +++ b/modules-available/statistics_reporting/hooks/cron.inc.php @@ -16,7 +16,7 @@ if ($nextReporting < $time && $allowReport) { $data['perLocation'] = GetData::perLocation(true); $data['perClient'] = GetData::perClient(true); $data['perUser'] = GetData::perUser(true); - $data['perVM'] = GetData::perVM(); + $data['perVM'] = GetData::perVM(true); $statisticsReport = json_encode($data); diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php index 880256d6..cfb63658 100644 --- a/modules-available/statistics_reporting/inc/getdata.inc.php +++ b/modules-available/statistics_reporting/inc/getdata.inc.php @@ -84,11 +84,12 @@ class GetData // per vm - public static function perVM() { + public static function perVM($anonymize = false) { $res = Queries::getVMStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $data = array(); + $vm = $anonymize ? 'vmHash' : 'name'; while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $data[] = array('vm' => $row['name'], 'sessions' => $row['count']); + $data[] = array('vm' => $row[$vm], 'sessions' => $row['count']); } return $data; } diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php index 23f5fb03..219640a6 100644 --- a/modules-available/statistics_reporting/inc/queries.inc.php +++ b/modules-available/statistics_reporting/inc/queries.inc.php @@ -64,7 +64,7 @@ class Queries // 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' + $res = Database::simpleQuery("SELECT data AS name, MD5(data) AS vmHash, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline+data >= $from and dateline <= $to AND ( (@daysDiff := (TO_DAYS(FROM_UNIXTIME(@end := IF(dateline+data > $to, $to, dateline+data), '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start := IF(dateline < $from, $from, dateline), '%y-%m-%d'))) = 0 -- cgit v1.2.3-55-g7522 From 6e9d2635c9fe69923a4c30cb99ab6938bfbffe27 Mon Sep 17 00:00:00 2001 From: Udo Walter Date: Tue, 17 Jan 2017 14:24:41 +0100 Subject: [statistics_reporting] improved anonymization --- .../statistics_reporting/hooks/cron.inc.php | 19 ++++++++----------- .../statistics_reporting/inc/getdata.inc.php | 2 +- .../statistics_reporting/inc/queries.inc.php | 17 +++++++++-------- 3 files changed, 18 insertions(+), 20 deletions(-) (limited to 'modules-available/statistics_reporting/inc/getdata.inc.php') diff --git a/modules-available/statistics_reporting/hooks/cron.inc.php b/modules-available/statistics_reporting/hooks/cron.inc.php index 9b32bc96..bd427e64 100644 --- a/modules-available/statistics_reporting/hooks/cron.inc.php +++ b/modules-available/statistics_reporting/hooks/cron.inc.php @@ -9,8 +9,9 @@ if ($nextReporting < $time && $allowReport) { Property::set("nextReporting", strtotime("Sunday 23:59:59")); - GetData::$from = strtotime("Monday last week"); - GetData::$to = strtotime("Sunday last week 23:59:59"); + GetData::$from = strtotime("last sunday - 6 days"); + GetData::$to = strtotime("last sunday 23:59:59"); + GetData::$salt = bin2hex(random_bytes(20)); $data = array_merge(GetData::total(true), array('perLocation' => array(), 'perClient' => array(), 'perUser' => array(), 'perVM' => array())); $data['perLocation'] = GetData::perLocation(true); @@ -21,15 +22,11 @@ if ($nextReporting < $time && $allowReport) { $statisticsReport = json_encode($data); - $url = CONFIG_REPORTING_URL; + $params = array("action" => "statistics", "data" => $statisticsReport); - $curl = curl_init($url); - curl_setopt($curl, CURLOPT_RETURNTRANSFER, true); - curl_setopt($curl, CURLOPT_HTTPHEADER, array("Content-type: application/json")); - curl_setopt($curl, CURLOPT_POST, true); - curl_setopt($curl, CURLOPT_POSTFIELDS, $statisticsReport); + Download::asStringPost(CONFIG_REPORTING_URL, $params, 300, $code); - $json_response = curl_exec($curl); - - curl_close($curl); + if ($code != 200) { + EventLog::warning("Statistics Reporting: ".$code); + } } diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php index cfb63658..07a13a0c 100644 --- a/modules-available/statistics_reporting/inc/getdata.inc.php +++ b/modules-available/statistics_reporting/inc/getdata.inc.php @@ -6,7 +6,7 @@ class GetData public static $to; public static $lowerTimeBound = 0; public static $upperTimeBound = 24; - + public static $salt; // total public static function total($anonymize = false) { diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php index 219640a6..77deb4f8 100644 --- a/modules-available/statistics_reporting/inc/queries.inc.php +++ b/modules-available/statistics_reporting/inc/queries.inc.php @@ -7,7 +7,7 @@ class Queries // Client Data: Name, Time Online, Median Time Online, Time Offline, last start, last logout, Last Time Booted, Number of Sessions > 60Sec, Number of Sessions < 60Sec, name of location, id of location (anonymized), machine uuid (anonymized) public static function getClientStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned'); - $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName, MD5(locId) AS locHash, MD5(t1.uuid) AS clientHash FROM ( + $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName, MD5(CONCAT(locId, :salt)) AS locHash, MD5(CONCAT(t1.uuid, :salt)) AS clientHash FROM ( SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId' FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid @@ -20,14 +20,15 @@ class Queries INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid GROUP BY machine.machineuuid ) t2 - ON t1.uuid = t2.uuid"); + ON t1.uuid = t2.uuid", array("salt" => GetData::$salt)); + return $res; } // Location Data: Name, ID (anonymized), Time Online, Median Time Online, Time Offline, Number of Sessions > 60Sec, Number of Sessions < 60Sec public static function getLocationStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { $notassigned = Dictionary::translateFile('template-tags', 'lang_notassigned'); - $res = Database::simpleQuery("SELECT t1.locName AS locName, MD5(locId) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM ( + $res = Database::simpleQuery("SELECT t1.locName AS locName, MD5(CONCAT(locId, :salt)) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM ( SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions' FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid @@ -41,13 +42,13 @@ class Queries LEFT JOIN location ON machine.locationid = location.locationid GROUP BY location.locationname ) t2 - ON t1.locName = t2.locName"); + ON t1.locName = t2.locName", array("salt" => GetData::$salt)); return $res; } // 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, IF(username = 'anonymous', 'anonymous', md5(username)) AS userHash, COUNT(*) AS 'count' + $res = Database::simpleQuery("SELECT username AS name, IF(username = 'anonymous', 'anonymous', md5(CONCAT(username, :salt))) AS userHash, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline+data >= $from and dateline <= $to AND ( (@daysDiff := (TO_DAYS(FROM_UNIXTIME(@end := IF(dateline+data > $to, $to, dateline+data), '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start := IF(dateline < $from, $from, dateline), '%y-%m-%d'))) = 0 @@ -58,13 +59,13 @@ class Queries @daysDiff >= 2 ) GROUP BY username - ORDER BY 2 DESC"); + ORDER BY 2 DESC", array("salt" => GetData::$salt)); 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, MD5(data) AS vmHash, COUNT(*) AS 'count' + $res = Database::simpleQuery("SELECT data AS name, MD5(CONCAT(data, :salt)) AS vmHash, COUNT(*) AS 'count' FROM statistic WHERE typeid='.vmchooser-session-name' AND dateline+data >= $from and dateline <= $to AND ( (@daysDiff := (TO_DAYS(FROM_UNIXTIME(@end := IF(dateline+data > $to, $to, dateline+data), '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start := IF(dateline < $from, $from, dateline), '%y-%m-%d'))) = 0 @@ -75,7 +76,7 @@ class Queries @daysDiff >= 2 ) GROUP BY data - ORDER BY 2 DESC"); + ORDER BY 2 DESC", array("salt" => GetData::$salt)); return $res; } -- cgit v1.2.3-55-g7522 From c76f3fd2ad3711327c4b9bc844ae927cb86416a0 Mon Sep 17 00:00:00 2001 From: Simon Rettberg Date: Thu, 19 Jan 2017 13:09:15 +0100 Subject: [statistics_reporting] intdiv() is PHP7 only --- modules-available/statistics_reporting/inc/getdata.inc.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'modules-available/statistics_reporting/inc/getdata.inc.php') diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php index 07a13a0c..b0a3171f 100644 --- a/modules-available/statistics_reporting/inc/getdata.inc.php +++ b/modules-available/statistics_reporting/inc/getdata.inc.php @@ -99,7 +99,7 @@ class GetData // Format $seconds into ".d .h .m .s" format (day, hour, minute, second) private static function formatSeconds($seconds) { - return intdiv($seconds, 3600*24).'d '.intdiv($seconds%(3600*24), 3600).'h '.intdiv($seconds%3600, 60).'m '.($seconds%60).'s'; + return sprintf('%dd, %02d:%02d:%02d', $seconds / (3600*24), ($seconds % (3600*24)) / 3600, ($seconds%3600) / 60, $seconds%60); } // Calculate Median -- cgit v1.2.3-55-g7522 From cb13c655c5394a3226fc355497af9907b1e84b83 Mon Sep 17 00:00:00 2001 From: Udo Walter Date: Tue, 31 Jan 2017 12:32:21 +0100 Subject: [statistics_reporting] fixed total showing wrong median and offline time --- modules-available/statistics_reporting/inc/getdata.inc.php | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'modules-available/statistics_reporting/inc/getdata.inc.php') diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php index b0a3171f..1a3c81dc 100644 --- a/modules-available/statistics_reporting/inc/getdata.inc.php +++ b/modules-available/statistics_reporting/inc/getdata.inc.php @@ -22,8 +22,8 @@ class GetData if (!$anonymize) { $data["time"] = self::formatSeconds($data["time"]); - $data["medianTime"] = self::formatSeconds($data["time"]); - $data["totalOfftime"] = self::formatSeconds($data["time"]); + $data["medianTime"] = self::formatSeconds($data["medianTime"]); + $data["totalOfftime"] = self::formatSeconds($data["totalOfftime"]); } return $data; -- cgit v1.2.3-55-g7522 From 5c6b40b28ead1157260e4abc2e697a696f69520e Mon Sep 17 00:00:00 2001 From: Simon Rettberg Date: Tue, 31 Jan 2017 15:45:28 +0100 Subject: [statistics_reporting] Make getdata functions options flag based in preparation of data export --- .../statistics_reporting/inc/getdata.inc.php | 87 ++++++++++++++-------- .../statistics_reporting/inc/remotereport.inc.php | 10 +-- .../statistics_reporting/page.inc.php | 10 +-- .../statistics_reporting/templates/_page.html | 24 +++--- 4 files changed, 76 insertions(+), 55 deletions(-) (limited to 'modules-available/statistics_reporting/inc/getdata.inc.php') diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php index 1a3c81dc..b19af966 100644 --- a/modules-available/statistics_reporting/inc/getdata.inc.php +++ b/modules-available/statistics_reporting/inc/getdata.inc.php @@ -1,5 +1,8 @@ fetch(PDO::FETCH_ASSOC); @@ -20,59 +24,75 @@ class GetData $row = $res->fetch(PDO::FETCH_ASSOC); $data = array_merge($data, array('totalOfftime' => $row['timeOff'])); - if (!$anonymize) { - $data["time"] = self::formatSeconds($data["time"]); - $data["medianTime"] = self::formatSeconds($data["medianTime"]); - $data["totalOfftime"] = self::formatSeconds($data["totalOfftime"]); + if ($printable) { + $data["time_s"] = self::formatSeconds($data["time"]); + $data["medianTime_s"] = self::formatSeconds($data["medianTime"]); + $data["totalOfftime_s"] = self::formatSeconds($data["totalOfftime"]); } return $data; } // per location - public static function perLocation($anonymize = false) { + public static function perLocation($flags = 0) { + $anonymize = 0 !== ($flags & GETDATA_ANONYMOUS); + $printable = 0 !== ($flags & GETDATA_PRINTABLE); $res = Queries::getLocationStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $data = array(); - if (!$anonymize) { - while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $median = self::calcMedian(self::calcMedian($row['medianTime'])); - $data[] = array('location' => $row['locName'], 'time' => self::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'], - 'medianTime' => self::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => self::formatSeconds($row['offlineSum']), - 'offlineTimeInSeconds' => $row['offlineSum'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']); - } - } else { - while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $median = self::calcMedian(self::calcMedian($row['medianTime'])); - $data[] = array('location' => $row['locHash'], 'time' => $row['timeSum'], 'medianTime' => $median, 'offTime' => $row['offlineSum'], - 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']); + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $median = self::calcMedian(self::calcMedian($row['medianTime'])); + $entry = array( + 'location' => ($anonymize ? $row['locHash'] : $row['locName']), + 'time' => $row['timeSum'], + 'medianTime' => $median, + 'offTime' => $row['offlineSum'], + 'sessions' => $row['longSessions'], + 'shortSessions' => $row['shortSessions'] + ); + if ($printable) { + $entry['time_s'] = self::formatSeconds($row['timeSum']); + $entry['medianTime_s'] = self::formatSeconds($median); + $entry['offTime_s'] = self::formatSeconds($row['offlineSum']); } + $data[] = $entry; } return $data; } // per client - public static function perClient($anonymize = false) { + public static function perClient($flags = 0) { + $anonymize = 0 !== ($flags & GETDATA_ANONYMOUS); + $printable = 0 !== ($flags & GETDATA_PRINTABLE); $res = Queries::getClientStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $data = array(); - if (!$anonymize) { - while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $median = self::calcMedian(self::calcMedian($row['medianTime'])); - $data[] = array('hostname' => $row['clientName'], 'time' => self::formatSeconds($row['timeSum']), 'timeInSeconds' => $row['timeSum'], - 'medianTime' => self::formatSeconds($median), 'medianTimeInSeconds' => $median, 'offTime' => self::formatSeconds($row['offlineSum']), 'offlineTimeInSeconds' => $row['offlineSum'], 'lastStart' => date(DATE_RSS, $row['lastStart']), 'lastStartUnixtime' => $row['lastStart'], - 'lastLogout' => date(DATE_RSS, $row['lastLogout']), 'lastLogoutUnixtime' => $row['lastLogout'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions'], 'locationName' => $row['locName']); - } - } else { - while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $median = self::calcMedian(self::calcMedian($row['medianTime'])); - $data[] = array('hostname' => $row['clientHash'], 'time' => $row['timeSum'], 'medianTime' => $median, 'offTime' => $row['offlineSum'], 'lastStart' => $row['lastStart'], - 'lastLogout' => $row['lastLogout'], 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions'], 'locationName' => $row['locHash']); + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $median = self::calcMedian(self::calcMedian($row['medianTime'])); + $entry = array( + 'hostname' => ($anonymize ? $row['clientHash'] : $row['clientName']), + 'time' => $row['timeSum'], + 'medianTime' => $median, + 'offTime' => $row['offlineSum'], + 'lastStart' => $row['lastStart'], + 'lastLogout' => $row['lastLogout'], + 'sessions' => $row['longSessions'], + 'shortSessions' => $row['shortSessions'], + 'location' => ($anonymize ? $row['locHash'] : $row['locName']), + ); + if ($printable) { + $entry['time_s'] = self::formatSeconds($row['timeSum']); + $entry['medianTime_s'] = self::formatSeconds($median); + $entry['offTime_s'] = self::formatSeconds($row['offlineSum']); + $entry['lastStart_s'] = date(DATE_RSS, $row['lastStart']); + $entry['lastLogout_s'] = date(DATE_RSS, $row['lastLogout']); } + $data[] = $entry; } return $data; } // per user - public static function perUser($anonymize = false) { + public static function perUser($flags = 0) { + $anonymize = 0 !== ($flags & GETDATA_ANONYMOUS); $res = Queries::getUserStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $data = array(); $user = $anonymize ? 'userHash' : 'name'; @@ -84,7 +104,8 @@ class GetData // per vm - public static function perVM($anonymize = false) { + public static function perVM($flags = 0) { + $anonymize = 0 !== ($flags & GETDATA_ANONYMOUS); $res = Queries::getVMStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $data = array(); $vm = $anonymize ? 'vmHash' : 'name'; diff --git a/modules-available/statistics_reporting/inc/remotereport.inc.php b/modules-available/statistics_reporting/inc/remotereport.inc.php index 0bf4e7e2..e00e6758 100644 --- a/modules-available/statistics_reporting/inc/remotereport.inc.php +++ b/modules-available/statistics_reporting/inc/remotereport.inc.php @@ -71,11 +71,11 @@ class RemoteReport GetData::$from = $from; GetData::$to = $to; GetData::$salt = bin2hex(Util::randomBytes(20)); - $data = GetData::total(true); - $data['perLocation'] = GetData::perLocation(true); - $data['perClient'] = GetData::perClient(true); - $data['perUser'] = GetData::perUser(true); - $data['perVM'] = GetData::perVM(true); + $data = GetData::total(GETDATA_ANONYMOUS); + $data['perLocation'] = GetData::perLocation(GETDATA_ANONYMOUS); + $data['perClient'] = GetData::perClient(GETDATA_ANONYMOUS); + $data['perUser'] = GetData::perUser(GETDATA_ANONYMOUS); + $data['perVM'] = GetData::perVM(GETDATA_ANONYMOUS); $data['tsFrom'] = $from; $data['tsTo'] = $to; return $data; diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php index 0309be68..052d813d 100644 --- a/modules-available/statistics_reporting/page.inc.php +++ b/modules-available/statistics_reporting/page.inc.php @@ -33,11 +33,11 @@ class Page_Statistics_Reporting extends Page GetData::$lowerTimeBound = Request::get('lower', 0, 'int'); GetData::$upperTimeBound = Request::get('upper', 24, 'int'); - $data = array_merge(GetData::total(), array('perLocation' => array(), 'perClient' => array(), 'perUser' => array(), 'perVM' => array())); - $data['perLocation'] = GetData::perLocation(); - $data['perClient'] = GetData::perClient(); - $data['perUser'] = GetData::perUser(); - $data['perVM'] = GetData::perVM(); + $data = GetData::total(GETDATA_PRINTABLE); + $data['perLocation'] = GetData::perLocation(GETDATA_PRINTABLE); + $data['perClient'] = GetData::perClient(GETDATA_PRINTABLE); + $data['perUser'] = GetData::perUser(GETDATA_PRINTABLE); + $data['perVM'] = GetData::perVM(GETDATA_PRINTABLE); Render::addTemplate('columnChooser'); Render::addTemplate('_page', $data); diff --git a/modules-available/statistics_reporting/templates/_page.html b/modules-available/statistics_reporting/templates/_page.html index 37c3cf90..39726d51 100644 --- a/modules-available/statistics_reporting/templates/_page.html +++ b/modules-available/statistics_reporting/templates/_page.html @@ -15,11 +15,11 @@ {{lang_total}} - {{time}} - {{medianTime}} + {{time_s}} + {{medianTime_s}} {{sessions}} {{shortSessions}} - {{totalOfftime}} + {{totalOfftime_s}} @@ -42,11 +42,11 @@ {{#perLocation}} {{location}} - {{time}} - {{medianTime}} + {{time_s}} + {{medianTime_s}} {{sessions}} {{shortSessions}} - {{offTime}} + {{offTime_s}} {{/perLocation}} @@ -73,14 +73,14 @@ {{#perClient}} {{hostname}} - {{locationName}} - {{time}} - {{medianTime}} + {{location}} + {{time_s}} + {{medianTime_s}} {{sessions}} {{shortSessions}} - {{offTime}} - {{lastLogout}} - {{lastStart}} + {{offTime_s}} + {{lastLogout_s}} + {{lastStart_s}} {{/perClient}} -- cgit v1.2.3-55-g7522 From 5dd18528ebd23cbc8b6d86c988d398a0058d1bab Mon Sep 17 00:00:00 2001 From: Simon Rettberg Date: Tue, 7 Feb 2017 14:55:23 +0100 Subject: [statistics_reporting] Use indexed fields for joins, return location id from GetData::* --- modules-available/statistics_reporting/inc/getdata.inc.php | 6 ++++++ modules-available/statistics_reporting/inc/queries.inc.php | 12 ++++++------ 2 files changed, 12 insertions(+), 6 deletions(-) (limited to 'modules-available/statistics_reporting/inc/getdata.inc.php') diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php index b19af966..9604d652 100644 --- a/modules-available/statistics_reporting/inc/getdata.inc.php +++ b/modules-available/statistics_reporting/inc/getdata.inc.php @@ -49,6 +49,9 @@ class GetData 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions'] ); + if (!$anonymize) { + $entry['locationId'] = $row['locId']; + } if ($printable) { $entry['time_s'] = self::formatSeconds($row['timeSum']); $entry['medianTime_s'] = self::formatSeconds($median); @@ -78,6 +81,9 @@ class GetData 'shortSessions' => $row['shortSessions'], 'location' => ($anonymize ? $row['locHash'] : $row['locName']), ); + if (!$anonymize) { + $entry['locationId'] = $row['locId']; + } if ($printable) { $entry['time_s'] = self::formatSeconds($row['timeSum']); $entry['medianTime_s'] = self::formatSeconds($median); diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php index a36584ee..7a56414b 100644 --- a/modules-available/statistics_reporting/inc/queries.inc.php +++ b/modules-available/statistics_reporting/inc/queries.inc.php @@ -8,7 +8,7 @@ class Queries public static function getClientStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { $notassigned = Dictionary::translate('notAssigned', true); Database::exec("SET SESSION group_concat_max_len = 1000000000"); - $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locName, MD5(CONCAT(locId, :salt)) AS locHash, MD5(CONCAT(t1.uuid, :salt)) AS clientHash FROM ( + $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locId, locName, MD5(CONCAT(locId, :salt)) AS locHash, MD5(CONCAT(t1.uuid, :salt)) AS clientHash FROM ( SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId' FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid @@ -30,21 +30,21 @@ class Queries public static function getLocationStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { $notassigned = Dictionary::translate('notAssigned', true); Database::exec("SET SESSION group_concat_max_len = 1000000000"); - $res = Database::simpleQuery("SELECT t1.locName AS locName, MD5(CONCAT(locId, :salt)) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM ( + $res = Database::simpleQuery("SELECT t1.locId, t1.locName AS locName, MD5(CONCAT(t1.locId, :salt)) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM ( SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions' FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid LEFT JOIN location ON machine.locationid = location.locationid - GROUP BY location.locationname + GROUP BY location.locationid ) t1 INNER JOIN ( - SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum' + SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum' FROM ".self::getBoundedTableQueryString('~offline-length', $from, $to, $lowerTimeBound, $upperTimeBound)." offlineTable INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid LEFT JOIN location ON machine.locationid = location.locationid - GROUP BY location.locationname + GROUP BY location.locationid ) t2 - ON t1.locName = t2.locName", array("salt" => GetData::$salt)); + ON t1.locId = t2.locId", array("salt" => GetData::$salt)); return $res; } -- cgit v1.2.3-55-g7522 From 3a09daee1ebaf014536e14e82ca0fd4c2c92a41f Mon Sep 17 00:00:00 2001 From: Simon Rettberg Date: Wed, 8 Feb 2017 13:38:04 +0100 Subject: [statistics_reporting] Consistent field naming across classes, templates etc. --- .../statistics_reporting/inc/getdata.inc.php | 46 +++++++++++----------- .../statistics_reporting/inc/queries.inc.php | 8 ++-- .../templates/table-client.html | 8 ++-- .../templates/table-location.html | 8 ++-- .../templates/table-total.html | 8 ++-- 5 files changed, 39 insertions(+), 39 deletions(-) (limited to 'modules-available/statistics_reporting/inc/getdata.inc.php') diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php index 9604d652..83f118e6 100644 --- a/modules-available/statistics_reporting/inc/getdata.inc.php +++ b/modules-available/statistics_reporting/inc/getdata.inc.php @@ -17,17 +17,17 @@ class GetData // total time online, average time online, total number of logins $res = Queries::getOverallStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $row = $res->fetch(PDO::FETCH_ASSOC); - $data = array('time' => $row['sum'], 'medianTime' => self::calcMedian($row['median']), 'sessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']); + $data = array('totalTime' => $row['sum'], 'medianSessionLength' => self::calcMedian($row['median']), 'longSessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions']); //total time offline $res = Queries::getTotalOfflineStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $row = $res->fetch(PDO::FETCH_ASSOC); - $data = array_merge($data, array('totalOfftime' => $row['timeOff'])); + $data['totalOffTime'] = $row['timeOff']; if ($printable) { - $data["time_s"] = self::formatSeconds($data["time"]); - $data["medianTime_s"] = self::formatSeconds($data["medianTime"]); - $data["totalOfftime_s"] = self::formatSeconds($data["totalOfftime"]); + $data["totalTime_s"] = self::formatSeconds($data["totalTime"]); + $data["medianSessionLength_s"] = self::formatSeconds($data["medianSessionLength"]); + $data["totalOffTime_s"] = self::formatSeconds($data["totalOffTime"]); } return $data; @@ -40,22 +40,22 @@ class GetData $res = Queries::getLocationStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $data = array(); while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $median = self::calcMedian(self::calcMedian($row['medianTime'])); + $median = self::calcMedian(self::calcMedian($row['medianSessionLength'])); $entry = array( 'location' => ($anonymize ? $row['locHash'] : $row['locName']), - 'time' => $row['timeSum'], - 'medianTime' => $median, - 'offTime' => $row['offlineSum'], - 'sessions' => $row['longSessions'], + 'totalTime' => $row['timeSum'], + 'medianSessionLength' => $median, + 'totalOffTime' => $row['offlineSum'], + 'longSessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions'] ); if (!$anonymize) { $entry['locationId'] = $row['locId']; } if ($printable) { - $entry['time_s'] = self::formatSeconds($row['timeSum']); - $entry['medianTime_s'] = self::formatSeconds($median); - $entry['offTime_s'] = self::formatSeconds($row['offlineSum']); + $entry['totalTime_s'] = self::formatSeconds($row['timeSum']); + $entry['medianSessionLength_s'] = self::formatSeconds($median); + $entry['totalOffTime_s'] = self::formatSeconds($row['offlineSum']); } $data[] = $entry; } @@ -69,15 +69,15 @@ class GetData $res = Queries::getClientStatistics(self::$from, self::$to, self::$lowerTimeBound, self::$upperTimeBound); $data = array(); while ($row = $res->fetch(PDO::FETCH_ASSOC)) { - $median = self::calcMedian(self::calcMedian($row['medianTime'])); + $median = self::calcMedian(self::calcMedian($row['medianSessionLength'])); $entry = array( 'hostname' => ($anonymize ? $row['clientHash'] : $row['clientName']), - 'time' => $row['timeSum'], - 'medianTime' => $median, - 'offTime' => $row['offlineSum'], + 'totalTime' => $row['timeSum'], + 'medianSessionLength' => $median, + 'totalOffTime' => $row['offlineSum'], 'lastStart' => $row['lastStart'], 'lastLogout' => $row['lastLogout'], - 'sessions' => $row['longSessions'], + 'longSessions' => $row['longSessions'], 'shortSessions' => $row['shortSessions'], 'location' => ($anonymize ? $row['locHash'] : $row['locName']), ); @@ -85,11 +85,11 @@ class GetData $entry['locationId'] = $row['locId']; } if ($printable) { - $entry['time_s'] = self::formatSeconds($row['timeSum']); - $entry['medianTime_s'] = self::formatSeconds($median); - $entry['offTime_s'] = self::formatSeconds($row['offlineSum']); - $entry['lastStart_s'] = date(DATE_RSS, $row['lastStart']); - $entry['lastLogout_s'] = date(DATE_RSS, $row['lastLogout']); + $entry['totalTime_s'] = self::formatSeconds($row['timeSum']); + $entry['medianSessionLength_s'] = self::formatSeconds($median); + $entry['totalOffTime_s'] = self::formatSeconds($row['offlineSum']); + $entry['lastStart_s'] = date(DATE_ISO8601, $row['lastStart']); + $entry['lastLogout_s'] = date(DATE_ISO8601, $row['lastLogout']); } $data[] = $entry; } diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php index 7a56414b..1d1d3a44 100644 --- a/modules-available/statistics_reporting/inc/queries.inc.php +++ b/modules-available/statistics_reporting/inc/queries.inc.php @@ -8,8 +8,8 @@ class Queries public static function getClientStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { $notassigned = Dictionary::translate('notAssigned', true); Database::exec("SET SESSION group_concat_max_len = 1000000000"); - $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianTime, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locId, locName, MD5(CONCAT(locId, :salt)) AS locHash, MD5(CONCAT(t1.uuid, :salt)) AS clientHash FROM ( - SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId' + $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianSessionLength, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locId, locName, MD5(CONCAT(locId, :salt)) AS locHash, MD5(CONCAT(t1.uuid, :salt)) AS clientHash FROM ( + SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianSessionLength', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId' FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid LEFT JOIN location ON machine.locationid = location.locationid @@ -30,8 +30,8 @@ class Queries public static function getLocationStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { $notassigned = Dictionary::translate('notAssigned', true); Database::exec("SET SESSION group_concat_max_len = 1000000000"); - $res = Database::simpleQuery("SELECT t1.locId, t1.locName AS locName, MD5(CONCAT(t1.locId, :salt)) AS locHash, timeSum, medianTime, offlineSum, longSessions, shortSessions FROM ( - SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianTime', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions' + $res = Database::simpleQuery("SELECT t1.locId, t1.locName AS locName, MD5(CONCAT(t1.locId, :salt)) AS locHash, timeSum, medianSessionLength, offlineSum, longSessions, shortSessions FROM ( + SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianSessionLength', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions' FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid LEFT JOIN location ON machine.locationid = location.locationid diff --git a/modules-available/statistics_reporting/templates/table-client.html b/modules-available/statistics_reporting/templates/table-client.html index 2fb2f574..be504cef 100644 --- a/modules-available/statistics_reporting/templates/table-client.html +++ b/modules-available/statistics_reporting/templates/table-client.html @@ -17,11 +17,11 @@ {{hostname}} {{location}} - {{time_s}} - {{medianTime_s}} - {{sessions}} + {{totalTime_s}} + {{medianSessionLength_s}} + {{longSessions}} {{shortSessions}} - {{offTime_s}} + {{totalOffTime_s}} {{lastLogout_s}} {{lastStart_s}} diff --git a/modules-available/statistics_reporting/templates/table-location.html b/modules-available/statistics_reporting/templates/table-location.html index 9479b7af..ccac623d 100644 --- a/modules-available/statistics_reporting/templates/table-location.html +++ b/modules-available/statistics_reporting/templates/table-location.html @@ -13,11 +13,11 @@ {{#data}} {{location}} - {{time_s}} - {{medianTime_s}} - {{sessions}} + {{totalTime_s}} + {{medianSessionLength_s}} + {{longSessions}} {{shortSessions}} - {{offTime_s}} + {{totalOffTime_s}} {{/data}} diff --git a/modules-available/statistics_reporting/templates/table-total.html b/modules-available/statistics_reporting/templates/table-total.html index e59dda25..4048a178 100644 --- a/modules-available/statistics_reporting/templates/table-total.html +++ b/modules-available/statistics_reporting/templates/table-total.html @@ -12,11 +12,11 @@ {{lang_total}} - {{data.time_s}} - {{data.medianTime_s}} - {{data.sessions}} + {{data.totalTime_s}} + {{data.medianSessionLength_s}} + {{data.longSessions}} {{data.shortSessions}} - {{data.totalOfftime_s}} + {{data.totalOffTime_s}} -- cgit v1.2.3-55-g7522 From 1fe5e109bc29af02de89c6513744c4c8755d5422 Mon Sep 17 00:00:00 2001 From: Udo Walter Date: Thu, 23 Feb 2017 17:44:39 +0100 Subject: [statistics_reporting] included currently offline clients in offline time + bugfixes + some comments --- .../statistics_reporting/inc/getdata.inc.php | 4 +- .../statistics_reporting/inc/queries.inc.php | 171 +++++++++++++-------- 2 files changed, 109 insertions(+), 66 deletions(-) (limited to 'modules-available/statistics_reporting/inc/getdata.inc.php') diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php index 83f118e6..f65ee868 100644 --- a/modules-available/statistics_reporting/inc/getdata.inc.php +++ b/modules-available/statistics_reporting/inc/getdata.inc.php @@ -88,8 +88,8 @@ class GetData $entry['totalTime_s'] = self::formatSeconds($row['timeSum']); $entry['medianSessionLength_s'] = self::formatSeconds($median); $entry['totalOffTime_s'] = self::formatSeconds($row['offlineSum']); - $entry['lastStart_s'] = date(DATE_ISO8601, $row['lastStart']); - $entry['lastLogout_s'] = date(DATE_ISO8601, $row['lastLogout']); + $entry['lastStart_s'] = $row['lastStart'] == 0 ? "" : date(DATE_ISO8601, $row['lastStart']); + $entry['lastLogout_s'] = $row['lastLogout'] == 0 ? "" : date(DATE_ISO8601, $row['lastLogout']); } $data[] = $entry; } diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php index bb96a46e..8cca9650 100644 --- a/modules-available/statistics_reporting/inc/queries.inc.php +++ b/modules-available/statistics_reporting/inc/queries.inc.php @@ -8,17 +8,17 @@ class Queries public static function getClientStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { $notassigned = Dictionary::translate('notAssigned', true); Database::exec("SET SESSION group_concat_max_len = 1000000000"); - $res = Database::simpleQuery("SELECT t1.name AS clientName, timeSum, medianSessionLength, offlineSum, lastStart, lastLogout, longSessions, shortSessions, locId, locName, MD5(CONCAT(locId, :salt)) AS locHash, MD5(CONCAT(t1.uuid, :salt)) AS clientHash FROM ( - SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianSessionLength', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions',MAX(sessionTable.dateline + sessionTable.data) AS 'lastLogout', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId' + $res = Database::simpleQuery("SELECT name AS clientName, timeSum, medianSessionLength, offlineSum, IFNULL(lastStart, 0) as lastStart, IFNULL(lastLogout, 0) as lastLogout, longSessions, shortSessions, locId, locName, MD5(CONCAT(locId, :salt)) AS locHash, MD5(CONCAT(t1.uuid, :salt)) AS clientHash FROM ( + SELECT machine.machineuuid AS 'uuid', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianSessionLength', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions', MAX(sessionTable.endInBound) AS 'lastLogout' FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid - LEFT JOIN location ON machine.locationid = location.locationid GROUP BY machine.machineuuid ) t1 - INNER JOIN ( - SELECT machine.machineuuid AS 'uuid', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum', MAX(offlineTable.dateline) AS 'lastStart' + RIGHT JOIN ( + SELECT machine.hostname AS 'name', machine.machineuuid AS 'uuid', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum', MAX(offlineTable.endInBound) AS 'lastStart', IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId' FROM ".self::getBoundedTableQueryString('~offline-length', $from, $to, $lowerTimeBound, $upperTimeBound)." offlineTable INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid + LEFT JOIN location ON machine.locationid = location.locationid GROUP BY machine.machineuuid ) t2 ON t1.uuid = t2.uuid", array("salt" => GetData::$salt)); @@ -30,15 +30,15 @@ class Queries public static function getLocationStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24, $excludeToday = false) { $notassigned = Dictionary::translate('notAssigned', true); Database::exec("SET SESSION group_concat_max_len = 1000000000"); - $res = Database::simpleQuery("SELECT t1.locId, t1.locName AS locName, MD5(CONCAT(t1.locId, :salt)) AS locHash, timeSum, medianSessionLength, offlineSum, longSessions, shortSessions FROM ( - SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianSessionLength', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions' + $res = Database::simpleQuery("SELECT t1.locId, locName AS locName, MD5(CONCAT(t1.locId, :salt)) AS locHash, timeSum, medianSessionLength, offlineSum, longSessions, shortSessions FROM ( + SELECT location.locationid AS 'locId', SUM(CAST(sessionTable.length AS UNSIGNED)) AS 'timeSum', GROUP_CONCAT(sessionTable.length) AS 'medianSessionLength', SUM(sessionTable.length >= 60) AS 'longSessions', SUM(sessionTable.length < 60) AS 'shortSessions' FROM ".self::getBoundedTableQueryString('~session-length', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable INNER JOIN machine ON sessionTable.machineuuid = machine.machineuuid LEFT JOIN location ON machine.locationid = location.locationid GROUP BY machine.locationid ) t1 - LEFT JOIN ( - SELECT location.locationid AS 'locId', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum' + RIGHT JOIN ( + SELECT IFNULL(location.locationname, '$notassigned') AS 'locName', location.locationid AS 'locId', SUM(CAST(offlineTable.length AS UNSIGNED)) AS 'offlineSum' FROM ".self::getBoundedTableQueryString('~offline-length', $from, $to, $lowerTimeBound, $upperTimeBound)." offlineTable INNER JOIN machine ON offlineTable.machineuuid = machine.machineuuid LEFT JOIN location ON machine.locationid = location.locationid @@ -52,14 +52,8 @@ class Queries public static function getUserStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) { $res = Database::simpleQuery("SELECT username AS name, IF(username = 'anonymous', 'anonymous', md5(CONCAT(username, :salt))) AS userHash, COUNT(*) AS 'count' FROM statistic - WHERE typeid='.vmchooser-session-name' AND dateline+data >= $from and dateline <= $to AND ( - (@daysDiff := (TO_DAYS(FROM_UNIXTIME(@end := IF(dateline+data > $to, $to, dateline+data), '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start := IF(dateline < $from, $from, dateline), '%y-%m-%d'))) = 0 - and (FROM_UNIXTIME(@end, '%H') >= $lowerTimeBound) AND (FROM_UNIXTIME(@start, '%H') < $upperTimeBound)) - or - (@daysDiff = 1 and (FROM_UNIXTIME(@end, '%H') >= $lowerTimeBound) OR (FROM_UNIXTIME(@start, '%H') < $upperTimeBound)) - or - @daysDiff >= 2 - ) + 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 ORDER BY 2 DESC", array("salt" => GetData::$salt)); return $res; @@ -69,14 +63,8 @@ class Queries public static function getVMStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) { $res = Database::simpleQuery("SELECT data AS name, MD5(CONCAT(data, :salt)) AS vmHash, COUNT(*) AS 'count' FROM statistic - WHERE typeid='.vmchooser-session-name' AND dateline+data >= $from and dateline <= $to AND ( - (@daysDiff := (TO_DAYS(FROM_UNIXTIME(@end := IF(dateline+data > $to, $to, dateline+data), '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start := IF(dateline < $from, $from, dateline), '%y-%m-%d'))) = 0 - and (FROM_UNIXTIME(@end, '%H') >= $lowerTimeBound) AND (FROM_UNIXTIME(@start, '%H') < $upperTimeBound)) - or - (@daysDiff = 1 and (FROM_UNIXTIME(@end, '%H') >= $lowerTimeBound) OR (FROM_UNIXTIME(@start, '%H') < $upperTimeBound)) - or - @daysDiff >= 2 - ) + 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 ORDER BY 2 DESC", array("salt" => GetData::$salt)); return $res; @@ -100,72 +88,127 @@ class Queries // query string which provides table with time-cutoff and time-bounds private static function getBoundedTableQueryString($typeid, $from, $to, $lowerTimeBound, $upperTimeBound) { + // get Clients that are currently oflfine (the offline time is not yet recorded in the statistic table) + $union = $typeid == '~offline-length' ? + "union + select CAST(IF(lastseen < $from, $from, lastseen) as UNSIGNED) as start, $to as end, + '~offline-length' as typeid, machineuuid, 'machine' + from machine where lastseen <= $to and UNIX_TIMESTAMP() - lastseen >= 600" : ""; + + $lowerFormat = "'%y-%m-%d $lowerTimeBound:00:00'"; $upperFormat = "'%y-%m-%d ".($upperTimeBound-1).":59:59'"; $queryString = " select - - @start := IF(dateline < $from, $from, dateline), - @end := IF(dateline+data > $to, $to, dateline+data), - @startLower := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $lowerFormat)), - @startUpper := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat)), - @endLower := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat)), - @endUpper := UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $upperFormat)), - (CAST(@end-@start AS SIGNED) + # The whole length of the session/offline time. + (end-start + + # Now the time that is not within the daily time bounds get substracted. + # This includes the time before the first daily bound, the time after the last daily bound + # and the time between the daily bounds (if a session/offline time spans multiple days) + + # Time before the first daily bound gets substracted. - IF( - @start > @startUpper, - UNIX_TIMESTAMP(FROM_UNIXTIME(@start, $lowerFormat) + INTERVAL 1 DAY) - @start, + start > startUpper, + UNIX_TIMESTAMP(FROM_UNIXTIME(start, $lowerFormat) + INTERVAL 1 DAY) - start, IF( - @start < @startLower, - @startLower - @start, + start < startLower, + startLower - start, 0 ) ) + + # Time after the last daily bound gets substracted. - IF( - @end > @endUpper, - @end - (@endUpper + 1), + end > endUpper, + end - (endUpper + 1), IF( - @end < @endLower, - @end - (UNIX_TIMESTAMP(FROM_UNIXTIME(@end, $upperFormat) - INTERVAL 1 DAY) + 1), + end < endLower, + end - (UNIX_TIMESTAMP(FROM_UNIXTIME(end, $upperFormat) - INTERVAL 1 DAY) + 1), 0 ) ) - - ( TO_DAYS(FROM_UNIXTIME(@end, '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(@start, '%y-%m-%d')) - - 2 - + IF(@start <= @startUpper, 1, 0) - + IF(@end >= @endLower, 1, 0) - ) * ((24 - ($upperTimeBound - $lowerTimeBound)) * 3600) + # Time between the daily bounds get substracted. + - ( daysDiff - 2 + + IF(start <= startUpper, 1, 0) + + IF(end >= endLower, 1, 0) + ) * ((24 - ($upperTimeBound - $lowerTimeBound)) * 3600) + + # If the session crossed a clock change (to/from daylight saving time), the last substraction may have substracted + # one hour too much or too little. This IF will correct this. - IF( - @leftBound := IF(@start <= @startUpper, @startUpper, UNIX_TIMESTAMP(FROM_UNIXTIME(@start, $upperFormat) + INTERVAL 1 DAY)) - < @rightBound := IF(@end >= @endLower, @endLower, UNIX_TIMESTAMP(FROM_UNIXTIME(@end, $lowerFormat) - INTERVAL 1 DAY)), + innerStart < innerEnd, IF( - @timeDiff := ( - (date_format(from_unixtime(@leftBound), '%H') - - date_format(convert_tz(from_unixtime(@leftBound), @@session.time_zone, '+00:00'), '%H') + 24) % 24 - - - (date_format(from_unixtime(@rightBound), '%H') - - date_format(convert_tz(from_unixtime(@rightBound), @@session.time_zone, '+00:00'), '%H') + 24) % 24) = 1 - AND ($lowerTimeBound >= 2 OR $upperTimeBound <= 2), + timeDiff = 1 AND ($lowerTimeBound >= 2 OR $upperTimeBound <= 2), 3600, - IF(@timeDiff = -1 AND ($lowerTimeBound >= 3 OR $upperTimeBound <= 3), -3600, 0) + IF(timeDiff = -1 AND ($lowerTimeBound >= 3 OR $upperTimeBound <= 3), -3600, 0) ), 0 ) + ) as 'length', - dateline, - data, + + IF(end < endUpper AND end > endLower AND end < $to, end, 0) as endInBound, + machineuuid - from statistic - where dateline+data >= $from and dateline <= $to and typeid = '$typeid' - and ( - (@daysDiff := (TO_DAYS(FROM_UNIXTIME(dateline+data, '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(dateline, '%y-%m-%d'))) = 0 and (dateline <= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat)) and dateline+data >= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat)))) + + # These nested selects are necessary because some things need to be calculated before others. + # (e.g. start is needed to calculate startLower) + from ( + select + *, + + # timeDiff is the clock change between leftBound and Rightbound. ( 0 = no clock change) + ((CAST(date_format(from_unixtime(innerStart), '%H') as SIGNED) - + CAST(date_format(convert_tz(from_unixtime(innerStart), @@session.time_zone, '+00:00'), '%H') as SIGNED) + 24) % 24 + - + (CAST(date_format(from_unixtime(innerEnd), '%H') as SIGNED) - + CAST(date_format(convert_tz(from_unixtime(innerEnd), @@session.time_zone, '+00:00'), '%H') as SIGNED) + 24) % 24) as timeDiff + from ( + select + *, + + # innerStart and innerEnd are start and end but excluding the time before the first daily upper bound and after the last daily lower bound. + CAST(IF(start <= startUpper, startUpper, UNIX_TIMESTAMP(FROM_UNIXTIME(start, $upperFormat) + INTERVAL 1 DAY)) as UNSIGNED) as innerStart, + CAST(IF(end >= endLower, endLower, UNIX_TIMESTAMP(FROM_UNIXTIME(end, $lowerFormat) - INTERVAL 1 DAY)) as UNSIGNED) as innerEnd + from ( + select + *, + + # daysDiff = how many different days the start and end are apart (0 = start and end on the same day) + (TO_DAYS(FROM_UNIXTIME(end, '%y-%m-%d')) - TO_DAYS(FROM_UNIXTIME(start, '%y-%m-%d'))) as daysDiff, + + # startLower = lower daily time bound on the starting day + CAST(UNIX_TIMESTAMP(FROM_UNIXTIME(start, $lowerFormat)) as UNSIGNED) as startLower, + # startUpper = upper daily time bound on the starting day + CAST(UNIX_TIMESTAMP(FROM_UNIXTIME(start, $upperFormat)) as UNSIGNED) as startUpper, + # endLower = lower daily time bound on the ending day + CAST(UNIX_TIMESTAMP(FROM_UNIXTIME(end, $lowerFormat)) as UNSIGNED) as endLower, + # endUpper = upper daily time bound on the ending day + CAST(UNIX_TIMESTAMP(FROM_UNIXTIME(end, $upperFormat)) as UNSIGNED) as endUpper + from ( + # Statistic logs (combined with currently offline machines if offline times are requested) . + select CAST(IF(dateline < $from, $from, dateline) as UNSIGNED) as start, + CAST(IF(dateline+data > $to, $to, dateline+data) as UNSIGNED) as end, + typeid, machineuuid, 'statistic' + from statistic where dateline+data >= $from and dateline <= $to and typeid = '$typeid' + $union + ) t + ) t + ) t + ) t + + + # Filter out the session that are at least overlapping with the time bounds. + where ( + (daysDiff = 0 and (start <= UNIX_TIMESTAMP(FROM_UNIXTIME(start, $upperFormat)) and end >= UNIX_TIMESTAMP(FROM_UNIXTIME(end, $lowerFormat)))) or - (@daysDiff = 1 and (dateline <= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline, $upperFormat)) or dateline+data >= UNIX_TIMESTAMP(FROM_UNIXTIME(dateline+data, $lowerFormat)))) + (daysDiff = 1 and (start <= UNIX_TIMESTAMP(FROM_UNIXTIME(start, $upperFormat)) or end >= UNIX_TIMESTAMP(FROM_UNIXTIME(end, $lowerFormat)))) or - @daysDiff >= 2 + daysDiff >= 2 ) "; return "(".$queryString.")"; -- cgit v1.2.3-55-g7522