<?php
/* base class with rudimentary SQL generation abilities.
* WARNING: argument is escaped, but $column and $operator are passed unfiltered into SQL */
abstract class StatisticsFilter
{
/**
* Legacy delimiter for js_selectize filters - used to redirect old URLs
*/
const LEGACY_DELIMITER = '~,~';
const SIZE_ID44 = array(0, 8, 16, 24, 30, 40, 50, 60, 80, 100, 120, 150, 180, 250, 300, 400, 500, 1000, 2000, 4000);
const SIZE_RAM = array(1, 2, 3, 4, 6, 8, 10, 12, 16, 24, 32, 48, 64, 96, 128, 192, 256, 320, 480, 512, 768, 1024);
private static $keyCounter = 0;
/*
* Simple filters that map directly to DB columns
*/
const OP_ORDINAL = ['=', '!=', '<', '>', '<=', '>='];
const OP_STRCMP = ['~', '!~', '=', '!='];
const OP_NOMINAL = ['=', '!='];
const OP_LOCATIONS = ['~', '=', '!='];
const OP_FUZZY_ORDINAL = ['=', '!=', '~', '!~', '<', '>', '<=', '>='];
/**
* @var StatisticsFilter[]
*/
public static $columns;
/*
* Class instance stuff
*/
/**
* @var string|null db-based sort column for this field, null if not sortable
*/
public $column;
/**
* @var string[] valid operators for this filter
*/
public $ops;
/**
* @var string placeholder for input field
*/
public $placeholder;
public function __construct($column, array $ops, string $placeholder = '')
{
$this->column = $column;
$this->ops = $ops;
$this->placeholder = $placeholder;
}
public function type()
{
return ($this->ops === self::OP_ORDINAL || $this->ops === self::OP_FUZZY_ORDINAL) ? 'int' : 'string';
}
/* returns a where clause and adds needed operators to the passed arrays */
public abstract function whereClause(string $operator, $argument, array &$args, array &$joins);
public function bind(string $op, $argument) { return new DatabaseFilter($this, $op, $argument); }
public final function validateOperator(string $operator)
{
if (empty($this->ops))
return;
if (!in_array($operator, $this->ops)) {
Util::traceError("Invalid op '$operator' for " . get_class($this) . '::' . $this->column);
}
}
/*
* Static/Helpers
*/
public static function findBestValue($array, $value, $up)
{
$best = 0;
for ($i = 0; $i < count($array); ++$i) {
if (abs($array[$i] - $value) < abs($array[$best] - $value)) {
$best = $i;
}
}
if (!$up && $best === 0) {
return $array[0];
}
if ($up && $best + 1 === count($array)) {
return $array[$best];
}
if ($up) {
return ($array[$best] + $array[$best + 1]) / 2;
}
return ($array[$best] + $array[$best - 1]) / 2;
}
public static function getNewKey($colname)
{
return $colname . '_' . (self::$keyCounter++);
}
/**
* @return DatabaseFilter[]
*/
public static function parseQuery()
{
// Get current settings from GET
$ops = Request::get('op', [], 'array');
$currentValues = ArrayUtil::mergeByKey([
'filter' => Request::get('filter', [], 'array'),
'op' => $ops,
'argument' => Request::get('arg', [], 'array'),
]);
if (Request::get('show') === false && empty($ops)) {
$currentValues['lastseen'] = [
'filter' => true,
'op' => '>',
'argument' => gmdate('Y-m-d', strtotime('-30 day')),
];
}
$filters = [];
foreach ($currentValues as $filterType => $data) {
if (!$data['filter'])
continue;
$operator = $data['op'];
$argument = $data['argument'];
if (array_key_exists($filterType, self::$columns)) {
$filters[$filterType] = self::$columns[$filterType]->bind($operator, $argument);
} else {
Message::addError('invalid-filter-key', $filterType);
}
}
return $filters;
}
/**
* @param \StatisticsFilterSet $filterSet
*/
public static function renderFilterBox($show, $filterSet)
{
// Build location list, with permissions
if (Module::isAvailable('locations')) {
self::$columns['location']->filterLocations($filterSet->getAllowedLocations());
}
// Build column array for rendering
$columns = [];
$showCount = 0;
foreach (self::$columns as $key => $filter) {
$col = [
'key' => $key,
'name' => Dictionary::translateFile('filters', $key, true),
'placeholder' => $filter->placeholder,
];
$bind = $filterSet->hasFilterKey($key);
if ($filter->type() === 'int') {
$col['input'] = 'number';
} elseif ($filter->type() === 'string') {
$col['input'] = 'text';
} elseif ($filter->type() === 'date') {
$col['input'] = 'text';
$col['inputclass'] = 'is-date';
} elseif ($filter->type() === 'enum') {
$col['enum'] = true;
$col['values'] = $filter->values;
if ($bind !== false) {
// Current value from GET
foreach ($col['values'] as &$value) {
if ($value['key'] == $bind->argument) {
$value['selected'] = 'selected';
}
}
}
}
// current value from GET
if ($bind !== false) {
$col['currentvalue'] = $bind->argument;
$col['checked'] = 'checked';
$showCount++;
} elseif (!isset($col['show']) || !$col['show']) {
$col['collapse'] = 'collapse';
}
$col['op'] = $filter->ops;
foreach ($col['op'] as &$value) {
$value = ['op' => $value];
if ($bind !== false && $bind->op === $value['op']) {
$value['selected'] = 'selected';
}
}
$columns[$key] = $col;
}
if ($showCount < 2) {
unset($columns['clientip']['collapse']);
}
if ($showCount < 1) {
unset($columns['machineuuid']['collapse']);
}
$data = array(
'show' => $show,
'columns' => array_values($columns),
$show . 'ButtonClass' => 'active',
);
Permission::addGlobalTags($data['perms'], null, ['view.summary', 'view.list']);
Render::addTemplate('filterbox', $data);
}
public static function initConstants()
{
self::$columns = [
'clientip' => new IpStatisticsFilter(),
'hostname' => new SimpleStatisticsFilter('hostname', self::OP_STRCMP, 'pc.fqdn.example.com'),
'machineuuid' => new SimpleStatisticsFilter('machineuuid', self::OP_STRCMP, '88888888-4444-4444-121212121212'),
'macaddr' => new SimpleStatisticsFilter('macaddr', self::OP_STRCMP, '11-22-33-44-55-66'),
'firstseen' => new DateStatisticsFilter('firstseen', '2020-10-15 14:00'),
'lastseen' => new DateStatisticsFilter('lastseen', '2020-10-15 14:00'),
'logintime' => new DateStatisticsFilter('logintime', '2020-10-15 14:00'),
'lastboot' => new DateStatisticsFilter('lastboot', '2020-10-15 14:00'),
'runtime' => new RuntimeStatisticsFilter(),
'realcores' => new SimpleStatisticsFilter('realcores', self::OP_ORDINAL, ''),
'systemmodel' => new SimpleStatisticsFilter('systemmodel', self::OP_STRCMP, 'PC-365 (IBM)'),
'cpumodel' => new SimpleStatisticsFilter('cpumodel', self::OP_STRCMP, 'Pentium Pro 200 MHz'),
'hddgb' => new Id44GbStatisticsFilter(),
'gbram' => new RamGbStatisticsFilter(),
'kvmstate' => new EnumStatisticsFilter('kvmstate', ['ENABLED', 'DISABLED', 'UNSUPPORTED']),
'badsectors' => new SimpleStatisticsFilter('badsectors', self::OP_ORDINAL, ''),
'currentuser' => new SimpleStatisticsFilter('currentuser', self::OP_STRCMP, 'login'),
'state' => new StateStatisticsFilter(),
'live_swapfree' => new SimpleStatisticsFilter('live_swapfree', self::OP_ORDINAL, 'MiB'),
'live_memfree' => new SimpleStatisticsFilter('live_memfree', self::OP_ORDINAL, 'MiB'),
'live_tmpfree' => new SimpleStatisticsFilter('live_tmpfree', self::OP_ORDINAL, 'MiB'),
];
if (Module::isAvailable('locations')) {
self::$columns['location'] = new LocationStatisticsFilter();
}
}
}
class SimpleStatisticsFilter extends StatisticsFilter
{
public function whereClause(string $operator, $argument, array &$args, array &$joins)
{
$addendum = '';
$key = self::getNewKey($this->column);
$args[$key] = $argument;
if (is_array($argument)) {
if ($operator{0} === '!') {
$op = 'NOT IN';
} else {
$op = 'IN';
}
} else {
if ($operator === '~' || $operator === '!~') {
$args[$key] = str_replace(array('=', '_', '%', '*', '?'), array('==', '=_', '=%', '%', '_'), $args[$key]);
$addendum = " ESCAPE '='";
}
$op = $operator;
if ($operator === '~') {
$op = 'LIKE';
} elseif ($operator === '!~') {
$op = 'NOT LIKE';
}
}
return 'm.' . $this->column . ' ' . $op . ' (:' . $key . ') ' . $addendum;
}
}
class EnumStatisticsFilter extends SimpleStatisticsFilter
{
public $values;
public function __construct(string $column, array $values, array $ops = self::OP_NOMINAL)
{
parent::__construct($column, $ops, '');
if (isset($values[0])) {
if (!is_array($values[0])) {
$values = array_map(function($e) { return [
'key' => $e,
'value' => $e,
]; }, $values);
}
} else {
$values = array_map(function($v, $k) { return [
'key' => $k,
'value' => $v,
]; }, $values, array_keys($values));
}
$this->values = $values;
}
public function type() { return 'enum'; }
public function whereClause(string $operator, $argument, array &$args, array &$joins)
{
$keys = ArrayUtil::flattenByKey($this->values, 'key');
if (is_array($argument)) {
$ok = true;
foreach ($argument as $e) {
if (!in_array($e, $keys)) {
$ok = false;
}
}
} else {
$ok = in_array($argument, $keys);
}
if (!$ok) {
Message::addError('invalid-enum-item', $this->column, $argument);
return '0';
}
return parent::whereClause($operator, $argument, $args, $joins);
}
}
class DateStatisticsFilter extends StatisticsFilter
{
public function __construct(string $column, string $placeholder)
{
parent::__construct($column, self::OP_ORDINAL, $placeholder);
}
public function type() { return 'date'; }
public function whereClause(string $operator, $argument, array &$args, array &$joins)
{
$key = self::getNewKey($this->column);
$addendum = '';
if (!preg_match('/^(?<date>\d{4}-\d{2}-\d{2})(\s+(?<h>\d{1,2})(:(?<m>\d{2})(:\d+)?)?)?$/', $argument, $out)) {
Message::addError('invalid-date-format', $argument);
return '0';
}
if (isset($out['m'])) {
$span = 'minute';
} elseif (isset($out['h'])) {
$span = 'hour';
$argument .= ':00';
} else {
$span = 'day';
}
$args[$key] = strtotime($argument);
if ($operator === '=' || $operator === '!=') {
$key2 = self::getNewKey($this->column);
$args[$key2] = strtotime(' +1 ' . $span, $args[$key]);
return ($operator === '=' ? '' : 'NOT ') . 'm.' . $this->column . " BETWEEN :$key AND :$key2";
}
if ($operator === '>' || $operator === '<=') {
$args[$key] = strtotime('+1 ' . $span . ' -1 second', $args[$key]);
}
return 'm.' . $this->column . ' ' . $operator . ' :' . $key . $addendum;
}
}
class RuntimeStatisticsFilter extends StatisticsFilter
{
public function __construct()
{
parent::__construct('lastboot', self::OP_ORDINAL);
}
public function whereClause(string $operator, $argument, array &$args, array &$joins)
{
$upper = time() - (int)$argument * 3600;
$lower = $upper - 3600;
$common = "state IN ('OCCUPIED', 'IDLE', 'STANDBY') AND";
if ($operator == '<') { // These are inverted (uptime vs lastboot)
return "$common lastboot > $upper";
} elseif ($operator == '<=') {
return "$common lastboot > $lower";
} elseif ($operator == '>') {
return "$common lastboot < $lower";
} elseif ($operator == '>=') {
return "$common lastboot < $upper";
} elseif ($operator == '=') {
return "$common (lastboot BETWEEN $lower AND $upper)";
}
// !=
return "$common (lastboot NOT BETWEEN $lower AND > $upper)";
}
}
abstract class GbToMbRangeStatisticsFilter extends StatisticsFilter
{
protected function rangeClause(string $operator, $argument, array $fuzzyVals)
{
if ($operator === '~' || $operator === '!~') {
$lower = (int)floor(StatisticsFilter::findBestValue($fuzzyVals, (int)$argument, false) * 1024 - 500);
$upper = (int)ceil(StatisticsFilter::findBestValue($fuzzyVals, (int)$argument, true) * 1024 + 100);
$operator = str_replace('~', '=', $operator);
} else {
$lower = round($argument * 1024 - 500);
$upper = round($argument * 1024 + 1023);
}
if ($operator === '=')
return " {$this->column} BETWEEN $lower AND $upper";
if ($operator === '!=')
return " {$this->column} NOT BETWEEN $lower AND $upper";
if ($operator === '<')
return " {$this->column} < $lower";
if ($operator === '<=')
return " {$this->column} <= $upper";
if ($operator === '>')
return " {$this->column} > $upper";
return " {$this->column} >= $lower"; // >=
}
}
class RamGbStatisticsFilter extends GbToMbRangeStatisticsFilter
{
public function __construct()
{
parent::__construct('mbram', self::OP_FUZZY_ORDINAL, 'GiB');
}
public function whereClause(string $operator, $argument, array &$args, array &$joins)
{
return parent::rangeClause($operator, $argument, self::SIZE_RAM);
}
}
class Id44GbStatisticsFilter extends GbToMbRangeStatisticsFilter
{
public function __construct()
{
parent::__construct('id44mb', self::OP_FUZZY_ORDINAL,'GiB');
}
public function whereClause(string $operator, $argument, array &$args, array &$joins)
{
return parent::rangeClause($operator, $argument, self::SIZE_ID44);
}
}
class StateStatisticsFilter extends EnumStatisticsFilter
{
public function __construct()
{
parent::__construct('state', ['on', 'off', 'idle', 'occupied', 'standby']);
}
public function whereClause(string $operator, $argument, array &$args, array &$joins)
{
$map = [ 'on' => ['IDLE', 'OCCUPIED'], 'off' => ['OFFLINE'], 'idle' => ['IDLE'], 'occupied' => ['OCCUPIED'], 'standby' => ['STANDBY'] ];
$neg = $operator == '!=' ? 'NOT ' : '';
if (array_key_exists($argument, $map)) {
$key = StatisticsFilter::getNewKey($this->column);
$args[$key] = $map[$argument];
return " m.state $neg IN ( :$key ) ";
} else {
Message::addError('invalid-filter-argument', 'state', $argument);
return ' 1';
}
}
}
class LocationStatisticsFilter extends EnumStatisticsFilter
{
public function __construct()
{
$locs = [];
foreach (Location::getLocations(-1, 0, true) as $loc) {
$locs[] = [
'key' => $loc['locationid'],
'value' => $loc['locationpad'] . ' ' . $loc['locationname'],
];
}
parent::__construct('locationid', $locs, self::OP_LOCATIONS);
}
public function type() { return 'enum'; }
public function whereClause(string $operator, $argument, array &$args, array &$joins)
{
$recursive = (substr($operator, -1) === '~');
$operator = str_replace('~', '=', $operator);
if ($recursive && is_array($argument)) {
Util::traceError('Cannot use ~ operator for location with array');
}
if ($recursive) {
$argument = array_keys(Location::getRecursiveFlat($argument));
} elseif ($argument == 0) {
return 'locationid IS ' . ($operator === '!=' ? 'NOT' : '') . ' NULL';
}
return parent::whereClause($operator, $argument, $args, $joins);
}
public function filterLocations($list)
{
if ($list === false || in_array(0, $list))
return;
foreach ($this->values as &$loc) {
if (!in_array($loc['key'], $list)) {
$loc['disabled'] = 'disabled';
}
}
}
}
class IpStatisticsFilter extends StatisticsFilter
{
public function __construct()
{
parent::__construct('clientip', self::OP_NOMINAL, '1.2.3.4, 1.2.3.*, 1.2.3/24');
}
public function whereClause(string $operator, $argument, array &$args, array &$joins) : string
{
$argument = strtolower(preg_replace('#[^0-9a-f.:/*]#i', '', $argument));
if (filter_var($argument, FILTER_VALIDATE_IP) !== false) {
// Valid \o/ - do nothing to $argument
} elseif (strpos($argument, '/') !== false) {
// TODO: IPv6 CIDR
$range = IpUtil::parseCidr($argument);
if ($range === false) {
Message::addError('invalid-cidr-notion', $argument);
return '0';
}
return 'INET_ATON(clientip) BETWEEN ' . $range['start'] . ' AND ' . $range['end'];
} elseif (($num = substr_count($argument, ':')) !== 0 && $num <= 7) {
// IPv6, not yet in DB but let's prepare
if ($num > 7 || strpos($argument, '::') !== false) { // Too many :, or invalid compressed format
Message::addError('invalid-ip-address', $argument);
return '0';
} elseif ($num <= 7 && substr($argument, -1) === ':') {
$argument .= '*';
} elseif ($num < 7) {
$argument .= ':*';
}
} elseif (($num = substr_count($argument, '.')) !== 0 && $num <= 3) {
if (substr($argument, -1) === '.') {
$argument .= '*';
} elseif ($num < 3) {
$argument .= '.*';
}
} else {
Message::addError('invalid-ip-address', $argument);
return '0';
}
return "clientip LIKE '" . str_replace('*', '%', $argument) . "'";
}
}
class IsClientStatisticsFilter extends StatisticsFilter
{
public function __construct()
{
parent::__construct(null, []);
}
public function whereClause(string $operator, $argument, array &$args, array &$joins)
{
if ($argument) {
$joins[] = ' LEFT JOIN runmode USING (machineuuid)';
return "(runmode.isclient <> 0 OR runmode.isclient IS NULL)";
}
$joins[] = ' INNER JOIN runmode USING (machineuuid)';
return "runmode.isclient = 0";
}
}
class DatabaseFilter
{
/** @var StatisticsFilter
*/
private $inst;
public $op;
public $argument;
public function __construct(StatisticsFilter $inst, string $op, $argument)
{
$inst->validateOperator($op);
$this->inst = $inst;
$this->op = $op;
$this->argument = $argument;
}
public function whereClause(array &$args, array &$joins)
{
return $this->inst->whereClause($this->op, $this->argument, $args, $joins);
}
public function isClass($what)
{
return get_class($this->inst) === $what;
}
}
StatisticsFilter::initConstants();