1<?php 2/* 3** Zabbix 4** Copyright (C) 2001-2021 Zabbix SIA 5** 6** This program is free software; you can redistribute it and/or modify 7** it under the terms of the GNU General Public License as published by 8** the Free Software Foundation; either version 2 of the License, or 9** (at your option) any later version. 10** 11** This program is distributed in the hope that it will be useful, 12** but WITHOUT ANY WARRANTY; without even the implied warranty of 13** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 14** GNU General Public License for more details. 15** 16** You should have received a copy of the GNU General Public License 17** along with this program; if not, write to the Free Software 18** Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. 19**/ 20 21 22/** 23 * Helper class containing methods for operations with tags. 24 */ 25class CApiTagHelper { 26 27 /** 28 * Returns SQL condition for tag filters. 29 * 30 * @param array $tags 31 * @param string $tags[]['tag'] 32 * @param int $tags[]['operator'] 33 * @param string $tags[]['value'] 34 * @param int $evaltype 35 * @param string $parent_alias 36 * @param string $table 37 * @param string $field 38 * 39 * @return string 40 */ 41 public static function addWhereCondition(array $tags, $evaltype, $parent_alias, $table, $field) { 42 $values_by_tag = []; 43 44 foreach ($tags as $tag) { 45 $operator = array_key_exists('operator', $tag) ? $tag['operator'] : TAG_OPERATOR_LIKE; 46 $value = array_key_exists('value', $tag) ? $tag['value'] : ''; 47 48 if ($operator == TAG_OPERATOR_NOT_LIKE && $value === '') { 49 $operator = TAG_OPERATOR_NOT_EXISTS; 50 } 51 elseif ($operator == TAG_OPERATOR_LIKE && $value === '') { 52 $operator = TAG_OPERATOR_EXISTS; 53 } 54 55 if (!array_key_exists($tag['tag'], $values_by_tag)) { 56 $values_by_tag[$tag['tag']] = [ 57 'NOT EXISTS' => [], 58 'EXISTS' => [] 59 ]; 60 } 61 62 $slot = in_array($operator, [TAG_OPERATOR_EXISTS, TAG_OPERATOR_LIKE, TAG_OPERATOR_EQUAL]) 63 ? 'EXISTS' 64 : 'NOT EXISTS'; 65 66 if (!is_array($values_by_tag[$tag['tag']][$slot])) { 67 /* 68 * If previously there was the same tag name with operators TAG_OPERATOR_EXISTS/TAG_OPERATOR_NOT_EXISTS, 69 * we don't collect more values anymore because TAG_OPERATOR_EXISTS/TAG_OPERATOR_NOT_EXISTS has higher 70 * priority. 71 * 72 * `continue` is necessary to accidentally not overwrite boolean with array. Tag values collected before 73 * will be later removed. 74 */ 75 continue; 76 } 77 78 switch ($operator) { 79 case TAG_OPERATOR_LIKE: 80 case TAG_OPERATOR_NOT_LIKE: 81 $value = str_replace(['!', '%', '_'], ['!!', '!%', '!_'], $value); 82 $value = '%'.mb_strtoupper($value).'%'; 83 84 $values_by_tag[$tag['tag']][$slot][] 85 = 'UPPER('.$table.'.value) LIKE '.zbx_dbstr($value)." ESCAPE '!'"; 86 break; 87 88 case TAG_OPERATOR_EXISTS: 89 case TAG_OPERATOR_NOT_EXISTS: 90 $values_by_tag[$tag['tag']][$slot] = false; 91 break; 92 93 case TAG_OPERATOR_EQUAL: 94 case TAG_OPERATOR_NOT_EQUAL: 95 $values_by_tag[$tag['tag']][$slot][] = $table.'.value='.zbx_dbstr($value); 96 break; 97 } 98 } 99 100 $sql_where = []; 101 foreach ($values_by_tag as $tag => $filters) { 102 // Tag operators TAG_OPERATOR_EXISTS/TAG_OPERATOR_NOT_EXISTS are both canceling explicit values of same tag. 103 if ($filters['EXISTS'] === false) { 104 unset($filters['NOT EXISTS']); 105 } 106 elseif ($filters['NOT EXISTS'] === false) { 107 unset($filters['EXISTS']); 108 } 109 110 $_where = []; 111 foreach ($filters as $prefix => $values) { 112 if ($values === []) { 113 continue; 114 } 115 116 $statement = $table.'.tag='.zbx_dbstr($tag); 117 if ($values) { 118 $statement .= (count($values) == 1) 119 ? ' AND '.implode(' OR ', $values) 120 : ' AND ('.implode(' OR ', $values).')'; 121 } 122 123 $_where[] = $prefix.' ('. 124 'SELECT NULL'. 125 ' FROM '.$table. 126 ' WHERE '.$parent_alias.'.'.$field.'='.$table.'.'.$field.' AND '.$statement. 127 ')'; 128 } 129 130 if (count($_where) == 1) { 131 $sql_where[] = $_where[0]; 132 } 133 else { 134 $sql_where[] = '('.$_where[0].' OR '.$_where[1].')'; 135 } 136 } 137 138 if (!$sql_where) { 139 return '(1=0)'; 140 } 141 142 $sql_where_cnt = count($sql_where); 143 144 $evaltype_glue = ($evaltype == TAG_EVAL_TYPE_OR) ? ' OR ' : ' AND '; 145 $sql_where = implode($evaltype_glue, $sql_where); 146 147 return ($sql_where_cnt > 1 && $evaltype == TAG_EVAL_TYPE_OR) ? '('.$sql_where.')' : $sql_where; 148 } 149 150 /** 151 * Return SQL query conditions to filter host tags including inherited template tags. 152 * 153 * @static 154 * 155 * @param array $tags 156 * @param string $tags[]['tag'] 157 * @param int $tags[]['operator'] 158 * @param string $tags[]['value'] 159 * @param int $evaltype 160 * 161 * @return string 162 */ 163 public static function addInheritedHostTagsWhereCondition(array $tags, int $evaltype): string { 164 // Swap tag operators to select templates normally should be excluded. 165 $swapped_filter = array_map(function ($tag) { 166 $swapping_map = [ 167 TAG_OPERATOR_LIKE => TAG_OPERATOR_LIKE, 168 TAG_OPERATOR_EQUAL => TAG_OPERATOR_EQUAL, 169 TAG_OPERATOR_NOT_LIKE => TAG_OPERATOR_LIKE, 170 TAG_OPERATOR_NOT_EQUAL => TAG_OPERATOR_EQUAL, 171 TAG_OPERATOR_EXISTS => TAG_OPERATOR_EXISTS, 172 TAG_OPERATOR_NOT_EXISTS => TAG_OPERATOR_EXISTS 173 ]; 174 return ['operator' => $swapping_map[$tag['operator']]] + $tag; 175 }, $tags); 176 177 $db_template_tags = DBfetchArray(DBselect( 178 'SELECT h.hostid,ht.tag,ht.value'. 179 ' FROM hosts h, host_tag ht'. 180 ' WHERE ht.hostid=h.hostid'. 181 ' AND h.status='.HOST_STATUS_TEMPLATE. 182 ' AND '.self::addWhereCondition($swapped_filter, TAG_EVAL_TYPE_OR, 'h','host_tag', 'hostid') 183 )); 184 185 // Group filter tags by operator and tag name. 186 $negated_tags = []; 187 $inclusive_tags = []; 188 189 foreach ($tags as $tag) { 190 if (!array_key_exists('operator', $tag)) { 191 $tag['operator'] = TAG_OPERATOR_LIKE; 192 } 193 if (!array_key_exists('value', $tag)) { 194 $tag['value'] = ''; 195 } 196 if ($tag['operator'] == TAG_OPERATOR_NOT_LIKE && $tag['value'] === '') { 197 $tag['operator'] = TAG_OPERATOR_NOT_EXISTS; 198 } 199 elseif ($tag['operator'] == TAG_OPERATOR_LIKE && $tag['value'] === '') { 200 $tag['operator'] = TAG_OPERATOR_EXISTS; 201 } 202 203 if (in_array($tag['operator'], [TAG_OPERATOR_LIKE, TAG_OPERATOR_EQUAL, TAG_OPERATOR_EXISTS]) 204 && !array_key_exists($tag['tag'], $inclusive_tags)) { 205 $inclusive_tags[$tag['tag']] = []; 206 } 207 elseif (in_array($tag['operator'], [TAG_OPERATOR_NOT_LIKE, TAG_OPERATOR_NOT_EQUAL, TAG_OPERATOR_NOT_EXISTS]) 208 && !array_key_exists($tag['tag'], $negated_tags)) { 209 $negated_tags[$tag['tag']] = []; 210 } 211 212 switch ($tag['operator']) { 213 case TAG_OPERATOR_LIKE: 214 case TAG_OPERATOR_EQUAL: 215 if (is_array($inclusive_tags[$tag['tag']])) { 216 $inclusive_tags[$tag['tag']][] = $tag; 217 } 218 break; 219 220 case TAG_OPERATOR_NOT_LIKE: 221 case TAG_OPERATOR_NOT_EQUAL: 222 if (is_array($negated_tags[$tag['tag']])) { 223 $negated_tags[$tag['tag']][] = $tag; 224 } 225 break; 226 227 case TAG_OPERATOR_EXISTS: 228 $inclusive_tags[$tag['tag']] = false; 229 break; 230 231 case TAG_OPERATOR_NOT_EXISTS: 232 $negated_tags[$tag['tag']] = false; 233 break; 234 } 235 } 236 237 // Make 'where' condition from negated filter tags. 238 $negated_conditions = array_fill_keys(array_keys($negated_tags), ['values' => [], 'templateids' => []]); 239 array_walk($negated_conditions, function (&$where, $tag_name) use ($negated_tags, $db_template_tags) { 240 if ($negated_tags[$tag_name] === false) { 241 $tag = ['tag' => $tag_name, 'operator' => TAG_OPERATOR_NOT_EXISTS]; 242 $where['templateids'] += self::getMatchingTemplateids($tag, $db_template_tags); 243 } 244 else { 245 foreach ($negated_tags[$tag_name] as $tag) { 246 $where['templateids'] += self::getMatchingTemplateids($tag, $db_template_tags); 247 248 if ($tag['operator'] == TAG_OPERATOR_NOT_EXISTS) { 249 $where['values'] = false; 250 } 251 elseif (is_array($where['values'])) { 252 $where['values'][] = self::makeTagWhereCondition($tag); 253 } 254 } 255 } 256 }); 257 258 $negated_where_conditions = []; 259 foreach ($negated_conditions as $tag => $tag_where) { 260 261 $templateids_in = []; 262 while ($tag_where['templateids']) { 263 $templateids_in += $tag_where['templateids']; 264 265 $tag_where['templateids'] = API::Template()->get([ 266 'output' => [], 267 'parentTemplateids' => array_keys($tag_where['templateids']), 268 'preservekeys' => true, 269 'nopermissions' => true 270 ]); 271 } 272 273 $negated_where_conditions[] = '(NOT EXISTS ('. 274 'SELECT NULL'. 275 ' FROM host_tag'. 276 ' WHERE (h.hostid=host_tag.hostid'. 277 ' AND host_tag.tag='.zbx_dbstr($tag). 278 ($tag_where['values'] ? ' AND ('.implode(' OR ', $tag_where['values']).')' : ''). 279 ')'. 280 ($templateids_in 281 ? ' OR '.dbConditionInt('ht2.templateid', array_keys($templateids_in)).'' 282 : '' 283 ). 284 ')'. 285 ')'; 286 } 287 288 $where_conditions = []; 289 290 if ($negated_where_conditions) { 291 if ($evaltype == TAG_EVAL_TYPE_AND_OR) { 292 $where_conditions[] = implode(' AND ', $negated_where_conditions); 293 } 294 else { 295 $where_conditions = array_map(function ($condition) { 296 return $condition; 297 }, $negated_where_conditions); 298 } 299 } 300 301 // Make 'where' conditions for inclusive filter tags. 302 foreach ($inclusive_tags as $tag_name => $tag_values) { 303 $templateids = []; 304 $values = []; 305 306 if ($tag_values === false) { 307 $templateids += self::getMatchingTemplateids(['tag' => $tag_name, 'operator' => TAG_OPERATOR_EXISTS], 308 $db_template_tags 309 ); 310 } 311 else { 312 foreach ($tag_values as $tag) { 313 $templateids += self::getMatchingTemplateids($tag, $db_template_tags); 314 315 if ($tag['operator'] == TAG_OPERATOR_EXISTS) { 316 $values = false; 317 } 318 elseif (is_array($values)) { 319 $values[] = self::makeTagWhereCondition($tag); 320 } 321 } 322 } 323 324 $templateids_in = []; 325 while ($templateids) { 326 $templateids_in += $templateids; 327 328 $templateids = API::Template()->get([ 329 'output' => [], 330 'parentTemplateids' => array_keys($templateids), 331 'preservekeys' => true, 332 'nopermissions' => true 333 ]); 334 } 335 336 $where_conditions[] = '(EXISTS ('. 337 'SELECT NULL'. 338 ' FROM host_tag'. 339 ' WHERE h.hostid=host_tag.hostid'. 340 ' AND host_tag.tag='.zbx_dbstr($tag_name). 341 ($values ? ' AND ('.implode(' OR ', $values).')' : ''). 342 ')'. 343 ($templateids_in ? ' OR '.dbConditionInt('ht2.templateid', array_keys($templateids_in)) : ''). 344 ')'; 345 } 346 347 $operator = ($evaltype == TAG_EVAL_TYPE_OR) ? ' OR ' : ' AND '; 348 return '('.implode($operator, $where_conditions).')'; 349 } 350 351 /** 352 * Function returns SQL WHERE statement for given tag value based on operator. 353 * 354 * @param array $tag 355 * @param string $tag['value'] 356 * @param int $tag['operator'] 357 * 358 * @return string 359 */ 360 private static function makeTagWhereCondition(array $tag): string { 361 if ($tag['operator'] == TAG_OPERATOR_EQUAL || $tag['operator'] == TAG_OPERATOR_NOT_EQUAL) { 362 return 'host_tag.value='.zbx_dbstr($tag['value']); 363 } 364 else { 365 $value = str_replace(['!', '%', '_'], ['!!', '!%', '!_'], $tag['value']); 366 $value = '%'.mb_strtoupper($value).'%'; 367 return 'UPPER(host_tag.value) LIKE '.zbx_dbstr($value)." ESCAPE '!'"; 368 } 369 } 370 371 /** 372 * Function to collect templateids having tags matching the filter tag. 373 * 374 * @param array $filter_tag 375 * @param string $filter_tag['tag'] 376 * @param int $filter_tag['operator'] 377 * @param string $filter_tag['value'] 378 * @param array $template_tags 379 * @param string $template_tags[]['tag'] 380 * @param string $template_tags[]['value'] 381 * @param string $template_tags[]['hostid'] 382 * 383 * @return array 384 */ 385 private static function getMatchingTemplateids(array $filter_tag, array $template_tags): array { 386 $templateids = []; 387 388 switch ($filter_tag['operator']) { 389 case TAG_OPERATOR_LIKE: 390 case TAG_OPERATOR_NOT_LIKE: 391 foreach ($template_tags as $tag) { 392 if ($filter_tag['tag'] === $tag['tag'] 393 && mb_stripos($tag['value'], $filter_tag['value']) !== false) { 394 $templateids[$tag['hostid']] = true; 395 } 396 } 397 break; 398 399 case TAG_OPERATOR_EQUAL: 400 case TAG_OPERATOR_NOT_EQUAL: 401 foreach ($template_tags as $tag) { 402 if ($filter_tag['tag'] === $tag['tag'] && $filter_tag['value'] === $tag['value']) { 403 $templateids[$tag['hostid']] = true; 404 } 405 } 406 break; 407 408 case TAG_OPERATOR_NOT_EXISTS: 409 case TAG_OPERATOR_EXISTS: 410 foreach ($template_tags as $tag) { 411 if ($filter_tag['tag'] === $tag['tag']) { 412 $templateids[$tag['hostid']] = true; 413 } 414 } 415 break; 416 } 417 418 return $templateids; 419 } 420} 421