[statistics_reporting] Extend remote report by hwstats, date ranges, userstats

This implements #3108

5 files changed, 119 insertions, 12 deletions

diff --git a/modules-available/statistics_reporting/hooks/cron.inc.php b/modules-available/statistics_reporting/hooks/cron.inc.php
index afb18a23..14597f7d 100644
--- a/modules-available/statistics_reporting/hooks/cron.inc.php
+++ b/modules-available/statistics_reporting/hooks/cron.inc.php
@@ -7,10 +7,9 @@ if (RemoteReport::isReportingEnabled()) {
 	while ($nextReporting <= time()) {
 		RemoteReport::writeNextReportingTimestamp();
 
-		$from = strtotime("-7 days", $nextReporting);
 		$to = $nextReporting;
 
-		$statisticsReport = json_encode(RemoteReport::generateReport($from, $to));
+		$statisticsReport = json_encode(RemoteReport::generateReport($to));
 
 		$params = array("action" => "statistics", "data" => $statisticsReport);
 
diff --git a/modules-available/statistics_reporting/inc/getdata.inc.php b/modules-available/statistics_reporting/inc/getdata.inc.php
index da3a9a26..ae4d5aa4 100644
--- a/modules-available/statistics_reporting/inc/getdata.inc.php
+++ b/modules-available/statistics_reporting/inc/getdata.inc.php
@@ -29,6 +29,7 @@ class GetData
 			$data["medianSessionLength_s"] = self::formatSeconds($data["medianSessionLength"]);
 			$data["totalOffTime_s"] = self::formatSeconds($data["totalOffTime"]);
 		}
+		$data['uniqueUsers'] = Queries::getUniqueUserCount(self::$from, self::$to);
 
 		return $data;
 	}
diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php
index 2269e764..1a125c9e 100644
--- a/modules-available/statistics_reporting/inc/queries.inc.php
+++ b/modules-available/statistics_reporting/inc/queries.inc.php
@@ -213,5 +213,68 @@ class Queries
 		";
 		return "(".$queryString.")";
 	}
+
+	public static function getDozmodStats($from, $to)
+	{
+		if (!Module::isAvailable('dozmod'))
+			return array('disabled' => true);
+
+		$return = array();
+		$return['vms'] = Database::queryFirst("SELECT Count(*) AS `total`, Sum(If(createtime >= $from, 1, 0)) AS `new`,
+			Sum(If(updatetime >= $from, 1, 0)) AS `updated`, Sum(If(latestversionid IS NOT NULL, 1, 0)) AS `valid`
+			FROM sat.imagebase
+			WHERE createtime <= $to");
+		$return['lectures'] = Database::queryFirst("SELECT Count(*) AS `total`, Sum(If(createtime >= $from, 1, 0)) AS `new`,
+			Sum(If(updatetime >= $from, 1, 0)) AS `updated`,
+			Sum(If((($from BETWEEN starttime AND endtime) OR ($to BETWEEN starttime AND endtime)) AND isenabled <> 0, 1, 0)) AS `valid`
+			FROM sat.lecture
+			WHERE createtime <= $to");
+		$return['users'] = Database::queryFirst("SELECT Count(*) AS `total`, Count(DISTINCT organizationid) AS `organizations`
+			FROM sat.user
+			WHERE lastlogin >= $from");
+		return $return;
+	}
+
+	public static function getAggregatedMachineStats($from)
+	{
+		$return = array();
+		$return['location'] = Database::queryAll("SELECT MD5(CONCAT(locationid, :salt)) AS `location`, Count(*) AS `count`
+			FROM machine
+			WHERE lastseen >= $from
+			GROUP BY locationid",
+			array('salt' => GetData::$salt));
+		$prev = 0;
+		$str = ' ';
+		foreach (array(0.5, 1, 1.5, 2, 3, 4, 6, 8, 10, 12, 16, 20, 24, 28, 32, 40, 48, 64, 72, 80, 88, 96, 128, 192, 256) as $val) {
+			$str .= 'WHEN mbram < ' . round(($val + $prev) * 512) . " THEN '" . $prev . "' ";
+			$prev = $val;
+		}
+		$return['ram'] = Database::queryAll("SELECT CASE $str ELSE 'OVER 9000' END AS `gbram`, Count(*) AS `total`
+			FROM machine
+			WHERE lastseen >= $from
+			GROUP BY gbram");
+		foreach (array('cpumodel', 'systemmodel', 'realcores', 'kvmstate') as $key) {
+			$return[$key] = Database::queryAll("SELECT $key, Count(*) AS `total`
+				FROM machine
+				WHERE lastseen >= $from
+				GROUP BY $key");
+		}
+		return $return;
+	}
+
+	/**
+	 * @param int $from start timestamp
+	 * @param int $to end timestamp
+	 * @return int count of user active in timespan
+	 */
+	public static function getUniqueUserCount($from, $to)
+	{
+		$res = Database::queryFirst("SELECT Count(DISTINCT username) as `total`
+			FROM statistic
+			WHERE (dateline BETWEEN $from AND $to) AND typeid = '.vmchooser-session-name'
+			GROUP BY username");
+		return (int)$res['total'];
+	}
+
 }
 
diff --git a/modules-available/statistics_reporting/inc/remotereport.inc.php b/modules-available/statistics_reporting/inc/remotereport.inc.php
index 4c5f604b..68a3e95e 100644
--- a/modules-available/statistics_reporting/inc/remotereport.inc.php
+++ b/modules-available/statistics_reporting/inc/remotereport.inc.php
@@ -63,19 +63,63 @@ class RemoteReport
 	 * Generate the multi-dimensional array containing the anonymized
 	 * (weekly) statistics to report.
 	 *
-	 * @param int $from start timestamp
 	 * @param int $to end timestamp
+	 * @param int[] $days list of days to generate aggregated stats for
 	 * @return array wrapped up statistics, ready for reporting
 	 */
-	public static function generateReport($from, $to) {
-		GetData::$from = $from;
-		GetData::$to = $to;
+	public static function generateReport($to, $days = false) {
+		if ($days === false) {
+			$days = [7, 30, 90];
+		}
 		GetData::$salt = bin2hex(Util::randomBytes(20, false));
-		$data = GetData::total(GETDATA_ANONYMOUS);
-		$data['perLocation'] = GetData::perLocation(GETDATA_ANONYMOUS);
-		$data['perVM'] = GetData::perVM(GETDATA_ANONYMOUS);
-		$data['tsFrom'] = $from;
-		$data['tsTo'] = $to;
+		GetData::$lowerTimeBound = 7;
+		GetData::$upperTimeBound = 20;
+		$result = array();
+		foreach ($days as $day) {
+			if (isset($result['days' . $day]))
+				continue;
+			$from = strtotime("-{$day} days", $to);
+			GetData::$from = $from;
+			GetData::$to = $to;
+			$data = array('total' => GetData::total(GETDATA_ANONYMOUS));
+			$data['perLocation'] = GetData::perLocation(GETDATA_ANONYMOUS);
+			$data['perVM'] = GetData::perVM(GETDATA_ANONYMOUS);
+			$data['tsFrom'] = $from;
+			$data['tsTo'] = $to;
+			$data['dozmod'] = Queries::getDozmodStats($from, $to);
+			$data['machines'] = Queries::getAggregatedMachineStats($from);
+			$result['days' . $day] = $data;
+		}
+		$result['server'] = self::getLocalHardware();
+		$result['version'] = CONFIG_FOOTER;
+		return $result;
+	}
+
+	private function getLocalHardware()
+	{
+		$cpuInfo = file_get_contents('/proc/cpuinfo');
+		$uptime = file_get_contents('/proc/uptime');
+		$memInfo = file_get_contents('/proc/meminfo');
+		preg_match_all('/\b(\w+):\s+(\d+)\s/s', $memInfo, $out, PREG_SET_ORDER);
+		$mem = array();
+		foreach ($out as $e) {
+			$mem[$e[1]] = $e[2];
+		}
+		//
+		$data = array();
+		$data['cpuCount'] = preg_match_all('/\bprocessor\s+:\s+(.*)$/m', $cpuInfo, $out);
+		if ($data['cpuCount'] > 0) {
+			$data['cpuModel'] = $out[1][0];
+		}
+		if (preg_match('/^(\d+)\D/', $uptime, $out)) {
+			$data['uptime'] = $out[1];
+		}
+		if (isset($mem['MemTotal']) && isset($mem['MemFree']) && isset($mem['SwapTotal'])) {
+			$data['memTotal'] = $mem['MemTotal'];
+			$data['memFree'] = ($mem['MemFree'] + $mem['Buffers'] + $mem['Cached']);
+			$data['swapTotal'] = $mem['SwapTotal'];
+			$data['swapUsed'] = ($mem['SwapTotal'] - $mem['SwapFree']);
+		}
 		return $data;
 	}
 
diff --git a/modules-available/statistics_reporting/page.inc.php b/modules-available/statistics_reporting/page.inc.php
index 6bd908a5..52accaea 100644
--- a/modules-available/statistics_reporting/page.inc.php
+++ b/modules-available/statistics_reporting/page.inc.php
@@ -58,7 +58,7 @@ class Page_Statistics_Reporting extends Page
 		// Get report - fetch data exactly the way it would automatically be reported
 		// so the user can know what is going on
 		if ($this->action === 'getreport') {
-			$report = RemoteReport::generateReport(strtotime('-7 days'), time('now'));
+			$report = RemoteReport::generateReport(time());
 			Header('Content-Disposition: attachment; filename=remote-report.json');
 			Header('Content-Type: application/json; charset=utf-8');
 			die(json_encode($report));