25 Zabbix系统数据表结构介绍
自学Zabbix之路15.1 Zabbix数据库表结构简单解析-Hosts表、Hosts_groups表、Interface表
自学Zabbix之路15.2 Zabbix数据库表结构简单解析-Items表
自学Zabbix之路15.3 Zabbix数据库表结构简单解析-Triggers表、Applications表、 Mapplings表
自学Zabbix之路15.4 Zabbix数据库表结构简单解析-Expressions表、Media表、 Events表
自学Zabbix之路15.5 Zabbix数据库表结构简单解析-其他 表
1. 查看目前zabbix系统所有数据表：
[root@localhost /]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 170786 Server version: 5.5.60-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | zabbix | +--------------------+ 5 rows in set (0.01 sec) MariaDB [(none)]> use zabbix Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [zabbix]> show tables; +----------------------------+ | Tables_in_zabbix | +----------------------------+ | acknowledges | | actions | | alerts | | application_discovery | | application_prototype | | application_template | | applications | | auditlog | | auditlog_details | | autoreg_host | | conditions | | config | | corr_condition | | corr_condition_group | | corr_condition_tag | | corr_condition_tagpair | | corr_condition_tagvalue | | corr_operation | | correlation | | dashboard | | dashboard_user | | dashboard_usrgrp | | dbversion | | dchecks | | dhosts | | drules | | dservices | | escalations | | event_recovery | | event_suppress | | event_tag | | events | | expressions | | functions | | globalmacro | | globalvars | | graph_discovery | | graph_theme | | graphs | | graphs_items | | group_discovery | | group_prototype | | history | | history_log | | history_str | | history_text | | history_uint | | host_discovery | | host_inventory | | hostmacro | | hosts | | hosts_groups | | hosts_templates | | housekeeper | | hstgrp | | httpstep | | httpstep_field | | httpstepitem | | httptest | | httptest_field | | httptestitem | | icon_map | | icon_mapping | | ids | | images | | interface | | interface_discovery | | item_application_prototype | | item_condition | | item_discovery | | item_preproc | | items | | items_applications | | maintenance_tag | | maintenances | | maintenances_groups | | maintenances_hosts | | maintenances_windows | | mappings | | media | | media_type | | opcommand | | opcommand_grp | | opcommand_hst | | opconditions | | operations | | opgroup | | opinventory | | opmessage | | opmessage_grp | | opmessage_usr | | optemplate | | problem | | problem_tag | | profiles | | proxy_autoreg_host | | proxy_dhistory | | proxy_history | | regexps | | rights | | screen_user | | screen_usrgrp | | screens | | screens_items | | scripts | | service_alarms | | services | | services_links | | services_times | | sessions | | slides | | slideshow_user | | slideshow_usrgrp | | slideshows | | sysmap_element_trigger | | sysmap_element_url | | sysmap_shape | | sysmap_url | | sysmap_user | | sysmap_usrgrp | | sysmaps | | sysmaps_elements | | sysmaps_link_triggers | | sysmaps_links | | tag_filter | | task | | task_acknowledge | | task_check_now | | task_close_problem | | task_remote_command | | task_remote_command_result | | timeperiods | | trends | | trends_uint | | trigger_depends | | trigger_discovery | | trigger_tag | | triggers | | users | | users_groups | | usrgrp | | valuemaps | | widget | | widget_field | +----------------------------+ 144 rows in set (0.00 sec) MariaDB [zabbix]>
2. 查看hosts数据表
MariaDB [zabbix]> show create table hosts G;
*************************** 1. row ***************************
Table: hosts
Create Table: CREATE TABLE `hosts` (
`hostid` bigint(20) unsigned NOT NULL,
`proxy_hostid` bigint(20) unsigned DEFAULT NULL,
`host` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',
`status` int(11) NOT NULL DEFAULT '0',
`disable_until` int(11) NOT NULL DEFAULT '0',
`error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
`available` int(11) NOT NULL DEFAULT '0',
`errors_from` int(11) NOT NULL DEFAULT '0',
`lastaccess` int(11) NOT NULL DEFAULT '0',
`ipmi_authtype` int(11) NOT NULL DEFAULT '-1',
`ipmi_privilege` int(11) NOT NULL DEFAULT '2',
`ipmi_username` varchar(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`ipmi_password` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '',
`ipmi_disable_until` int(11) NOT NULL DEFAULT '0',
`ipmi_available` int(11) NOT NULL DEFAULT '0',
`snmp_disable_until` int(11) NOT NULL DEFAULT '0',
`snmp_available` int(11) NOT NULL DEFAULT '0',
`maintenanceid` bigint(20) unsigned DEFAULT NULL,
`maintenance_status` int(11) NOT NULL DEFAULT '0',
`maintenance_type` int(11) NOT NULL DEFAULT '0',
`maintenance_from` int(11) NOT NULL DEFAULT '0',
`ipmi_errors_from` int(11) NOT NULL DEFAULT '0',
`snmp_errors_from` int(11) NOT NULL DEFAULT '0',
`ipmi_error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
`snmp_error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
`jmx_disable_until` int(11) NOT NULL DEFAULT '0',
`jmx_available` int(11) NOT NULL DEFAULT '0',
`jmx_errors_from` int(11) NOT NULL DEFAULT '0',
`jmx_error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
`name` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',
`flags` int(11) NOT NULL DEFAULT '0',
`templateid` bigint(20) unsigned DEFAULT NULL,
`description` text COLLATE utf8_bin NOT NULL,
`tls_connect` int(11) NOT NULL DEFAULT '1',
`tls_accept` int(11) NOT NULL DEFAULT '1',
`tls_issuer` varchar(1024) COLLATE utf8_bin NOT NULL DEFAULT '',
`tls_subject` varchar(1024) COLLATE utf8_bin NOT NULL DEFAULT '',
`tls_psk_identity` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',
`tls_psk` varchar(512) COLLATE utf8_bin NOT NULL DEFAULT '',
`proxy_address` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`auto_compress` int(11) NOT NULL DEFAULT '1',
PRIMARY KEY (`hostid`),
KEY `hosts_1` (`host`),
KEY `hosts_2` (`status`),
KEY `hosts_3` (`proxy_hostid`),
KEY `hosts_4` (`name`),
KEY `hosts_5` (`maintenanceid`),
KEY `c_hosts_3` (`templateid`),
CONSTRAINT `c_hosts_3` FOREIGN KEY (`templateid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE,
CONSTRAINT `c_hosts_1` FOREIGN KEY (`proxy_hostid`) REFERENCES `hosts` (`hostid`),
CONSTRAINT `c_hosts_2` FOREIGN KEY (`maintenanceid`) REFERENCES `maintenances` (`maintenanceid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
ERROR: No query specified
3. 查看interface数据表
MariaDB [zabbix]> show create table interface G;
*************************** 1. row ***************************
Table: interface
Create Table: CREATE TABLE `interface` (
`interfaceid` bigint(20) unsigned NOT NULL,
`hostid` bigint(20) unsigned NOT NULL,
`main` int(11) NOT NULL DEFAULT '0',
`type` int(11) NOT NULL DEFAULT '0',
`useip` int(11) NOT NULL DEFAULT '1',
`ip` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '127.0.0.1',
`dns` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`port` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '10050',
`bulk` int(11) NOT NULL DEFAULT '1',
PRIMARY KEY (`interfaceid`),
KEY `interface_1` (`hostid`,`type`),
KEY `interface_2` (`ip`,`dns`),
CONSTRAINT `c_interface_1` FOREIGN KEY (`hostid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
ERROR: No query specified
目前zabbix系统监控2台主机：
查看一下hostid 10263 信息：
MariaDB [zabbix]> select * from interface a inner join hosts b on a.hostid=b.hostid where a.hostid=10263 G;
*************************** 1. row ***************************
interfaceid: 3
hostid: 10263
main: 1
type: 2
useip: 1
ip: 172.18.100.25
dns:
port: 161
bulk: 1
hostid: 10263
proxy_hostid: NULL
host: CARLOS_test_100.25
status: 0
disable_until: 0
error:
available: 0
errors_from: 0
lastaccess: 0
ipmi_authtype: -1
ipmi_privilege: 2
ipmi_username:
ipmi_password:
ipmi_disable_until: 0
ipmi_available: 0
snmp_disable_until: 0
snmp_available: 1
maintenanceid: NULL
maintenance_status: 0
maintenance_type: 0
maintenance_from: 0
ipmi_errors_from: 0
snmp_errors_from: 0
ipmi_error:
snmp_error:
jmx_disable_until: 0
jmx_available: 0
jmx_errors_from: 0
jmx_error:
name: CARLOS_test_100.25
flags: 0
templateid: NULL
description:
tls_connect: 1
tls_accept: 1
tls_issuer:
tls_subject:
tls_psk_identity:
tls_psk:
proxy_address:
auto_compress: 1
4. 查看items数据表
MariaDB [zabbix]> show create table items G;
*************************** 1. row ***************************
Table: items
Create Table: CREATE TABLE `items` (
`itemid` bigint(20) unsigned NOT NULL,
`type` int(11) NOT NULL DEFAULT '0',
`snmp_community` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`snmp_oid` varchar(512) COLLATE utf8_bin NOT NULL DEFAULT '',
`hostid` bigint(20) unsigned NOT NULL,
`name` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`key_` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`delay` varchar(1024) COLLATE utf8_bin NOT NULL DEFAULT '0',
`history` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '90d',
`trends` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '365d',
`status` int(11) NOT NULL DEFAULT '0',
`value_type` int(11) NOT NULL DEFAULT '0',
`trapper_hosts` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`units` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`snmpv3_securityname` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`snmpv3_securitylevel` int(11) NOT NULL DEFAULT '0',
`snmpv3_authpassphrase` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`snmpv3_privpassphrase` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`formula` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`error` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
`lastlogsize` bigint(20) unsigned NOT NULL DEFAULT '0',
`logtimefmt` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`templateid` bigint(20) unsigned DEFAULT NULL,
`valuemapid` bigint(20) unsigned DEFAULT NULL,
`params` text COLLATE utf8_bin NOT NULL,
`ipmi_sensor` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '',
`authtype` int(11) NOT NULL DEFAULT '0',
`username` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`password` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`publickey` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`privatekey` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`mtime` int(11) NOT NULL DEFAULT '0',
`flags` int(11) NOT NULL DEFAULT '0',
`interfaceid` bigint(20) unsigned DEFAULT NULL,
`port` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`description` text COLLATE utf8_bin NOT NULL,
`inventory_link` int(11) NOT NULL DEFAULT '0',
`lifetime` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '30d',
`snmpv3_authprotocol` int(11) NOT NULL DEFAULT '0',
`snmpv3_privprotocol` int(11) NOT NULL DEFAULT '0',
`state` int(11) NOT NULL DEFAULT '0',
`snmpv3_contextname` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`evaltype` int(11) NOT NULL DEFAULT '0',
`jmx_endpoint` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`master_itemid` bigint(20) unsigned DEFAULT NULL,
`timeout` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '3s',
`url` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
`query_fields` varchar(2048) COLLATE utf8_bin NOT NULL DEFAULT '',
`posts` text COLLATE utf8_bin NOT NULL,
`status_codes` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '200',
`follow_redirects` int(11) NOT NULL DEFAULT '1',
`post_type` int(11) NOT NULL DEFAULT '0',
`http_proxy` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`headers` text COLLATE utf8_bin NOT NULL,
`retrieve_mode` int(11) NOT NULL DEFAULT '0',
`request_method` int(11) NOT NULL DEFAULT '0',
`output_format` int(11) NOT NULL DEFAULT '0',
`ssl_cert_file` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`ssl_key_file` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`ssl_key_password` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`verify_peer` int(11) NOT NULL DEFAULT '0',
`verify_host` int(11) NOT NULL DEFAULT '0',
`allow_traps` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`itemid`),
UNIQUE KEY `items_1` (`hostid`,`key_`),
KEY `items_3` (`status`),
KEY `items_4` (`templateid`),
KEY `items_5` (`valuemapid`),
KEY `items_6` (`interfaceid`),
KEY `items_7` (`master_itemid`),
CONSTRAINT `c_items_5` FOREIGN KEY (`master_itemid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE,
CONSTRAINT `c_items_1` FOREIGN KEY (`hostid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE,
CONSTRAINT `c_items_2` FOREIGN KEY (`templateid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE,
CONSTRAINT `c_items_3` FOREIGN KEY (`valuemapid`) REFERENCES `valuemaps` (`valuemapid`),
CONSTRAINT `c_items_4` FOREIGN KEY (`interfaceid`) REFERENCES `interface` (`interfaceid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
查看当前zabbix系统items数量：
select count(*) from interface a inner join hosts b on a.hostid=b.hostid inner join items c on c.hostid=a.hostid where a.hostid=10263 G;
查看当前zabbix系统单台主机key值与cpu相关：
select c.key_,b.hostid,a.ip from interface a inner join hosts b on a.hostid=b.hostid inner join items c on c.hostid=a.hostid where a.hostid= 10263 and c.key_ like "%cpu%" G;
查看一下与主机相关联的模板数：
show tables like "%host%";
select * from hosts_templates;
以上显示主机hostid=10263一共关联6个模板，在zabbix页面确认一下：
5. 查看目前zabbix系统history表：
select * from history
MariaDB [zabbix]> select * from items where itemid=28565 and value_type=0 G;
*************************** 1. row ***************************
itemid: 28565
type: 3
snmp_community:
snmp_oid:
hostid: 10263
name: ICMP response time
key_: icmppingsec
delay: 60s
history: 90d
trends: 365d
status: 0
value_type: 0
trapper_hosts:
units:
snmpv3_securityname:
snmpv3_securitylevel: 0
snmpv3_authpassphrase:
snmpv3_privpassphrase:
formula:
error:
lastlogsize: 0
logtimefmt:
templateid: 28511
valuemapid: NULL
params:
ipmi_sensor:
authtype: 0
username:
password:
publickey:
privatekey:
mtime: 0
flags: 0
interfaceid: 3
port:
description:
inventory_link: 0
lifetime: 30d
snmpv3_authprotocol: 0
snmpv3_privprotocol: 0
state: 0
snmpv3_contextname:
evaltype: 0
jmx_endpoint:
master_itemid: NULL
timeout: 3s
url:
query_fields:
posts:
status_codes: 200
follow_redirects: 1
post_type: 0
http_proxy:
headers:
retrieve_mode: 0
request_method: 0
output_format: 0
ssl_cert_file:
ssl_key_file:
ssl_key_password:
verify_peer: 0
verify_host: 0
allow_traps: 0
6. 查看目前zabbix系统triggers表：
.....