/**
* 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;
}
