Get LAST value of an item from database

[registration_is_lame]

Is there a way to find the lastcheck of an item? Otherwise we have a problem here.

If I try

SELECT value FROM history_uint WHERE itemid = '12345' AND clock >= UNIX_TIMESTAMP(NOW() - INTERVAL 60 SECOND) ORDER BY itemid DESC LIMIT 1;

then sometimes it returns no row if the item hasn't polled for the within last 60 seconds. If I increase it to 120 seconds and LIMIT 1 we still have the same problem if the item hasn't polled for last 120 seconds due to any server delay etc.,

I think back then there was a column lastcheck in older versions, I can't find any such column in zabbix 4.0.

If we had such column then we could use WHERE clock >= lastcheck_column_value in the query.

How is latest data showing us last check date of a item? Is it showing the date from the history_uint table clock column? How can I fetch the same so I can use it in the above query?

I want to SUM the value of 3 items last value and store it in another item. This is needed because I can't use calculated item on LLD discovered item since calculated item doesn't allow wildcard key names to SUM the discovered items.

[skygge]

If you mean "lastcheck" as a "timestamp of last item value" then there is a "clock" column : SELECT value FROM history_uint WHERE itemid = '12345' order by clock desc limit 1;

~~~~~
Well, I remember the old days when Zabbix had a dedicated DB table for this. It was called "latest_values" if I remember correctly.
Then this table was removed from Zabbix, and, in my humble opinion, this was a bad decision because I used this table everywhere: in reports, custom views, and so on.

So I decided, to avoid modifying tons of reports, to re-create this table by myself and fill it with triggers.
This is working until now without any problems so...
This is the table structure:

CREATE TABLE `latest_values` (
`itemid` bigint(20) unsigned NOT NULL,
`history` double(16,4) DEFAULT NULL,
`history_log` text,
`history_str` varchar(255) DEFAULT NULL,
`history_sync` int(11) DEFAULT NULL,
`history_text` text,
`history_uint` bigint(20) DEFAULT NULL,
`clock` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`itemid`),
KEY `latest_values_1` (`itemid`,`clock`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and the trigger for HISTORY_UINT (similar triggers I have for every history table):

CREATE DEFINER=`root`@`localhost` TRIGGER `latest_values_uint_bi` BEFORE INSERT ON history_uint FOR EACH ROW
begin
DECLARE itemid_exists Boolean;
SELECT
count(itemid)
INTO
@itemid_exists
FROM
latest_values
WHERE
itemid = NEW.itemid;

IF @itemid_exists = 0
THEN
INSERT INTO
latest_values (itemid, history_uint, clock)
VALUES
(NEW.itemid, NEW.value, NEW.clock);
ELSE
UPDATE
latest_values
SET
history_uint = NEW.value,
clock = NEW.clock
WHERE
itemid = NEW.itemid;
END IF;
END





Sorry Zabbix team, I really needed this table
Querying history tables for latest values for 2000 hosts takes forever, querying "latest_values" table - milliseconds.

[Vermizz]

Hi,
I had a similar problem as you. I wanted to pull the last value for the zabbix agent version installed for windows and linux hosts group.
I used the API for this and built a report.
I upload it to my github: https://github.com/Vermiz/Zabbix/tre..._Agent_version