/** * Returns the history value of the item at the given time. If no value exists at the given time, the function * will return the previous value. * * The $db_item parameter must have the value_type and itemid properties set. * * @param array $db_item * @param int $clock * @param int $ns * * @return string */ function item_get_history($db_item, $clock, $ns) { $value = null; $table = CHistoryManager::getTableName($db_item['value_type']); $sql = 'SELECT value' . ' FROM ' . $table . ' WHERE itemid=' . zbx_dbstr($db_item['itemid']) . ' AND clock=' . zbx_dbstr($clock) . ' AND ns=' . zbx_dbstr($ns); if (null != ($row = DBfetch(DBselect($sql, 1)))) { $value = $row['value']; } if ($value != null) { return $value; } $max_clock = 0; $sql = 'SELECT DISTINCT clock' . ' FROM ' . $table . ' WHERE itemid=' . zbx_dbstr($db_item['itemid']) . ' AND clock=' . zbx_dbstr($clock) . ' AND ns<' . zbx_dbstr($ns); if (null != ($row = DBfetch(DBselect($sql)))) { $max_clock = $row['clock']; } if ($max_clock == 0) { $sql = 'SELECT MAX(clock) AS clock' . ' FROM ' . $table . ' WHERE itemid=' . zbx_dbstr($db_item['itemid']) . ' AND clock<' . zbx_dbstr($clock); if (null != ($row = DBfetch(DBselect($sql)))) { $max_clock = $row['clock']; } } if ($max_clock == 0) { return $value; } if ($clock == $max_clock) { $sql = 'SELECT value' . ' FROM ' . $table . ' WHERE itemid=' . zbx_dbstr($db_item['itemid']) . ' AND clock=' . zbx_dbstr($clock) . ' AND ns<' . zbx_dbstr($ns); } else { $sql = 'SELECT value' . ' FROM ' . $table . ' WHERE itemid=' . zbx_dbstr($db_item['itemid']) . ' AND clock=' . zbx_dbstr($max_clock) . ' ORDER BY itemid,clock desc,ns desc'; } if (null != ($row = DBfetch(DBselect($sql, 1)))) { $value = $row['value']; } return $value; }
/** * Get history data. * * @param array $options * @param array $options['itemids'] * @param boolean $options['editable'] * @param string $options['pattern'] * @param int $options['limit'] * @param string $options['order'] * * @return array|int item data as array or false if error */ public function get($options = array()) { $result = array(); $sqlParts = array('select' => array('history' => 'h.itemid'), 'from' => array(), 'where' => array(), 'group' => array(), 'order' => array(), 'limit' => null); $defOptions = array('history' => ITEM_VALUE_TYPE_UINT64, 'hostids' => null, 'itemids' => null, 'editable' => null, 'nopermissions' => null, 'filter' => null, 'search' => null, 'searchByAny' => null, 'startSearch' => null, 'excludeSearch' => null, 'searchWildcardsEnabled' => null, 'time_from' => null, 'time_till' => null, 'output' => API_OUTPUT_EXTEND, 'countOutput' => null, 'groupCount' => null, 'preservekeys' => null, 'sortfield' => '', 'sortorder' => '', 'limit' => null); $options = zbx_array_merge($defOptions, $options); if (!($tableName = CHistoryManager::getTableName($options['history']))) { $tableName = 'history'; } $sqlParts['from']['history'] = $tableName . ' h'; // editable + PERMISSION CHECK if (USER_TYPE_SUPER_ADMIN == self::$userData['type'] || $options['nopermissions']) { } else { $items = API::Item()->get(array('itemids' => $options['itemids'] === null ? null : $options['itemids'], 'output' => array('itemid'), 'editable' => $options['editable'], 'preservekeys' => true, 'webitems' => true)); $options['itemids'] = array_keys($items); } // itemids if (!is_null($options['itemids'])) { zbx_value2array($options['itemids']); $sqlParts['where']['itemid'] = dbConditionInt('h.itemid', $options['itemids']); } // hostids if (!is_null($options['hostids'])) { zbx_value2array($options['hostids']); $sqlParts['from']['items'] = 'items i'; $sqlParts['where']['i'] = dbConditionInt('i.hostid', $options['hostids']); $sqlParts['where']['hi'] = 'h.itemid=i.itemid'; } // time_from if (!is_null($options['time_from'])) { $sqlParts['where']['clock_from'] = 'h.clock>=' . zbx_dbstr($options['time_from']); } // time_till if (!is_null($options['time_till'])) { $sqlParts['where']['clock_till'] = 'h.clock<=' . zbx_dbstr($options['time_till']); } // filter if (is_array($options['filter'])) { $this->dbFilter($sqlParts['from']['history'], $options, $sqlParts); } // search if (is_array($options['search'])) { zbx_db_search($sqlParts['from']['history'], $options, $sqlParts); } // output if ($options['output'] == API_OUTPUT_EXTEND) { unset($sqlParts['select']['clock']); $sqlParts['select']['history'] = 'h.*'; } // countOutput if (!is_null($options['countOutput'])) { $options['sortfield'] = ''; $sqlParts['select'] = array('count(DISTINCT h.hostid) as rowscount'); // groupCount if (!is_null($options['groupCount'])) { foreach ($sqlParts['group'] as $key => $fields) { $sqlParts['select'][$key] = $fields; } } } // sorting $sqlParts = $this->applyQuerySortOptions($tableName, $this->tableAlias(), $options, $sqlParts); // limit if (zbx_ctype_digit($options['limit']) && $options['limit']) { $sqlParts['limit'] = $options['limit']; } $sqlParts['select'] = array_unique($sqlParts['select']); $sqlParts['from'] = array_unique($sqlParts['from']); $sqlParts['where'] = array_unique($sqlParts['where']); $sqlParts['order'] = array_unique($sqlParts['order']); $sqlSelect = ''; $sqlFrom = ''; $sqlOrder = ''; if (!empty($sqlParts['select'])) { $sqlSelect .= implode(',', $sqlParts['select']); } if (!empty($sqlParts['from'])) { $sqlFrom .= implode(',', $sqlParts['from']); } $sqlWhere = !empty($sqlParts['where']) ? ' WHERE ' . implode(' AND ', $sqlParts['where']) : ''; if (!empty($sqlParts['order'])) { $sqlOrder .= ' ORDER BY ' . implode(',', $sqlParts['order']); } $sqlLimit = $sqlParts['limit']; $sql = 'SELECT ' . $sqlSelect . ' FROM ' . $sqlFrom . $sqlWhere . $sqlOrder; $dbRes = DBselect($sql, $sqlLimit); while ($data = DBfetch($dbRes)) { if ($options['countOutput']) { $result = $data; } else { $result[] = $data; } } if (is_null($options['preservekeys'])) { $result = zbx_cleanHashes($result); } return $result; }