From 701d9b01775a6befa0aafedd1e2d56237ad3ed58 Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Fri, 17 Jan 2020 17:56:46 +0100
Subject: [statistics_reporting] Don't consider machines not recently used

... machine must have been running in the previous 30 days, relative to
the "from" timestamp of selected interval.
---
 .../statistics_reporting/inc/queries.inc.php              | 15 +++++++++------
 1 file changed, 9 insertions(+), 6 deletions(-)

diff --git a/modules-available/statistics_reporting/inc/queries.inc.php b/modules-available/statistics_reporting/inc/queries.inc.php
index 58e9e63b..39e43765 100644
--- a/modules-available/statistics_reporting/inc/queries.inc.php
+++ b/modules-available/statistics_reporting/inc/queries.inc.php
@@ -13,11 +13,12 @@ class Queries
 		}
 	}
 
-	public static function getClientStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24)
+	public static function getClientStatistics(int $from, int $to, $lowerTimeBound = 0, $upperTimeBound = 24)
 	{
+		$fromCutoff = $from - 86400 * 30;
 		$res = Database::simpleQuery("SELECT m.machineuuid, m.hostname, m.clientip,
 				m.locationid, m.firstseen -- , m.lastboot, m.logintime, m.state
- 				FROM machine m WHERE firstseen <= $to"); // " WHERE lastseen >= :from", compact('from'));
+ 				FROM machine m WHERE firstseen <= $to AND lastseen > $fromCutoff"); // " WHERE lastseen >= :from", compact('from'));
 		$machines = self::getStats3($res, $from, $to, $lowerTimeBound, $upperTimeBound);
 		foreach ($machines as &$machine) {
 			$machine['medianSessionLength'] = self::calcMedian($machine['sessions']);
@@ -27,11 +28,12 @@ class Queries
 		return $machines;
 	}
 
-	public static function getLocationStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24)
+	public static function getLocationStatistics(int $from, int $to, $lowerTimeBound = 0, $upperTimeBound = 24)
 	{
+		$fromCutoff = $from - 86400 * 30;
 		$res = Database::simpleQuery("SELECT m.machineuuid, m.hostname, m.clientip,
 				m.locationid, m.firstseen -- , m.lastboot, m.logintime, m.state
- 				FROM machine m WHERE firstseen <= $to"); // " WHERE lastseen >= :from", compact('from'));
+ 				FROM machine m WHERE firstseen <= $to AND lastseen > $fromCutoff"); // " WHERE lastseen >= :from", compact('from'));
 		$machines = self::getStats3($res, $from, $to, $lowerTimeBound, $upperTimeBound);
 		$locations = [];
 		$keys = ['locationid', 'totalTime', 'totalOffTime', 'totalSessionTime', 'totalStandbyTime', 'totalIdleTime', 'totalIdleTime', 'longSessions', 'shortSessions', 'sessions'];
@@ -58,11 +60,12 @@ class Queries
 		return $locations;
 	}
 
-	public static function getOverallStatistics($from, $to, $lowerTimeBound = 0, $upperTimeBound = 24)
+	public static function getOverallStatistics(int $from, int $to, $lowerTimeBound = 0, $upperTimeBound = 24)
 	{
+		$fromCutoff = $from - 86400 * 30;
 		$res = Database::simpleQuery("SELECT m.machineuuid, m.hostname, m.clientip,
 				m.locationid, m.firstseen -- , m.lastboot, m.logintime, m.state
- 				FROM machine m WHERE firstseen <= $to"); // " WHERE lastseen >= :from", compact('from'));
+ 				FROM machine m WHERE firstseen <= $to AND lastseen > $fromCutoff"); // " WHERE lastseen >= :from", compact('from'));
 		$machines = self::getStats3($res, $from, $to, $lowerTimeBound, $upperTimeBound);
 		$total = false;
 		$keys = ['totalTime', 'totalOffTime', 'totalSessionTime', 'totalStandbyTime', 'totalIdleTime', 'totalIdleTime', 'longSessions', 'shortSessions', 'sessions'];
-- 
cgit v1.2.3-55-g7522