It is strange to hear that you are writing SQL queries while using Grafana. Why? Have you heard about Zabbix plugin?

Hey Glebs,

I need the flexibility of a database query to be able to retrieve the info I'm using.

Here is an example. I have several wireless controllers. I do a discovery on each one to see what APs are on the controllers database  and check wether the APs are up (1) or down (2).

With Zabbix plugin I haven't managed a way to get a count of APs that are UP and APs that are DOWN. I could preprocess the status values to 1 = up and 0 = down but still, I have to be very careful with the Time Range. If it's too short I will miss values and if it's too large I will get too many.

AFAIK there is no way to get the latest value on a metric on Grafana, it's always time dependant and very hard to know where to set the limit, it really depends on when the data was written into the DB (clock).

I have attached a screenshot to explain it better. The AP status items are being being queried every 5 minutes so I configured the time range on the Zabbix datasource to 7 minutes getting 64 APs UP but there are really 111 APs UP (see the SQL output right beside it). It doesn't matter how I fiddle with the time range I can't get it to state the correct number of APs being UP so I figured it would be easier and more accurate to directly query the DB.

I hope I have explained myself properly!

Best regards,

Aarón

If I read your SQL query correctly, it selects latest value for all items in history_uint, no wonder it takes ages to complete. It would be more efficient to get the list of itemid's you are interested in and only then find out their latest values (where itemid in (...)).

Hi Glebs,

I got the list of items I want to query and managed to query the history_uint for only those items, but I'm failing to keep the most recent values and also discern when an AP is UP or when it's DOWN.

Let me explain, if I want to see if an AP is down I would filter the query to see if value == 2. If an AP goes down (value == 2) and then it comes up (value == 1) I will always get a match on value == 2 even though the AP is already up...

It's very complicated to query the last value on multiple items, that's why I was asking for that fix above. I checked the documentation (https://zabbix.org/wiki/Docs/DB_schema/2.0/items) on the database there was a field on the items table called 'lastvalue' that would help me a lot but it was removed on version 2.2.

I don't want to modify Zabbix code so I will try to create a trigger that on the event of an INSERT on history_uint table, I will store the itemid/value/clock into a new table.

Cheers,

Aarón

The trigger sounds like a good idea. As to why it was removed from items table, it's because it was causing deadlocks and performance issues every time new values were to be added to the database and that decision was right. But I think we forgot to think of other possibilities to have latest data always available.

Hello,

I did it just for history_uint but this is replicable to all history tables. I created two functions, one for inserting/updating and another one for deleting.

The new table 'lastvalue_uint' stores itemid, clock and value:

CREATE TABLE lastvalue_uint(
itemid bigint primary key,
clock integer NOT NULL,
value numeric(20,0) NOT NULL);

The insert/update is triggered whenever an item is inserted/updated in history_uint.

CREATE OR REPLACE FUNCTION lastvalue_uint_func() RETURNS TRIGGER AS $BODY$
 DECLARE
 BEGIN
 IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE' ) THEN
 INSERT INTO lastvalue_uint(itemid, clock, value)
 VALUES(NEW.itemid, NEW.clock, NEW.value)
 ON CONFLICT (itemid) DO
 UPDATE SET
 clock = NEW.clock, 
 value = NEW.value;
 END IF;
 RETURN NEW;
 END;
$BODY$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_insert_lastvalue_uint
BEFORE INSERT OR UPDATE ON history_uint 
FOR EACH ROW EXECUTE 
PROCEDURE lastvalue_uint_func();

The delete is triggered whenever an item is deleted in the items table.

CREATE OR REPLACE FUNCTION lastvalue_uint_func_delete() RETURNS TRIGGER AS $BODY$
 DECLARE
 BEGIN
 IF (TG_OP = 'DELETE') THEN
 DELETE FROM lastvalue_uint WHERE itemid = OLD.itemid;
 END IF;
 RETURN OLD;
 END;
$BODY$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_delete_lastvalue_uint
BEFORE DELETE ON items 
FOR EACH ROW EXECUTE 
PROCEDURE lastvalue_uint_func_delete();

So far the size of the table is slowly growing but I will have to wait a day or so to see that it matches the size of history_uint unique items.

Hope this helps someone!

Cheers,

Aarón

After a week the solution seems stable, no tables have outgrown the history_* unique itemids so that's good.

I finally created a function to update/insert on lastvalue_* for each history_* and a function for every delete on items. Probably there is a better way to do this but having separate functions keeps it pretty simple and easier to understand.

I have attached the functions/triggers I'm using, now the queries take almost no time and are a lot simpler.

Comparison on number of itemids on history_uint vs lastvalue_uint:

zabbix=> select count(DISTINCT itemid) from history_uint ;
 count 
-------
 36736
(1 row)
zabbix=> select count(DISTINCT itemid) from lastvalue_uint ;
 count 
-------
 29667
(1 row)

Comparison on size of history_uint and lastvalue_uint:

zabbix=> SELECT pg_size_pretty( pg_total_relation_size('history_uint'));
 pg_size_pretty 
----------------
 7612 MB
(1 row)
 
zabbix=> SELECT pg_size_pretty( pg_total_relation_size('lastvalue_uint'));
 pg_size_pretty 
----------------
 4240 kB
(1 row)

Triggers.functions.lastvalue.txt

Even though it's working, it would be great if this could be added on future Zabbix releases.

Cheers,

Aarón