SQL query returns inconsistent disabled items amount

[marcioale]

Hi guys,

I´m trying to generate report of all disable items on Zabbix database with the following query:

select a1.host, a2.itemid,a2.name from hosts a1, items a2
where a1.hostid = a2.hostid
and a2.status = 1;

When I run this query I got 183 disabled items but the system information widget tells me that I have 201 disabled items

Do you know why the amount does not match? Is my sql query right?

Thanks,

[Hamardaban]

The number of unsupported items is calculated from information in the cache, not in the database. To get a list of unsupported items, you can use a filter on the data item form.

[marcioale]

Hi Hamardaban,

I Know that we can  get a list of unsupported/disabled items from data item form but the point is, we cannot export this report to work on this data afterwards.
So that´s why I´ve been trying to retrieve a report from Zabbix database.

The following query is not wront at all.

select a1.host, a2.itemid,a2.name from hosts a1, items a2
where a1.hostid = a2.hostid
and a2.status = 1;

For example, when I change a item status on Zabbix its status also change on querys result and what I need to know is why the result of the query does not match with the system information widget.

Is some "inner join" with another table missing? Or is there another way to generate a report of disabled items on Zabbix, like using command line.

Thanks,

[marcioale]

Hi Hamardaban,

I Know that we can get a list of unsupported/disabled items from data item form but the point is, we cannot export this report to work on this data afterwards.
So that´s why I´ve been trying to retrieve a report from Zabbix database.

The following query is not wront at all.

select a1.host, a2.itemid,a2.name from hosts a1, items a2
where a1.hostid = a2.hostid
and a2.status = 1;

For example, when I change a item status on Zabbix its status also change on querys result and what I need to know is why the result of the query does not match with the system information widget.

Is some "inner join" with another table missing? Or is there another way to generate a report of disabled items on Zabbix, like using command line.

Thanks,

[Hamardaban]

What version of zabbix do you use?
Try counting the number of deactivated data items without binding them to hosts.
But once again, data for the GUI is taken from the cache, not from the database. How they get there, how they are counted, and how they are synchronized with the database is not an easy question. You can try looking in the source codes. You may find the following information about key values in tables useful (for version 4.4)


Host
status:
0, HOST_STATUS_MONITORED - Host is monitored
1, HOST_STATUS_NOT_MONITORED - Host is not monitored
3, HOST_STATUS_TEMPLATE - The record is template
5, HOST_STATUS_PROXY_ACTIVE - The record is active proxy
6, HOST_STATUS_PROXY_PASSIVE - The record is passive proxy

flags:
0x00, ZBX_FLAG_DISCOVERY_NORMAL - Normal host
0x02, ZBX_FLAG_DISCOVERY_PROTOTYPE - Host prototype
0x04, ZBX_FLAG_DISCOVERY_CREATED - Auto-created host


Items

type integer Type of the item or LLD rule:
0, ITEM_TYPE_ZABBIX - Zabbix agent
1, ITEM_TYPE_SNMPV1 - SNMPv1 agent
2, ITEM_TYPE_TRAPPER - Zabbix trapper
3, ITEM_TYPE_SIMPLE - Simple check
4, ITEM_TYPE_SNMPV2C - SNMPv2 agent
5, ITEM_TYPE_INTERNAL - Zabbix internal
6, ITEM_TYPE_SNMPV3 - SNMPv3 agent
7, ITEM_TYPE_ZABBIX_ACTIVE - Zabbix agent (active) check
8, ITEM_TYPE_AGGREGATE - Aggregate
9, ITEM_TYPE_HTTPTEST - HTTP test (web monitoring scenario step)
10, ITEM_TYPE_EXTERNAL - External check
11, ITEM_TYPE_DB_MONITOR - Database monitor
12, ITEM_TYPE_IPMI - IPMI agent
13, ITEM_TYPE_SSH - SSH agent
14, ITEM_TYPE_TELNET - TELNET agent
15, ITEM_TYPE_CALCULATED - Calculated
16, ITEM_TYPE_JMX - JMX agent
17, ITEM_TYPE_SNMPTRAP - SNMP trap
18, ITEM_TYPE_DEPENDENT - Dependent item
19, ITEM_TYPE_HTTPAGENT - HTTP agent

status integer Status of the item:
0, ITEM_STATUS_ACTIVE - Active
1, ITEM_STATUS_DISABLED - Disabled