Hosts search in monitoring > hosts not finding hosts when using the "name" filter

[stefano_775]

Hi!

Odd issue with our Zabbix 6.0.18 environment:

All recently added hosts can't be found when searching by partial or full hostname in monitoring > hosts and using the name filter. Hosts which were configured before the upgrade to 6.0 (from 3.0 + mysql --> postgres migration) can still be found.

Enabling debug mode on the UI showed me the query, and I can recreate the problem when I search directly in the database:

Original query (from UI debug output):

zabbix=# SELECT h.hostid,h.name,h.status FROM hosts h WHERE h.flags IN (0,4) AND h.status IN (0,1) AND ((h.name_upper LIKE '%TEST0015%' ESCAPE '!')) ORDER BY h.name LIMIT 20001;
hostid | name | status
--------+------+--------
(0 rows)


Edited query, where I replaced 'h.name_upper' with `h.name` and included 'h.name_upper' in the SELECT statement to see it:


zabbix=# SELECT h.hostid,h.name,h.name_upper,h.status FROM hosts h WHERE h.flags IN (0,4) AND h.status IN (0,1) AND ((h.name LIKE '%TEST0015-AP%' ESCAPE '!')) ORDER BY h.name LIMIT 20001;
hostid | name | name_upper | status
--------+-----------------+------------+--------
118320 | TEST0015-AP0001 | | 0
118321 | TEST0015-AP0002 | | 0
118322 | TEST0015-AP0003 | | 0
118323 | TEST0015-AP0004 | | 0
118324 | TEST0015-AP0005 | | 0
119491 | TEST0015-AP0006 | | 0
119492 | TEST0015-AP0007 | | 0
118317 | TEST0015-AP0008 | | 0
118316 | TEST0015-AP0009 | | 0
118314 | TEST0015-AP0010 | | 0
119498 | TEST0015-AP0011 | | 0
119499 | TEST0015-AP0012 | | 0
119500 | TEST0015-AP0013 | | 0
119501 | TEST0015-AP0014 | | 0
119502 | TEST0015-AP0015 | | 0
119494 | TEST0015-AP0016 | | 0
119493 | TEST0015-AP0017 | | 0
119497 | TEST0015-AP0018 | | 0
119496 | TEST0015-AP0019 | | 0
119495 | TEST0015-AP0020 | | 0
(20 rows)

From the above I can see the reason why the UI is not returning anything is due to the fact that the field name_upper in the database is not being populated for newly added hosts.

I have looked at the change logs to see if this might have been resolved between 6.0.18 and 6.0.22, but I don't believe there is anything related.

Any idea anyone?

Thanks

Stefano

[dimir]

The hosts.name field contains so called visible name, you probably are looking for the field hosts.host, which contains so called technical name, which is used in the filter. Confusing? Yes, but what can one do. :-) See details here: https://www.zabbix.com/documentation...nfiguration

[cyber]

If UI uses name_upper to select results (... AND ((h.name_upper LIKE...) and that field is not filled (what it should be), then you do not get results...
That "name_upper" field appeared somewhere between 6.0.9 and 6.0.13 (those sources I have in some reason downloaded, but I don't bother to go look for exact version.. 6.0.13 src says "DBpatch_6000008") ... Something with that triggers there? There should be 2 triggers for hosts table... for insert and for update

\dS hosts;
...
Triggers:
    hosts_name_upper_insert AFTER INSERT ON hosts FOR EACH ROW EXECUTE FUNCTION hosts_name_upper_upper()
    hosts_name_upper_update AFTER UPDATE OF name ON hosts FOR EACH ROW EXECUTE FUNCTION hosts_name_upper_upper()
​

[stefano_775]

Thank you both for the responses.

dimir, my understanding is that the visible name is optional, and it is the one which becomes visible if it's set. Not setting it it should not prevent the host from becoming searchable in the standard UI "name" filters. The UI seems to use the name_upper field for "name" searches, which I don't believe is linked to the visible name. From what I've read `name_upper` should get automatically populated in the background to optimise searches. Unless I am missing something?

cyber can you think of a way to debug why the triggers above are not being actioned when adding or updating a host? We have hosts with the field correctly set, so it should not be a database/table issue.

Thanks again!

Stefano

[dimir]

First, check if those triggers exist in your database. I believe this functionality was added in 6.0.11, it is considered a "breaking change": https://www.zabbix.com/documentation...ade_notes_6011

[stefano_775]

The triggers are indeed missing, there's no triggers in the hosts table at all.

If my query is correct the below are the triggers for the entire database:

```
zabbix=# SELECT event_object_table AS table_name ,trigger_name
FROM information_schema.triggers
GROUP BY table_name , trigger_name
ORDER BY table_name ,trigger_name ;
table_name | trigger_name
---------------------------+-------------------
_compressed_hypertable_10 | ts_insert_blocker
_compressed_hypertable_11 | ts_insert_blocker
_compressed_hypertable_12 | ts_insert_blocker
_compressed_hypertable_13 | ts_insert_blocker
_compressed_hypertable_14 | ts_insert_blocker
_compressed_hypertable_8 | ts_insert_blocker
_compressed_hypertable_9 | ts_insert_blocker
history | ts_insert_blocker
history_log | ts_insert_blocker
history_str | ts_insert_blocker
history_text | ts_insert_blocker
history_uint | ts_insert_blocker
trends | ts_insert_blocker
trends_uint | ts_insert_blocker
(14 rows)
```

I will do some comparison against a fresh install, I am worried about what else might me missing.
We did a migration from a very old version (3.0) and changed database from mysql to postgres, but we kept a close eye to error messages etc.. so I am surprised this is missing.

If this ends up being the only triggers missing, any suggestion on how to rectify?

Thanks

Stefano

[dimir]

I wonder if you could try to execute the related part from the schema file:

# zgrep -A6 name_upper_ /usr/share/zabbix-sql-scripts/postgresql/server.sql.gz
create or replace function hosts_name_upper_upper()
returns trigger language plpgsql as $func$
begin
update hosts set name_upper=upper(name)
where hostid=new.hostid;
return null;
end $func$;
create trigger hosts_name_upper_insert after insert
on hosts
for each row execute function hosts_name_upper_upper();
create trigger hosts_name_upper_update after update
of name on hosts
for each row execute function hosts_name_upper_upper();
create or replace function items_name_upper_upper()
returns trigger language plpgsql as $func$
begin
update items set name_upper=upper(name)
where itemid=new.itemid;
return null;
end $func$;
create trigger items_name_upper_insert after insert
on items
for each row execute function items_name_upper_upper();
create trigger items_name_upper_update after update
of name on items
for each row execute function items_name_upper_upper();​
[...]