diff options
Diffstat (limited to 'modules-available/statistics_reporting/inc/queries.inc.php')
| -rw-r--r-- | modules-available/statistics_reporting/inc/queries.inc.php | 217 |
1 files changed, 217 insertions, 0 deletions
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.")"; + } +} + |