From 6abbd2bf9a0dafa93e92928a269820eaa127b12b Mon Sep 17 00:00:00 2001 From: Simon Rettberg Date: Fri, 29 Jul 2016 12:18:36 +0200 Subject: [statistics] Make filtering more dynamic, remove hard cutoff from summary --- modules-available/statistics/inc/filter.inc.php | 394 +++++++++++---------- modules-available/statistics/inc/filterset.inc.php | 58 +-- modules-available/statistics/page.inc.php | 40 ++- .../statistics/templates/cpumodels.html | 8 +- .../statistics/templates/filterbox.html | 19 +- modules-available/statistics/templates/id44.html | 7 +- .../statistics/templates/kvmstate.html | 6 +- modules-available/statistics/templates/memory.html | 6 +- 8 files changed, 310 insertions(+), 228 deletions(-) (limited to 'modules-available') diff --git a/modules-available/statistics/inc/filter.inc.php b/modules-available/statistics/inc/filter.inc.php index a3f1cbb5..ecf222e9 100644 --- a/modules-available/statistics/inc/filter.inc.php +++ b/modules-available/statistics/inc/filter.inc.php @@ -2,209 +2,235 @@ /* base class with rudimentary SQL generation abilities. * WARNING: argument is escaped, but $column and $operator are passed unfiltered into SQL */ + class Filter { - /** - * Delimiter for js_selectize filters - */ - const DELIMITER = '~,~'; - - public $column; - public $operator; - public $argument; - - public function __construct($column, $operator, $argument = null) - { - $this->column = trim($column); - $this->operator = trim($operator); - $this->argument = trim($argument); - } - /* returns a where clause and adds needed operators to the passed array */ - public function whereClause(&$args, &$joins) - { - global $unique_key; - $key = $this->column.'_arg' . ($unique_key++); - - /* check if we have to do some parsing*/ - if (Page_Statistics::$columns[$this->column]['type'] == 'date') { - $args[$key] = strtotime($this->argument); - } else { - $args[$key] = $this->argument; - } - - $op = $this->operator; - if ($this->operator == '~') { - $op = 'LIKE'; - } elseif ($this->operator == '!~') { - $op = 'NOT LIKE'; - } - - return $this->column.' '.$op.' :'.$key; - } - /* parse a query into an array of filters */ - public static function parseQuery($query) - { - $operators = ['<=', '>=', '!=', '!~', '=', '~', '<', '>']; - $filters = []; - foreach (explode(self::DELIMITER, $query) as $q) { - $q = trim($q); - /* find position of first operator */ - $pos = 10000; - $operator = false; - foreach ($operators as $op) { - $newpos = strpos($q, $op); - if ($newpos > -1 && ($newpos < $pos)) { - $pos = $newpos; - $operator = $op; - } - } - if ($pos == 10000) { - error_log("couldn't find operator in segment ".$q); - /* TODO */ - continue; - } - $lhs = trim(substr($q, 0, $pos)); - $rhs = trim(substr($q, $pos + strlen($operator))); - - if ($lhs == 'gbram') { - $filters[] = new RamGbFilter($operator, $rhs); - } elseif ($lhs == 'state') { - error_log('new state filter with ' . $rhs); - $filters[] = new StateFilter($operator, $rhs); - } elseif ($lhs == 'hddgb') { - $filters[] = new Id44Filter($operator, $rhs); - } elseif ($lhs == 'location') { - $filters[] = new LocationFilter($operator, $rhs); - } elseif ($lhs == 'subnet') { - $filters[] = new SubnetFilter($operator, $rhs); - } else { - if (array_key_exists($lhs, Page_Statistics::$columns) && Page_Statistics::$columns[$lhs]['column']) { - $filters[] = new Filter($lhs, $operator, $rhs); - } else { - Message::addError('invalid-filter-key', $lhs); - } - } - } - - return $filters; - } + /** + * Delimiter for js_selectize filters + */ + const DELIMITER = '~,~'; + + public $column; + public $operator; + public $argument; + + public function __construct($column, $operator, $argument = null) + { + $this->column = trim($column); + $this->operator = trim($operator); + $this->argument = trim($argument); + } + + /* returns a where clause and adds needed operators to the passed array */ + public function whereClause(&$args, &$joins) + { + global $unique_key; + $key = $this->column . '_arg' . ($unique_key++); + + /* check if we have to do some parsing*/ + if (Page_Statistics::$columns[$this->column]['type'] == 'date') { + $args[$key] = strtotime($this->argument); + } else { + $args[$key] = $this->argument; + } + + $op = $this->operator; + if ($this->operator == '~') { + $op = 'LIKE'; + } elseif ($this->operator == '!~') { + $op = 'NOT LIKE'; + } + + return $this->column . ' ' . $op . ' :' . $key; + } + + /* parse a query into an array of filters */ + public static function parseQuery($query) + { + $operators = ['<=', '>=', '!=', '!~', '=', '~', '<', '>']; + $filters = []; + if (empty($query)) + return $filters; + foreach (explode(self::DELIMITER, $query) as $q) { + $q = trim($q); + if (empty($q)) + continue; + // Special case: User pasted UUID, turn into filter + if (preg_match('/^\w{8}-\w{4}-\w{4}-\w{4}-\w{12}$/', $q)) { + $filters[] = new Filter('machineuuid', '=', $q); + continue; + } + // Special case: User pasted IP, turn into filter + if (preg_match('/^\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}$/', $q)) { + $filters[] = new Filter('clientip', '=', $q); + continue; + } + /* find position of first operator */ + $pos = 10000; + $operator = false; + foreach ($operators as $op) { + $newpos = strpos($q, $op); + if ($newpos > -1 && ($newpos < $pos)) { + $pos = $newpos; + $operator = $op; + } + } + if ($pos == 10000) { + error_log("couldn't find operator in segment " . $q); + /* TODO */ + continue; + } + $lhs = trim(substr($q, 0, $pos)); + $rhs = trim(substr($q, $pos + strlen($operator))); + + if ($lhs == 'gbram') { + $filters[] = new RamGbFilter($operator, $rhs); + } elseif ($lhs == 'state') { + error_log('new state filter with ' . $rhs); + $filters[] = new StateFilter($operator, $rhs); + } elseif ($lhs == 'hddgb') { + $filters[] = new Id44Filter($operator, $rhs); + } elseif ($lhs == 'location') { + $filters[] = new LocationFilter($operator, $rhs); + } elseif ($lhs == 'subnet') { + $filters[] = new SubnetFilter($operator, $rhs); + } else { + if (array_key_exists($lhs, Page_Statistics::$columns) && Page_Statistics::$columns[$lhs]['column']) { + $filters[] = new Filter($lhs, $operator, $rhs); + } else { + Message::addError('invalid-filter-key', $lhs); + } + } + } + + return $filters; + } } class RamGbFilter extends Filter { - public function __construct($operator, $argument) - { - parent::__construct('mbram', $operator, $argument); - } - public function whereClause(&$args, &$joins) - { - global $SIZE_RAM; - $lower = floor(Page_Statistics::findBestValue($SIZE_RAM, (int) $this->argument, false) * 1024 - 100); - $upper = ceil(Page_Statistics::findBestValue($SIZE_RAM, (int) $this->argument, true) * 1024 + 100); - if ($this->operator == '=') { - return " mbram BETWEEN $lower AND $upper"; - } elseif ($this->operator == '<') { - return " mbram < $lower"; - } elseif ($this->operator == '<=') { - return " mbram <= $upper"; - } elseif ($this->operator == '>') { - return " mbram > $upper"; - } elseif ($this->operator == '>=') { - return " mbram >= $lower"; - } elseif ($this->operator == '!=') { - return " (mbram < $lower OR mbram > $upper)"; - } else { - error_log("unimplemented operator in RamGbFilter: $this->operator"); - - return ' 1'; - } - } + public function __construct($operator, $argument) + { + parent::__construct('mbram', $operator, $argument); + } + + public function whereClause(&$args, &$joins) + { + global $SIZE_RAM; + $lower = floor(Page_Statistics::findBestValue($SIZE_RAM, (int)$this->argument, false) * 1024 - 100); + $upper = ceil(Page_Statistics::findBestValue($SIZE_RAM, (int)$this->argument, true) * 1024 + 100); + if ($this->operator == '=') { + return " mbram BETWEEN $lower AND $upper"; + } elseif ($this->operator == '<') { + return " mbram < $lower"; + } elseif ($this->operator == '<=') { + return " mbram <= $upper"; + } elseif ($this->operator == '>') { + return " mbram > $upper"; + } elseif ($this->operator == '>=') { + return " mbram >= $lower"; + } elseif ($this->operator == '!=') { + return " (mbram < $lower OR mbram > $upper)"; + } else { + error_log("unimplemented operator in RamGbFilter: $this->operator"); + + return ' 1'; + } + } } + class Id44Filter extends Filter { - public function __construct($operator, $argument) - { - parent::__construct('id44mb', $operator, $argument); - } - public function whereClause(&$args, &$joins) - { - global $SIZE_ID44; - $lower = floor(Page_Statistics::findBestValue($SIZE_ID44, $this->argument, false) * 1024 - 100); - $upper = ceil(Page_Statistics::findBestValue($SIZE_ID44, $this->argument, true) * 1024 + 100); - - if ($this->operator == '=') { - return " id44mb BETWEEN $lower AND $upper"; - } elseif ($this->operator == '!=') { - return " id44mb < $lower OR id44mb > $upper"; - } elseif ($this->operator == '<=') { - return " id44mb < $upper"; - } elseif ($this->operator == '>=') { - return " id44mb > $lower"; - } elseif ($this->operator == '<') { - return " id44mb < $lower"; - } elseif ($this->operator == '>') { - return " id44mb > $upper"; - } else { - error_log("unimplemented operator in Id44Filter: $this->operator"); - - return ' 1'; - } - } + public function __construct($operator, $argument) + { + parent::__construct('id44mb', $operator, $argument); + } + + public function whereClause(&$args, &$joins) + { + global $SIZE_ID44; + $lower = floor(Page_Statistics::findBestValue($SIZE_ID44, $this->argument, false) * 1024 - 100); + $upper = ceil(Page_Statistics::findBestValue($SIZE_ID44, $this->argument, true) * 1024 + 100); + + if ($this->operator == '=') { + return " id44mb BETWEEN $lower AND $upper"; + } elseif ($this->operator == '!=') { + return " id44mb < $lower OR id44mb > $upper"; + } elseif ($this->operator == '<=') { + return " id44mb < $upper"; + } elseif ($this->operator == '>=') { + return " id44mb > $lower"; + } elseif ($this->operator == '<') { + return " id44mb < $lower"; + } elseif ($this->operator == '>') { + return " id44mb > $upper"; + } else { + error_log("unimplemented operator in Id44Filter: $this->operator"); + + return ' 1'; + } + } } + class StateFilter extends Filter { - public function __construct($operator, $argument) - { - $this->operator = $operator; - $this->argument = $argument; - } - - public function whereClause(&$args, &$joins) - { - $neg = $this->operator == '!=' ? 'NOT ' : ''; - if ($this->argument === 'on') { - return " $neg (lastseen + 600 > UNIX_TIMESTAMP() ) "; - } elseif ($this->argument === 'off') { - return " $neg (lastseen + 600 < UNIX_TIMESTAMP() ) "; - } elseif ($this->argument === 'idle') { - return " $neg (lastseen + 600 > UNIX_TIMESTAMP() AND logintime = 0 ) "; - } elseif ($this->argument === 'occupied') { - return " $neg (lastseen + 600 > UNIX_TIMESTAMP() AND logintime <> 0 ) "; - } else { - Message::addError('invalid-filter-argument', 'state', $this->argument); - return ' 1'; - } - } + public function __construct($operator, $argument) + { + $this->operator = $operator; + $this->argument = $argument; + } + + public function whereClause(&$args, &$joins) + { + $neg = $this->operator == '!=' ? 'NOT ' : ''; + if ($this->argument === 'on') { + return " $neg (lastseen + 600 > UNIX_TIMESTAMP() ) "; + } elseif ($this->argument === 'off') { + return " $neg (lastseen + 600 < UNIX_TIMESTAMP() ) "; + } elseif ($this->argument === 'idle') { + return " $neg (lastseen + 600 > UNIX_TIMESTAMP() AND logintime = 0 ) "; + } elseif ($this->argument === 'occupied') { + return " $neg (lastseen + 600 > UNIX_TIMESTAMP() AND logintime <> 0 ) "; + } else { + Message::addError('invalid-filter-argument', 'state', $this->argument); + return ' 1'; + } + } } class LocationFilter extends Filter { - public function __construct($operator, $argument) { - parent::__construct('locationid', $operator, $argument); - } - - public function whereClause(&$args, &$joins) { - settype($this->argument, 'int'); - if ($this->argument === 0) { - $joins[] = 'LEFT JOIN subnet s ON (INET_ATON(machine.clientip) BETWEEN s.startaddr AND s.endaddr)'; - return 'machine.locationid IS NULL AND s.locationid IS NULL'; - } else { - $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)"; - } - } + public function __construct($operator, $argument) + { + parent::__construct('locationid', $operator, $argument); + } + + public function whereClause(&$args, &$joins) + { + settype($this->argument, 'int'); + if ($this->argument === 0) { + $joins[] = 'LEFT JOIN subnet s ON (INET_ATON(machine.clientip) BETWEEN s.startaddr AND s.endaddr)'; + return 'machine.locationid IS NULL AND s.locationid IS NULL'; + } else { + $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)"; + } + } } class SubnetFilter extends Filter { - public function __construct($operator, $argument) { - parent::__construct(null, $operator, $argument); - } - public function whereClause(&$args, &$joins) { - $argument = preg_replace('/[^0-9\.:]/', '', $this->argument); - return " clientip LIKE '$argument%'"; - } + public function __construct($operator, $argument) + { + parent::__construct(null, $operator, $argument); + } + + public function whereClause(&$args, &$joins) + { + $argument = preg_replace('/[^0-9\.:]/', '', $this->argument); + return " clientip LIKE '$argument%'"; + } } diff --git a/modules-available/statistics/inc/filterset.inc.php b/modules-available/statistics/inc/filterset.inc.php index ac928ac4..7cc075c3 100644 --- a/modules-available/statistics/inc/filterset.inc.php +++ b/modules-available/statistics/inc/filterset.inc.php @@ -2,27 +2,31 @@ class FilterSet { - private $filters; - private $sortDirection; - private $sortColumn; + private $filters; + private $sortDirection; + private $sortColumn; - public function __construct($filters) { - $this->filters = $filters; - } + public function __construct($filters) + { + $this->filters = $filters; + } - public function setSort($col, $direction) { - $this->sortDirection = $direction === 'DESC' ? 'DESC' : 'ASC'; + public function setSort($col, $direction) + { + $this->sortDirection = $direction === 'DESC' ? 'DESC' : 'ASC'; if (array_key_exists($col, Page_Statistics::$columns)) { - $isMapped = array_key_exists('map_sort', Page_Statistics::$columns[$col]); - $this->sortColumn = $isMapped ? Page_Statistics::$columns[$col]['map_sort'] : $col; - } else { - /* default sorting column is clientip */ - $this->sortColumn = 'clientip'; - } - - } - public function makeFragments(&$where, &$join, &$sort, &$args) { + $isMapped = array_key_exists('map_sort', Page_Statistics::$columns[$col]); + $this->sortColumn = $isMapped ? Page_Statistics::$columns[$col]['map_sort'] : $col; + } else { + /* default sorting column is clientip */ + $this->sortColumn = 'clientip'; + } + + } + + public function makeFragments(&$where, &$join, &$sort, &$args) + { /* generate where clause & arguments */ $where = ''; $joins = []; @@ -39,12 +43,16 @@ class FilterSet $join = implode('', array_unique($joins)); - $sort = " ORDER BY " . $this->sortColumn . " " . $this->sortDirection; - } - public function getSortDirection() { - return $this->sortDirection; - } - public function getSortColumn() { - return $this->sortColumn; - } + $sort = " ORDER BY " . $this->sortColumn . " " . $this->sortDirection; + } + + public function getSortDirection() + { + return $this->sortDirection; + } + + public function getSortColumn() + { + return $this->sortColumn; + } } diff --git a/modules-available/statistics/page.inc.php b/modules-available/statistics/page.inc.php index ca0ea96e..7ffe2562 100644 --- a/modules-available/statistics/page.inc.php +++ b/modules-available/statistics/page.inc.php @@ -148,7 +148,10 @@ class Page_Statistics extends Page } /* read filter */ - $this->query = Request::any('filters'); + $this->query = Request::any('filters', false); + if ($this->query === false) { + $this->query = 'lastseen > ' . gmdate('Y-m-d', strtotime('-30 day')); + } $sortColumn = Request::any('sortColumn'); $sortDirection = Request::any('sortDirection'); $filters = Filter::parseQuery($this->query); @@ -174,6 +177,9 @@ class Page_Statistics extends Page Render::closeTag('div'); } + /** + * @param \FilterSet $filterSet + */ private function showFilter($show, $filterSet) { $data = array( @@ -237,16 +243,18 @@ class Page_Statistics extends Page } } + /** + * @param \FilterSet $filterSet + */ private function showSummary($filterSet) { $filterSet->makeFragments($where, $join, $sort, $args); - $cutoff = time() - 86400 * 30; $online = time() - 610; - $known = Database::queryFirst("SELECT Count(*) AS val FROM machine $join WHERE lastseen > $cutoff AND $where", $args); - $on = Database::queryFirst("SELECT Count(*) AS val FROM machine $join WHERE lastseen > $online AND $where", $args); - $used = Database::queryFirst("SELECT Count(*) AS val FROM machine $join WHERE lastseen > $online AND logintime <> 0 AND $where", $args); - $hdd = Database::queryFirst("SELECT Count(*) AS val FROM machine $join WHERE badsectors > 10 AND lastseen > $cutoff AND $where", $args); + $known = Database::queryFirst("SELECT Count(*) AS val FROM machine $join WHERE ($where)", $args); + $on = Database::queryFirst("SELECT Count(*) AS val FROM machine $join WHERE lastseen > $online AND ($where)", $args); + $used = Database::queryFirst("SELECT Count(*) AS val FROM machine $join WHERE lastseen > $online AND logintime <> 0 AND ($where)", $args); + $hdd = Database::queryFirst("SELECT Count(*) AS val FROM machine $join WHERE badsectors >= 10 AND ($where)", $args); if ($on['val'] != 0) { $usedpercent = round($used['val'] / $on['val'] * 100); } else { @@ -287,6 +295,9 @@ class Page_Statistics extends Page Render::addTemplate('summary', $data); } + /** + * @param \FilterSet $filterSet + */ private function showSystemModels($filterSet) { global $STATS_COLORS; @@ -317,6 +328,9 @@ class Page_Statistics extends Page Render::addTemplate('cpumodels', array('rows' => $lines, 'query' => $this->query, 'json' => json_encode($json))); } + /** + * @param \FilterSet $filterSet + */ private function showMemory($filterSet) { global $STATS_COLORS, $SIZE_RAM; @@ -362,6 +376,9 @@ class Page_Statistics extends Page Render::addTemplate('memory', $data); } + /** + * @param \FilterSet $filterSet + */ private function showKvmState($filterSet) { $filterSet->makeFragments($where, $join, $sort, $args); @@ -381,6 +398,9 @@ class Page_Statistics extends Page Render::addTemplate('kvmstate', array('rows' => $lines, 'query' => $this->query,'json' => json_encode($json))); } + /** + * @param \FilterSet $filterSet + */ private function showId44($filterSet) { global $STATS_COLORS, $SIZE_ID44; @@ -432,6 +452,9 @@ class Page_Statistics extends Page Render::addTemplate('id44', $data); } + /** + * @param \FilterSet $filterSet + */ private function showLatestMachines($filterSet) { $filterSet->makeFragments($where, $join, $sort, $args); @@ -461,7 +484,9 @@ class Page_Statistics extends Page Render::addTemplate('newclients', array('rows' => $rows, 'openbutton' => $count > 5)); } - + /** + * @param \FilterSet $filterSet + */ private function showMachineList($filterSet) { $filterSet->makeFragments($where, $join, $sort, $args); @@ -504,7 +529,6 @@ class Page_Statistics extends Page 'sortDirection' => $filterSet->getSortDirection(), 'sortColumn' => $filterSet->getSortColumn(), 'columns' => json_encode(Page_Statistics::$columns), - 'locations' => json_encode($locsFlat), 'showList' => 1, 'show' => 'list' )); diff --git a/modules-available/statistics/templates/cpumodels.html b/modules-available/statistics/templates/cpumodels.html index f4af9cd2..0ab5286f 100644 --- a/modules-available/statistics/templates/cpumodels.html +++ b/modules-available/statistics/templates/cpumodels.html @@ -15,10 +15,12 @@ {{#rows}} - - {{systemmodel}} + + {{systemmodel}} + + + {{cores}} - {{cores}} {{count}} {{/rows}} diff --git a/modules-available/statistics/templates/filterbox.html b/modules-available/statistics/templates/filterbox.html index 2e7928e9..a8a36adb 100644 --- a/modules-available/statistics/templates/filterbox.html +++ b/modules-available/statistics/templates/filterbox.html @@ -99,7 +99,8 @@ document.addEventListener("DOMContentLoaded", function () { /* initialize selectize */ filterSelectize = $('#filterInput').selectize({ delimiter: slxFilterDel, - plugins: ['restore_on_backspace', 'remove_button'], + persist: false, + plugins: ['remove_button'], create: function(input) { return {value: input, text: input} }, @@ -156,6 +157,22 @@ document.addEventListener("DOMContentLoaded", function () { initButtons(); + $('.filter-col').each(function(idx, elem) { + var e = $(elem); + var col = e.data('filter-col'); + if (!col) return; + e.find('.filter-val').each(function(idx, elem) { + var e = $(elem); + var val = e.data('filter-val'); + if (!val) return; + e.click(function(ev) { + ev.preventDefault(); + addFilter(col, '=', val); + refresh(); + }); + }); + }); + }, false); diff --git a/modules-available/statistics/templates/id44.html b/modules-available/statistics/templates/id44.html index f0b5b9f7..84b515d1 100644 --- a/modules-available/statistics/templates/id44.html +++ b/modules-available/statistics/templates/id44.html @@ -6,14 +6,16 @@
- +
{{#rows}} - + {{/rows}} @@ -35,7 +37,6 @@ return; } sel = $('#tmpid' + String(tooltip.text)); - console.log('#tmpid' + String(tooltip.text)); sel.addClass('slx-bold'); } }); diff --git a/modules-available/statistics/templates/kvmstate.html b/modules-available/statistics/templates/kvmstate.html index 4c286d36..c44fb9ab 100644 --- a/modules-available/statistics/templates/kvmstate.html +++ b/modules-available/statistics/templates/kvmstate.html @@ -6,14 +6,16 @@
-
{{lang_partitionSize}} {{lang_machineCount}}
{{gb}} GiB + {{gb}} GiB + {{count}}
+
{{#rows}} - + {{/rows}} diff --git a/modules-available/statistics/templates/memory.html b/modules-available/statistics/templates/memory.html index 8a882fa6..2d50c47f 100644 --- a/modules-available/statistics/templates/memory.html +++ b/modules-available/statistics/templates/memory.html @@ -6,14 +6,16 @@
-
{{lang_kvmState}} {{lang_machineCount}}
{{kvmstate}} + {{kvmstate}} + {{count}}
+
{{#rows}} - + {{/rows}} -- cgit v1.2.3-55-g7522
{{lang_ramSize}} {{lang_machineCount}}
{{gb}} GiB + {{gb}} GiB + {{count}}