diff options
Diffstat (limited to 'modules-available/statistics_reporting/inc')
3 files changed, 448 insertions, 0 deletions
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..f65ee868 --- /dev/null +++ b/modules-available/statistics_reporting/inc/getdata.inc.php @@ -0,0 +1,147 @@ +<?php + +define('GETDATA_ANONYMOUS', 1); +define('GETDATA_PRINTABLE', 2); + +class GetData +{ + public static $from; + public static $to; + public static $lowerTimeBound = 0; + public static $upperTimeBound = 24; + public static $salt; + + // total + public static function total($flags = 0) { + $printable = 0 !== ($flags & GETDATA_PRINTABLE); + // 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('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['totalOffTime'] = $row['timeOff']; + + if ($printable) { + $data["totalTime_s"] = self::formatSeconds($data["totalTime"]); + $data["medianSessionLength_s"] = self::formatSeconds($data["medianSessionLength"]); + $data["totalOffTime_s"] = self::formatSeconds($data["totalOffTime"]); + } + + return $data; + } + + // per location + 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(); + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $median = self::calcMedian(self::calcMedian($row['medianSessionLength'])); + $entry = array( + 'location' => ($anonymize ? $row['locHash'] : $row['locName']), + 'totalTime' => $row['timeSum'], + 'medianSessionLength' => $median, + 'totalOffTime' => $row['offlineSum'], + 'longSessions' => $row['longSessions'], + 'shortSessions' => $row['shortSessions'] + ); + if (!$anonymize) { + $entry['locationId'] = $row['locId']; + } + if ($printable) { + $entry['totalTime_s'] = self::formatSeconds($row['timeSum']); + $entry['medianSessionLength_s'] = self::formatSeconds($median); + $entry['totalOffTime_s'] = self::formatSeconds($row['offlineSum']); + } + $data[] = $entry; + } + return $data; + } + + // per client + 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(); + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $median = self::calcMedian(self::calcMedian($row['medianSessionLength'])); + $entry = array( + 'hostname' => ($anonymize ? $row['clientHash'] : $row['clientName']), + 'totalTime' => $row['timeSum'], + 'medianSessionLength' => $median, + 'totalOffTime' => $row['offlineSum'], + 'lastStart' => $row['lastStart'], + 'lastLogout' => $row['lastLogout'], + 'longSessions' => $row['longSessions'], + 'shortSessions' => $row['shortSessions'], + 'location' => ($anonymize ? $row['locHash'] : $row['locName']), + ); + if (!$anonymize) { + $entry['locationId'] = $row['locId']; + } + if ($printable) { + $entry['totalTime_s'] = self::formatSeconds($row['timeSum']); + $entry['medianSessionLength_s'] = self::formatSeconds($median); + $entry['totalOffTime_s'] = self::formatSeconds($row['offlineSum']); + $entry['lastStart_s'] = $row['lastStart'] == 0 ? "" : date(DATE_ISO8601, $row['lastStart']); + $entry['lastLogout_s'] = $row['lastLogout'] == 0 ? "" : date(DATE_ISO8601, $row['lastLogout']); + } + $data[] = $entry; + } + return $data; + } + + // per user + 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'; + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $data[] = array('user' => $row[$user], 'sessions' => $row['count']); + } + return $data; + } + + + // per vm + 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'; + while ($row = $res->fetch(PDO::FETCH_ASSOC)) { + $data[] = array('vm' => $row[$vm], 'sessions' => $row['count']); + } + return $data; + } + + + + // Format $seconds into ".d .h .m .s" format (day, hour, minute, second) + private static function formatSeconds($seconds) + { + return sprintf('%dd, %02d:%02d:%02d', $seconds / (3600*24), ($seconds % (3600*24)) / 3600, ($seconds%3600) / 60, $seconds%60); + } + + // 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 diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php new file mode 100644 index 00000000..3e944c92 --- /dev/null +++ b/modules-available/statistics_reporting/inc/queries.inc.php @@ -0,0 +1,217 @@ +<?php + + +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::translate('notAssigned', true); + Database::exec("SET SESSION group_concat_max_len = 1000000000"); + $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 + GROUP BY machine.machineuuid + ) t1 + 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)); + + 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::translate('notAssigned', true); + Database::exec("SET SESSION group_concat_max_len = 1000000000"); + $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 + 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 + GROUP BY machine.locationid + ) t2 + ON t1.locId = t2.locId", 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(CONCAT(username, :salt))) AS userHash, 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 + 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(CONCAT(data, :salt)) AS vmHash, 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 + ORDER BY 2 DESC", array("salt" => GetData::$salt)); + return $res; + } + + //Total Data: Time Online, Median Time Online, Number of Sessions > 60Sec, Number of Sessions < 60Sec + public static function getOverallStatistics ($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24) { + Database::exec("SET SESSION group_concat_max_len = 1000000000"); + $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', $from, $to, $lowerTimeBound, $upperTimeBound)." sessionTable"); + return $res; + } + + // Total Data(2): Time Offline + 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', $from, $to, $lowerTimeBound, $upperTimeBound)." offlineTable"); + return $res; + } + + // 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 + + # The whole length of the session/offline time. + (end-start + + # Now the time that is not within the daily time bounds is subtracted. + # 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 is subtracted. + - IF( + start > startUpper, + UNIX_TIMESTAMP(FROM_UNIXTIME(start, $lowerFormat) + INTERVAL 1 DAY) - start, + IF( + start < startLower, + startLower - start, + 0 + ) + ) + + # Time after the last daily bound is subtracted. + - IF( + end > endUpper, + end - (endUpper + 1), + IF( + end < endLower, + end - (UNIX_TIMESTAMP(FROM_UNIXTIME(end, $upperFormat) - INTERVAL 1 DAY) + 1), + 0 + ) + ) + + # Time between the daily bounds is subtracted. + - ( 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 subtraction may have subtracted + # one hour too much or too little. This IF will correct this. + - IF( + innerStart < innerEnd, + IF( + timeDiff = 1 AND ($lowerTimeBound >= 2 OR $upperTimeBound <= 2), + 3600, + IF(timeDiff = -1 AND ($lowerTimeBound >= 3 OR $upperTimeBound <= 3), -3600, 0) + ), + 0 + ) + + ) as 'length', + + IF(end < endUpper AND end > endLower AND end < $to, end, 0) as endInBound, + + machineuuid + + + # 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 innerStart and innerEnd. ( 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 (start <= UNIX_TIMESTAMP(FROM_UNIXTIME(start, $upperFormat)) or end >= UNIX_TIMESTAMP(FROM_UNIXTIME(end, $lowerFormat)))) + or + daysDiff >= 2 + ) + "; + return "(".$queryString.")"; + } +} + diff --git a/modules-available/statistics_reporting/inc/remotereport.inc.php b/modules-available/statistics_reporting/inc/remotereport.inc.php new file mode 100644 index 00000000..7aad8b3a --- /dev/null +++ b/modules-available/statistics_reporting/inc/remotereport.inc.php @@ -0,0 +1,84 @@ +<?php + +class RemoteReport +{ + + const ENABLED_ID = 'statistics-reporting-enabled'; + const NEXT_SUBMIT_ID = 'statistics-reporting-next'; + + /** + * Enable or disable remote reporting of usage statistics. + * + * @param bool|string $isEnabled true or 'on' if reporting should be enabled + */ + public static function setReportingEnabled($isEnabled) + { + $value = ($isEnabled === true || $isEnabled === 'on') ? 'on' : ''; + Property::set(self::ENABLED_ID, $value, 60 * 24 * 14); + } + + /** + * Returns whether remote reporting is enabled or not. + * + * @return bool true if reporting is on, false if off + */ + public static function isReportingEnabled() + { + return Property::get(self::ENABLED_ID, false) === 'on'; + } + + /** + * Get the timestamp of the end of the next 7 day interval to + * report statistics for. Usually if this is < time() you want + * to generate the report. + * + * @return int timestamp of the end of the reporting time frame + */ + public static function getReportingTimestamp() + { + $ts = Property::get(self::NEXT_SUBMIT_ID, 0); + if ($ts === 0) { + // No timestamp stored yet - might be a fresh install + // schedule for next time + self::updateNextReportingTimestamp(); + $ts = Property::get(self::NEXT_SUBMIT_ID, 0); + } elseif ($ts < strtotime('last monday')) { + // Too long ago, move forward to last monday + $ts = strtotime('last monday'); + } + return $ts; + } + + /** + * Update the timestamp of the next scheduled statistics report. + * This sets the end of the next 7 day interval to the start of + * next monday (00:00). + */ + public static function writeNextReportingTimestamp() + { + Property::set(self::NEXT_SUBMIT_ID, strtotime('next monday'), 60 * 24 * 14); + } + + /** + * Generate the multi-dimensional array containing the anonymized + * (weekly) statistics to report. + * + * @param $from start timestamp + * @param $to end timestamp + * @return array wrapped up statistics, ready for reporting + */ + public static function generateReport($from, $to) { + GetData::$from = $from; + GetData::$to = $to; + GetData::$salt = bin2hex(Util::randomBytes(20)); + $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; + } + +}
\ No newline at end of file |