modules/reports/models/report_query_builder.php

   1 <?php
   2
   3 /**
   4  * Class for building report sql queries
   5  *
   6  * Should eventually be shared between summary and status reports. Isn't currently.
   7  */
   8 class Report_query_builder_Model extends Model
   9 {
  10         /* oh no, it leaked out from the summary report model
  11          * (contains a mapping from each group to each object, so alert summary
  12          * can sum up group reports)
  13          */
  14         public $host_hostgroup; /**< array(host => array(hgrop1, hgroupx...)) */
  15         public $service_servicegroup; /**< array(service => array(sgroup1, sgroupx...))*/
  16
  17         protected $db_table = false; /**< The table we'll be operating on */
  18         protected $options = false; /**< An options object (or array) to work with */
  19
  20         /**
  21          * Create new report query builder
  22          * @param $db_table The table name
  23          * @param $options The options object to work with
  24          */
  25         function __construct($db_table, $options) {
  26                 parent::__construct();
  27                 $this->db_table = $db_table;
  28                 $this->options = $options;
  29         }
  30
  31         /**
  32          * Create the base of the query to use when calculating
  33          * alert summary. Each caller is responsible for adding
  34          * sorting and limit options as necessary.
  35          *
  36          * @param $fields string Comma separated list of database columns the caller needs
  37          * @return string (sql)
  38          */
  39         function build_alert_summary_query($fields = null)
  40         {
  41                 if(!$fields) {
  42                         // default to the most commonly used fields
  43                         $fields = 'host_name, service_description, state, hard';
  44                 }
  45                 $auth = op5auth::instance();
  46                 $softorhard = false;
  47                 $alert_types = false;
  48                 $downtime = false;
  49                 $process = false;
  50                 $time_first = false;
  51                 $time_last = false;
  52                 $wildcard_filter = false;
  53
  54                 $hosts = false;
  55                 $services = false;
  56                 if ($this->options['report_type'] == 'servicegroups') {
  57                         $hosts = $services = array();
  58                         foreach ($this->options['objects'] as $sg) {
  59                                 $res = Livestatus::instance()->getServices(array('columns' => array('host_name', 'description'), 'filter' => array('groups' => array('>=' => $sg))));
  60                                 foreach ($res as $o) {
  61                                         $name = implode(';', $o);
  62                                         # To be able to sum up alert totals:
  63                                         if (empty($services[$name])) {
  64                                                 $services[$name] = array();
  65                                         }
  66                                         $services[$name][$sg] = $sg;
  67                                         if (empty($hosts[$o['host_name']])) {
  68                                                 $hosts[$o['host_name']] = array();
  69                                         }
  70                                         $hosts[$o['host_name']][$sg] = $sg;
  71                                 }
  72                         }
  73                         $this->service_servicegroup['host'] = $hosts;
  74                         $this->service_servicegroup['service'] = $services;
  75                 } elseif ($this->options['report_type'] == 'hostgroups') {
  76                         $hosts = array();
  77                         foreach ($this->options['objects'] as $hg) {
  78                                 $res = Livestatus::instance()->getHosts(array('columns' => array('host_name'), 'filter' => array('groups' => array('>=' => $hg))));
  79                                 foreach ($res as $row) {
  80                                         # To be able to sum up alert totals:
  81                                         if (empty($hosts[$row['host_name']])) {
  82                                                 $hosts[$row['host_name']] = array();
  83                                         }
  84                                         $hosts[$row['host_name']][$hg] = $hg;
  85                                 }
  86                         }
  87                         $this->host_hostgroup = $hosts;
  88                 } elseif ($this->options['report_type'] == 'services') {
  89                         $services = false;
  90                         if($this->options['objects'] === Report_options::ALL_AUTHORIZED) {
  91                                 $services = Report_options::ALL_AUTHORIZED;
  92                         } else {
  93                                 foreach ($this->options['objects'] as $srv) {
  94                                         $services[$srv] = $srv;
  95                                 }
  96                         }
  97                 } elseif ($this->options['report_type'] == 'hosts') {
  98                         $hosts = false;
  99                         if($this->options['objects'] === Report_options::ALL_AUTHORIZED) {
 100                                 $hosts = Report_options::ALL_AUTHORIZED;
 101                         } else {
 102                                 if (is_array($this->options['objects'])) {
 103                                         foreach ($this->options['objects'] as $hn)
 104                                                 $hosts[$hn] = $hn;
 105                                 } else {
 106                                         $hosts[$this->options['objects']] = $this->options['objects'];
 107                                 }
 108                         }
 109                 }
 110
 111                 if (empty($hosts) && empty($services)) {
 112                         return "SELECT $fields FROM $this->db_table LIMIT 0";
 113                 }
 114
 115                 $object_selection = false;
 116                 if(($hosts === Report_options::ALL_AUTHORIZED) || ($services === Report_options::ALL_AUTHORIZED)) {
 117                         // screw filters, we're almighty
 118                 } elseif ($services) {
 119                         if ($services !== true) {
 120                                 $object_selection .= "(";
 121                                 $orstr = '';
 122                                 # Must do this the hard way to allow host_name indices to
 123                                 # take effect when running the query, since the construct
 124                                 # "concat(host_name, ';', service_description)" isn't
 125                                 # indexable
 126                                 foreach ($services as $srv => $discard) {
 127                                         $ary = explode(';', $srv);
 128                                         $h = $ary[0];
 129                                         $s = $ary[1];
 130                                         $object_selection .= $orstr . "(host_name = '" . $h . "'\n    AND (" ;
 131                                         if ($s) { /* this if-statement can probably just go away */
 132                                                 $object_selection .= "service_description = '" . $s . "' OR ";
 133                                         }
 134                                         $object_selection .= "event_type = 801))";
 135                                         $orstr = "\n OR ";
 136                                 }
 137                         }
 138                         if (!empty($object_selection))
 139                                 $object_selection .= ')';
 140                 } elseif ($hosts && $hosts !== true) {
 141                         $object_selection = "host_name IN(\n '" .
 142                                 join("',\n '", array_keys($hosts)) . "')";
 143                 }
 144                 switch ($this->options['state_types']) {
 145                         case 0:
 146                         case 3:
 147                         default:
 148                                 break;
 149                         case 1:
 150                                 $softorhard = 'hard = 0';
 151                                 break;
 152                         case 2:
 153                                 $softorhard = 'hard = 1';
 154                                 break;
 155                 }
 156
 157                 if (!$this->options['host_states'] || $this->options['host_states'] == Reports_Model::HOST_ALL) {
 158                         $host_states_sql = 'event_type = ' . Reports_Model::HOSTCHECK;
 159                 } else {
 160                         $x = array();
 161                         $host_states_sql = '(event_type = ' . Reports_Model::HOSTCHECK . ' ' .
 162                                 'AND state IN(';
 163                         for ($i = 0; $i < Reports_Model::HOST_ALL; $i++) {
 164                                 if (1 << $i & $this->options['host_states']) {
 165                                         $x[$i] = $i;
 166                                 }
 167                         }
 168                         $host_states_sql .= join(',', $x) . '))';
 169                 }
 170
 171                 if (!$this->options['service_states'] || $this->options['service_states'] == Reports_Model::SERVICE_ALL) {
 172                         $service_states_sql = 'event_type = ' . Reports_Model::SERVICECHECK;
 173                 } else {
 174                         $x = array();
 175                         $service_states_sql = '(event_type = ' . Reports_Model::SERVICECHECK .
 176                                 "\nAND state IN(";
 177                         for ($i = 0; $i < Reports_Model::SERVICE_ALL; $i++) {
 178                                 if (1 << $i & $this->options['service_states']) {
 179                                         $x[$i] = $i;
 180                                 }
 181                         }
 182                         $service_states_sql .= join(',', $x) . '))';
 183                 }
 184
 185                 switch ($this->options['alert_types']) {
 186                  case 1:
 187                         $alert_types = $host_states_sql;
 188                         break;
 189                  case 2:
 190                         $alert_types = $service_states_sql;
 191                         break;
 192                  case 3:
 193                         $alert_types = sql::combine('or', $host_states_sql, $service_states_sql);
 194                         break;
 195                 }
 196
 197                 if (isset($this->options['include_downtime']) && $this->options['include_downtime'])
 198                         $downtime = 'event_type < 1200 AND event_type > 1100';
 199
 200                 if (isset($this->options['include_process']) && $this->options['include_process'])
 201                         $process = 'event_type < 200';
 202
 203                 if($this->options['start_time']) {
 204                         $time_first = 'timestamp >= ' . $this->options['start_time'];
 205                 }
 206                 if($this->options['end_time']) {
 207                         $time_last = 'timestamp <= ' . $this->options['end_time'];
 208                 }
 209
 210                 if(isset($this->options['filter_output']) && $this->options['filter_output']) {
 211                         # convert fnmatch wildcards to sql ditos
 212                         $wc_str = $this->options['filter_output'];
 213                         $wc_str = preg_replace("/(?!\\\)\*/", '\1%', $wc_str);
 214                         $wc_str = preg_replace("/(?!\\\)\?/", '\1_', $wc_str);
 215                         # case insensitive. This also works on oracle
 216                         $wc_str = strtoupper($wc_str);
 217                         $wc_str = '%' . $wc_str . '%';
 218                         $wc_str_esc = $this->db->escape($wc_str);
 219                         $wildcard_filter = "\n UPPER(output) LIKE $wc_str_esc" .
 220                                 "\n OR UPPER(host_name) LIKE $wc_str_esc " .
 221                                 "\n OR UPPER(service_description) LIKE $wc_str_esc";
 222                 }
 223
 224                 $query = "SELECT " . $fields . "\nFROM " . $this->db_table;
 225                 $query .= ' WHERE '.
 226                         sql::combine('and',
 227                                 $time_first,
 228                                 $time_last,
 229                                 sql::combine('or',
 230                                         $process,
 231                                         sql::combine('and',
 232                                                 $object_selection,
 233                                                 sql::combine('or',
 234                                                         $downtime,
 235                                                         sql::combine('and',
 236                                                                 $softorhard,
 237                                                                 $alert_types)))),
 238                                 $wildcard_filter
 239                         );
 240
 241
 242                 $extra_sql = array();
 243                 $db = $this->db; // for closures
 244                 $implode_str = ') OR (';
 245                 // summa summarum: Don't use the API unless you're *authorized* (this is really slow)
 246                 if(1 & $this->options["alert_types"] && !$auth->authorized_for("host_view_all")) {
 247                         $ls = op5Livestatus::instance();
 248                         $hosts = $ls->query("hosts", null, array("name"), array('auth' => $auth->get_user()));
 249                         $objtosql = function($e) use ($db) {
 250                                                         return $db->escape(current($e));
 251                                                 };
 252                         if (!empty($hosts[1])) {
 253                                 $extra_sql[] = sql::combine(
 254                                                 "AND",
 255                                                 "host_name IN (".
 256                                                 implode(", ",array_map($objtosql,$hosts[1])).")",
 257                                                 "service_description = ''"
 258                                                 );
 259                         }
 260                         else {
 261                                 $extra_sql[] = "service_description != ''";
 262                                 $implode_str = ') AND (';
 263                         }
 264                 }
 265
 266                 // summa summarum: Don't use the API unless you're *authorized* (this is really slow)
 267                 if(2 & $this->options["alert_types"] && !$auth->authorized_for("service_view_all")) {
 268                         $ls = op5Livestatus::instance();
 269                         $services = $ls->query("services", null, array("host_name", "description"), array('auth' => $auth->get_user()));
 270                         $objtosql = function($e) use ($db) {
 271                                                         return '('.$db->escape($e[0]).', '.$db->escape($e[1]).')';
 272                                                 };
 273                         if (!empty($services[1])) {
 274                                 $extra_sql[] = "(host_name, service_description) IN (".
 275                                                 implode(", ",array_map($objtosql,$services[1])).")";
 276                         }
 277                         else {
 278                                 $extra_sql[] = "service_description = ''";
 279                                 $implode_str = ') AND (';
 280                         }
 281                 }
 282
 283                 if(count($extra_sql) > 0) {
 284                         /* The innermost parenthesis matches the parenthesis in $implode_str */
 285                         $query .= " AND ((".implode($implode_str, $extra_sql)."))";
 286                 }
 287
 288                 return $query;
 289         }
 290 }