From d2d7f27f58235db9ac428b9dd952e7b108f3c381 Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Fri, 9 Sep 2016 18:16:46 +0200
Subject: [locations/statistics] Make machine counting more consistent
---
modules-available/locations/inc/location.inc.php | 5 ++-
modules-available/locations/page.inc.php | 39 +++++++++++++---------
.../locations/templates/locations.html | 4 +++
modules-available/statistics/inc/filter.inc.php | 2 +-
modules-available/statistics/page.inc.php | 1 +
.../statistics/templates/clientlist.html | 2 +-
6 files changed, 34 insertions(+), 19 deletions(-)
diff --git a/modules-available/locations/inc/location.inc.php b/modules-available/locations/inc/location.inc.php
index 81ee342f..bde07e61 100644
--- a/modules-available/locations/inc/location.inc.php
+++ b/modules-available/locations/inc/location.inc.php
@@ -140,6 +140,7 @@ class Location
foreach ($tree as $node) {
$output[(int)$node['locationid']] = array(
'locationid' => $node['locationid'],
+ 'parentlocationid' => $node['parentlocationid'],
'locationname' => $node['locationname'],
'locationpad' => str_repeat('--', $depth),
'isleaf' => empty($node['children']),
@@ -287,7 +288,7 @@ class Location
/**
* @return array|bool assoc array mapping from locationid to subnets
*/
- public static function getSubnetsByLocation(&$overlapSelf, &$overlapOther)
+ public static function getSubnetsByLocation(&$overlapSelf, &$overlapOther, $recursive = true)
{
$locs = self::getLocationsAssoc();
$subnets = self::getSubnets();
@@ -307,6 +308,8 @@ class Location
'startaddr' => $subnet['startaddr'],
'endaddr' => $subnet['endaddr']
);
+ if (!$recursive)
+ break;
$lid = $locs[$lid]['parentlocationid'];
}
}
diff --git a/modules-available/locations/page.inc.php b/modules-available/locations/page.inc.php
index 26c5d1ed..5f3c87dd 100644
--- a/modules-available/locations/page.inc.php
+++ b/modules-available/locations/page.inc.php
@@ -290,30 +290,37 @@ class Page_Locations extends Page
}
}
+ private function queryMachineCount($lid, $subnets)
+ {
+ if (!isset($subnets[$lid]))
+ return 0;
+ $loc =& $subnets[$lid];
+ if (empty($loc['subnets'])) {
+ $query = "SELECT Count(*) AS cnt FROM machine WHERE locationid = :locationid";
+ } else {
+ $query = "SELECT Count(*) AS cnt FROM machine WHERE locationid = :locationid OR (locationid IS NULL AND (0";
+ foreach ($loc['subnets'] as $sub) {
+ $query .= ' OR INET_ATON(clientip) BETWEEN ' . $sub['startaddr'] . ' AND ' . $sub['endaddr'];
+ }
+ $query .= '))';
+ }
+ $ret = Database::queryFirst($query, array('locationid' => $lid));
+ return $ret['cnt'];
+ }
+
private function showLocationList()
{
$overlapSelf = $overlapOther = true;
- $subnets = Location::getSubnetsByLocation($overlapSelf, $overlapOther);
+ $subnetsFlat = Location::getSubnetsByLocation($overlapSelf, $overlapOther, false);
+ $subnetsRecursive = Location::getSubnetsByLocation($overlapSelf, $overlapOther, true);
$locs = Location::getLocations(0, 0, false, true);
// Statistics: Count machines for each subnet
$unassigned = false;
if (Module::get('statistics') !== false) {
foreach ($locs as &$location) {
$lid = (int)$location['locationid'];
- if (!isset($subnets[$lid]))
- continue;
- $loc =& $subnets[$lid];
- if (empty($loc['subnets'])) {
- $query = "SELECT Count(*) AS cnt FROM machine WHERE locationid = :locationid";
- } else {
- $query = "SELECT Count(*) AS cnt FROM machine WHERE locationid = :locationid OR (locationid IS NULL AND (0";
- foreach ($loc['subnets'] as $sub) {
- $query .= ' OR INET_ATON(clientip) BETWEEN ' . $sub['startaddr'] . ' AND ' . $sub['endaddr'];
- }
- $query .= '))';
- }
- $ret = Database::queryFirst($query, array('locationid' => $lid));
- $location['clientCount'] = $ret['cnt'];
+ $location['clientCount'] = $this->queryMachineCount($lid, $subnetsFlat);
+ $location['clientCountSum'] = $this->queryMachineCount($lid, $subnetsRecursive);
}
$res = Database::queryFirst("SELECT Count(*) AS cnt FROM machine m"
. " LEFT JOIN subnet s ON (INET_ATON(m.clientip) BETWEEN s.startaddr AND s.endaddr)"
@@ -432,7 +439,7 @@ class Page_Locations extends Page
if (Module::get('statistics') !== false) {
$mres = Database::simpleQuery("SELECT lastseen, logintime FROM machine"
. " INNER JOIN subnet ON (INET_ATON(machine.clientip) BETWEEN startaddr AND endaddr)"
- . " WHERE subnet.locationid = :lid OR machine.locationid = :lid", array('lid' => $locationId));
+ . " WHERE (subnet.locationid = :lid AND machine.locationid IS NULL) OR machine.locationid = :lid", array('lid' => $locationId));
$DL = time() - 605;
while ($row = $mres->fetch(PDO::FETCH_ASSOC)) {
$count++;
diff --git a/modules-available/locations/templates/locations.html b/modules-available/locations/templates/locations.html
index 5fec2e2e..99fb59fd 100644
--- a/modules-available/locations/templates/locations.html
+++ b/modules-available/locations/templates/locations.html
@@ -25,6 +25,10 @@
|
{{#havestatistics}}
{{clientCount}}
+
+ /
+ {{clientCountSum}}
+
{{/havestatistics}}
|
diff --git a/modules-available/statistics/inc/filter.inc.php b/modules-available/statistics/inc/filter.inc.php
index 19d38140..b35075bc 100644
--- a/modules-available/statistics/inc/filter.inc.php
+++ b/modules-available/statistics/inc/filter.inc.php
@@ -220,7 +220,7 @@ class LocationFilter extends Filter
$joins[] = ' INNER JOIN subnet ON (INET_ATON(clientip) BETWEEN startaddr AND endaddr) ';
$args['lid'] = $this->argument;
$neg = $this->operator == '=' ? '' : 'NOT';
- return "$neg (subnet.locationid = :lid OR machine.locationid = :lid)";
+ return "$neg ((subnet.locationid = :lid AND machine.locationid IS NULL) OR machine.locationid = :lid)";
}
}
}
diff --git a/modules-available/statistics/page.inc.php b/modules-available/statistics/page.inc.php
index c8fcaa43..9383944e 100644
--- a/modules-available/statistics/page.inc.php
+++ b/modules-available/statistics/page.inc.php
@@ -532,6 +532,7 @@ class Page_Statistics extends Page
$rows[] = $row;
}
Render::addTemplate('clientlist', array(
+ 'rowCount' => count($rows),
'rows' => $rows,
'query' => $this->query,
'delimiter' => Filter::DELIMITER,
diff --git a/modules-available/statistics/templates/clientlist.html b/modules-available/statistics/templates/clientlist.html
index ca4b971a..2bac29fa 100644
--- a/modules-available/statistics/templates/clientlist.html
+++ b/modules-available/statistics/templates/clientlist.html
@@ -1,7 +1,7 @@
-{{lang_clientList}}
+{{lang_clientList}} ({{rowCount}})