[db] Select all unsupported items

[Failsloth]

Hello, topic is pretty much self-explained.
How can I find all the unsupported items in the database? Using mysql.
As I remember there was an "error" column in the "items" few versions ago. Tho I can't see it now.
I mean, in which table they're being marked as unsupported? Thanks in advance.
Zabbix v4

[Hamardaban]

select count(p.objectid) from problem p, items i where p.objectid=i.itemid and p.source=3 and p.object=4 and i.flags in (0,1,4);

[Failsloth]

select count(p.objectid) from problem p, items i where p.objectid=i.itemid and p.source=3 and p.object=4 and i.flags in (0,1,4);

This returns the amount of unsupported items as I can see. Any way I can get a list of them?
Sorry I'm lacking the knowledge of sql syntax.

[Hamardaban]

select * from problem p, items i where p.objectid=i.itemid and p.source=3 and p.object=4 and i.flags in (0,1,4);
and choose the fields you need

[Failsloth]

Hamardaban , thanks a lot.
Tho it seems to be displaying previous problems items had as well. Any way to make it show only currently "broken" items?

[Zhen]

Hello Failsloth
Did you find how to list only the active unsuported items ?
Thank's

[Zhen]

Hello,
If someone is still searching i did it by myself.
You can find my request here :


select hs.name as 'Groupe Applicatif', h.host as 'Nom Host', h.name as 'Nom Machine', i.description, p.name as 'Description du probleme', i.lifetime, i.name as 'Description de l item', h.hostid, i.itemid
, from_unixtime(p.clock) as 'Date d ouverture de l incident', from_unixtime(r_clock) as 'Date de cloture', p.severity as 'Severite de l alerte 0 étant la plus faible et 5 la plus haute'

from items i
join problem p on p.objectid = i.itemid
join hosts h on h.hostid = i.hostid
join hosts_groups hg on i.hostid = hg.hostid
join hstgrp hs on hg.groupid = hs.groupid
where p.source=3 and p.object=4 and i.flags in (0,1,4) and r_clock = '0';

With the r_clock = 0 you have all the alert who are still on.