Shulou(Shulou.com)05/31 Report--

What is the ZABBIX database table? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

The importance of zabbix database table structure

If you want to understand the front-end code of zabbix, do in-depth secondary development, and even tuning, you have to understand the table structure of the database.

We use zabbix3.4 and mysql here, so simply talk about the table structure on our mysql side, other environments are not guaranteed to be correct.

Mysql > show tables +-- + | Tables_in_zabbix | +-- + | acknowledges | | actions | | alerts | | application_template | | applications | | auditlog | | auditlog_details | | autoreg_host | | conditions | Config | | dbversion | | dchecks | | dhosts | | drules | | dservices | | escalations | | events | | expressions | | functions | | globalmacro | | globalvars | | graph_discovery | | graph_theme | | graphs | | graphs_items | | group_discovery | | group_prototype | | groups | | history | | history_log | | history_str | | history_text | | history_uint | host_discovery | | host_inventory | | hostmacro | | hosts | | hosts_groups | | hosts_templates | | | housekeeper | | httpstep | | httpstepitem | | httptest | | httptestitem | | icon_map | | icon_mapping | | ids | images | | interface | | interface_discovery | | item_condition | | item_discovery | | items | | items_applications | | maintenances | | | maintenances_groups | | maintenances_hosts | | maintenances_windows | | mappings | | media | | media_type | | opcommand | | opcommand_grp | opcommand_hst | | opconditions | | operations | | opgroup | | opmessage | | opmessage_grp | | opmessage_usr | | optemplate | | | profiles | | proxy_autoreg_host | | proxy_dhistory | | proxy_history | | regexps | | rights | | screens | | screens_items | | scripts | service_alarms | | services | | services_links | | services_times | | sessions | | slides | | | slideshows | | sysmap_element_url | | sysmap_url | | sysmaps | | sysmaps_elements | | sysmaps_link_triggers | | sysmaps_links | | timeperiods | trends | | trends_uint | | trigger_depends | | trigger_discovery | | triggers | | user_history | | users | | users_ | Groups | | usrgrp | | valuemaps | +-+ actions

The actions table records the actions that need to be taken when the trigger is triggered.

Mysql > desc actions +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | actionid | bigint (20) unsigned | NO | PRI | 0 | | name | varchar (255) | NO | | eventsource | int (11) | NO | MUL | 0 | | | evaltype | int (11) | NO | | 0 | | status | int (11) | NO | | 0 | | esc_period | int (11) | NO | | 0 | | def_shortdata | varchar (255) | NO | | | def_longdata | blob | NO | | NULL | | recovery_msg | int (11) | NO | | 0 | | r_shortdata | varchar | NO | | r_longdata | blob | NO | | NULL | | +-| -+-+ alerts

The alerts table stores historical alarm events, from which you can do some statistical analysis, such as a department,

Alarm statistics for someone, a certain type of time, and more in-depth failure occurrence and recovery time depends on how you want to use it.

Mysql > desc alerts +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | alertid | bigint (20) unsigned | NO | PRI | 0 | | actionid | bigint (20) unsigned | NO | MUL | 0 | | eventid | bigint (20) unsigned | NO | MUL | 0 | | userid | bigint (20) unsigned | NO | | MUL | 0 | | clock | int (11) | NO | PRI | 0 | | mediatypeid | bigint (20) unsigned | NO | MUL | 0 | | sendto | varchar | NO | subject | varchar | NO | Message | blob | NO | | NULL | | status | int (11) | NO | MUL | 0 | | retries | int (11) | NO | | 0 | | error | varchar | NO | | nextcheck | int (11) ) | NO | | 0 | | esc_step | int (11) | NO | | 0 | | alerttype | int (11) | NO | | 0 | | +- -+ config

The config table stores global parameters, including the back end. In many cases, the parameters of the modified table will be queried, such as the user's custom theme,

Login authentication type, etc., is very important

But it doesn't make much sense to analyze the data for us.

Mysql > desc config +-+ | Field | Type | Null | Key | Default | Extra | + -+-+ | configid | bigint (20) unsigned | NO | PRI | 0 | | Alert_history | int (11) | NO | | 0 | | event_history | int (11) | NO | | 0 | | refresh_unsupported | int (11) | NO | | 0 | work_period | | varchar | NO | | 1-5 alert_usrgrpid | bigint (20) unsigned | NO | | 0 | | event_ack_enable | int (11) | NO | | 1 | | event_expire | int (11) | | | NO | | 7 | | event_show_max | int (11) | NO | | 100 | | default_theme | varchar | NO | | default.css | | authentication_type | int (11) | NO | 0 | ldap_host | varchar | NO | | ldap_port | int (11) | NO | | 389 | | ldap_base_dn | varchar (255) | NO | | ldap_bind_dn | varchar | NO | | ldap_bind_password | varchar | NO | | ldap_search_attribute | varchar | NO | | | dropdown_first_entry | int (11) | NO | | 1 | dropdown_first_remember | int (11) | NO | | 1 | | discovery_groupid | bigint (20) unsigned | NO | | 0 | | max_in_table | int (11) | ) | NO | | 50 | search_limit | int (11) | NO | | 1000 | | +-- -+ functions

The function table is a very important table, recording the expressions used in trigger, such as max, last, nodata, and so on.

But in fact, this table says that it is important because it records trigger and itemid at the same time, so it can do some API development, for example, according to

IP tea changed all the trigger of IP. I remember that the 1.8 version of API can not achieve the function I mentioned, so we can only use

The function table has been checked by myself.

Mysql > desc functions +-+ | Field | Type | Null | Key | Default | Extra | +-+- -- + | functionid | bigint (20) unsigned | NO | PRI | 0 | itemid | bigint (20) unsigned | NO | MUL | 0 | | triggerid | bigint (20) unsigned | NO | MUL | 0 | | lastvalue | varchar (255) | YES | NULL | | function | varchar (12) | NO | | parameter | varchar (255) | NO | | 0 | | +-+-+ graphs |

The graphs table contains user-defined chart information. The same method can be used to query and change all charts under IP according to IP.

But there seems to be an API, I'm just an example.

Mysql > desc graphs +-+-+ | Field | Type | Null | Key | Default | Extra | + -+ | graphid | bigint (20) unsigned | NO | PRI | 0 | | name | varchar | NO | MUL | | width | int (11) | | NO | | 0 | | height | int (11) | NO | | 0 | | yaxismin | double (16Power4) | NO | | 0.0000 | | yaxismax | double (16Magin4) | NO | | 0.0000 | | templateid | bigint (20) unsigned | | | NO | | 0 | | show_work_period | int (11) | NO | | 1 | | show_triggers | int (11) | NO | | 1 | | graphtype | int (11) | NO | | 0 | | show_legend | int (11) | | | NO | | 0 | | show_3d | int (11) | NO | | 0 | | percent_left | double (16Power4) | NO | | 0.0000 | | percent_right | double (16Pione4) | NO | | 0.0000 | | ymin_type | Int (11) | NO | | 0 | | ymax_type | int (11) | NO | | 0 | | ymin_itemid | bigint (20) unsigned | NO | | 0 | | ymax_itemid | bigint (20) unsigned | NO | | 0 | | +- -+-+ graphs_items

Graphs_items holds all the monitoring item information that belongs to a chart.

Mysql > desc graphs_items +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | gitemid | bigint (20) unsigned | NO | PRI | 0 | | graphid | bigint (20) unsigned | NO | MUL | 0 | | itemid | bigint (20) unsigned | NO | MUL | 0 | | drawtype | int (11) | | NO | | 0 | | sortorder | int (11) | NO | | 0 | | color | varchar (6) | NO | | 009600 | | yaxisside | int (11) | NO | | 1 | | calc_fnc | int (11) | NO | | | 2 | | type | int (11) | NO | | 0 | | periods_cnt | int (11) | NO | | 5 | | +-- | -+ groups

Groups has nothing to say, just save the group name and the ID of the group.

Mysql > desc groups +-+ | Field | Type | Null | Key | Default | Extra | +-- -+ | groupid | bigint (20) unsigned | NO | PRI | 0 | name | varchar (64) | NO | MUL | | internal | int (11) | NO | | 0 | | +-+-- -+ history, History_str, history_log, history_uint_sync, etc.

This part of the table is similar, the only difference is the data type saved, the data saved by history_str

Type is str, that is, character type. This is consistent with the data type set at the time of collection.

When you need to pay attention, because there are so many types of history tables, write your own report system to query.

Data, you need to determine the type of data collection, if you check the wrong table, then there must be no data.

Mysql > desc history +-+ | Field | Type | Null | Key | Default | Extra | +-+ -+ | itemid | bigint (20) unsigned | NO | PRI | 0 | clock | int (11) | NO | PRI | 0 | | value | double (1610) | NO | | 0.0000 | | +-+- -+ mysql > desc history_str +-+ | Field | Type | Null | Key | Default | Extra | +-+ -+ | itemid | bigint (20) unsigned | NO | MUL | 0 | clock | int (11) | NO | | 0 | | value | varchar (255) | NO | +-+- -+

The time value when receiving the item value is stored in two fields, the clock field unit for the part greater than 1 second is in seconds (s), and the part less than one second is stored in the ns field unit in nanosecond (ns).

The value added by the two fields is the time value when the item value is received. Generally, you don't have to care about the part that is less than 1 second.

Trends 、 trends_uint

Trends is also used to save historical data, unlike history, the trends table is only saved

The hourly average value, which you can understand as the data compression of the history table. So the trends table also has

There are many types, corresponding to history.

Note that the trends and history tables have a very large amount of data, and we probably have them in a day.

40g of data.

So pay attention to do compression, delete.

Mysql > desc trends +-+ | Field | Type | Null | Key | Default | Extra | +-+- +-+ | itemid | bigint (20) unsigned | NO | PRI | 0 | | clock | int (11) | NO | PRI | 0 | | num | int (11) | NO | | 0 | | value_min | double | NO | | 0.0000 | value_avg | double (16Power4) | NO | | 0.0000 | | value_max | double (16recover4) | NO | | 0.0000 | | +-- -- + mysql > desc trends_uint +-+ | Field | Type | Null | Key | Default | Extra | +-+- +-+ | itemid | bigint (20) unsigned | NO | PRI | 0 | | clock | int (11) | NO | PRI | 0 | | num | int (11) | NO | | 0 | | value_min | bigint (20) unsigned | NO | | 0 | | value_avg | bigint (20) unsigned | NO | | 0 | | value_max | bigint (20) unsigned | NO | | 0 | | +-- -+-+ hosts

Hosts is very important, saving IP, hostid, status, IPMI and other information of each agent, proxy, etc.

Almost all the information about a device is recorded.

Of course, hostid is a very important piece of information, and other tables are usually associated with hostid.

Mysql > desc hosts +-+ | Field | Type | Null | Key | Default | Extra | +- -+-+ | hostid | bigint (20) unsigned | NO | PRI | 0 | proxy_hostid | bigint (20) unsigned | NO | MUL | 0 | | host | | varchar (64) | NO | MUL | | dns | varchar (64) | NO | | useip | int (11) | NO | | 1 | | ip | varchar (39) | NO | | | 127.0.0.1 | port | int (11) | NO | | 10050 | | status | int (11) | NO | MUL | 0 | | disable_until | int (11) | NO | | 0 | error | | | varchar | NO | | available | int (11) | NO | | 0 | | errors_from | int (11) | NO | | 0 | | lastaccess | int (11) | NO | | 0 | | inbytes | bigint (20) unsigned | NO | | 0 | | outbytes | bigint (20) unsigned | NO | | 0 | | useipmi | int (11) | NO | | 0 | | ipmi_port | int (11) | NO | | ipmi_authtype | int (11) | NO | | 0 | | ipmi_privilege | int (11) | NO | | 2 | | ipmi_username | varchar (16) | NO | | ipmi_password | varchar ( 20) | NO | | ipmi_disable_until | int (11) | NO | | 0 | | ipmi_available | int (11) | NO | | 0 | | snmp_disable_until | int (11) | NO | | 0 | | | snmp_available | int (11) | NO | | 0 | | maintenanceid | bigint (20) unsigned | NO | | 0 | | maintenance_status | int (11) | NO | | 0 | | maintenance_type | int (11) | NO | 0 | | | | maintenance_from | int (11) | NO | | 0 | ipmi_ip | varchar (64) | NO | | 127.0.0.1 | ipmi_errors_from | int (11) | NO | | 0 | | snmp_errors_from | int (11) | | | NO | | 0 | | ipmi_error | varchar | NO | | snmp_error | varchar | NO | +-+-| -+

In fact, in version 1.0, there are not so many fields. It seems that there are only hostid, host, status and disable_until.

Wait a few fields, but 1.8 is already so rich.

Hosts_groups

Hosts_groups preserves the relationship between host (host) and host groups (host group).

We can make some batch queries about this information, such as querying all the information associated with a host group.

IP, survival status of the equipment, etc., to further query the batch of equipment load, IO, mem and other statistical information.

A simple report I made before is, for example, this part of the information to query all the devices offline in a certain business.

The weekly statistics, of course, are in the same host group or template group.

Mysql > desc hosts_groups +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | hostgroupid | bigint (20) unsigned | NO | PRI | 0 | | hostid | bigint (20) unsigned | NO | MUL | 0 | | groupid | bigint (20) unsigned | NO | MUL | 0 | | +- +-+ items

The items table holds the information about the collected items.

Mysql > desc items +-- +-+ | Field | Type | Null | Key | Default | Extra | +- -+-+ | itemid | bigint (20) unsigned | NO | PRI | 0 | | type | int (11) | NO | | 0 | snmp_community | varchar (64) | NO | | snmp_oid | varchar (255) | NO | | snmp_port | int (11) | NO | | 161 | | hostid | bigint (20) | Unsigned | NO | MUL | 0 | | description | varchar | NO | | key_ | varchar | NO | | delay | int (11) | NO | | 0 | history | | int (11) | NO | | 90 | | trends | int (11) | NO | | 365 | | lastvalue | varchar (255) | YES | | NULL | | lastclock | int (11) | YES | NULL | prevvalue | varchar | YES | | NULL | | status | int (11) | NO | MUL | 0 | | value_type | int (11) | NO | | 0 | | trapper_hosts | varchar (255) | | NO | | units | varchar (10) | NO | | multiplier | int (11) | NO | | 0 | | delta | int (11) | NO | | 0 | Prevorgvalue | varchar | YES | | NULL | | snmpv3_securityname | varchar (64) | NO | | snmpv3_securitylevel | int (11) | NO | | 0 | | snmpv3_authpassphrase | varchar (64) | NO | snmpv3_privpassphrase | varchar ( 64) | NO | | formula | varchar | NO | | 1 | | error | varchar | NO | | lastlogsize | int (11) | NO | | 0 | | | logtimefmt | varchar (64) | NO | | templateid | bigint (20) unsigned | NO | MUL | 0 | | valuemapid | bigint (20) unsigned | NO | | 0 | | delay_flex | varchar (255) | NO | | | params | text | NO | | NULL | | ipmi_sensor | varchar (128) | NO | | data_type | int (11) | NO | | 0 | | authtype | int (11) | NO | | 0 | | username | varchar (64) | NO | | password | varchar (64) | NO | | publickey | varchar (64) | NO | | privatekey | varchar ( 64) | NO | | mtime | int (11) | NO | | 0 | | + -+-+ media

Media saves a user's media configuration item, that is, the corresponding alarm method.

Mysql > desc media +-+-+ | Field | Type | Null | Key | Default | Extra | + -+ | mediaid | bigint (20) unsigned | NO | PRI | 0 | | userid | bigint (20) unsigned | NO | MUL | 0 | | mediatypeid | bigint (20) unsigned | | NO | MUL | 0 | sendto | varchar | NO | | active | int (11) | NO | | 0 | | severity | int (11) | NO | | 63 | | period | | | varchar | NO | | 1-7 recording 00-23:59 | | +-+-+ media_type |

The media_type table is different from the media table in that media_type records the storage path of the steps corresponding to an alarm method.

Mysql > desc media_type +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | mediatypeid | bigint (20) unsigned | NO | PRI | 0 | type | int (11) | NO | | 0 | | description | varchar (100) | NO | | smtp_server | Varchar | NO | | smtp_helo | varchar | NO | | smtp_email | varchar | NO | | exec_path | varchar | NO | | gsm_modem | varchar (255) | | NO | | username | varchar (255) | NO | | passwd | varchar (255) | NO | +-- -- +

Media and media_type are associated with the mediatypeid key.

Profiles

The profiles table holds some of the user's configuration items.

Mysql > desc profiles +-+ | Field | Type | Null | Key | Default | Extra | +-+- +-+ | profileid | bigint (20) unsigned | NO | PRI | 0 | | userid | bigint (20) unsigned | NO | MUL | 0 | | idx | varchar (96) | NO | | idx2 | bigint (20) unsigned | NO | | | 0 | | value_id | bigint (20) unsigned | NO | | 0 | | value_int | int (11) | NO | | 0 | | value_str | varchar (255) | NO | | source | varchar (96) | NO | | | type | int (11) | NO | | 0 | | +-+-+ rights |

The rights table stores the permission information of the user group, and the permission of zabbix has always been something I can't figure out.

In fact, there are detailed records in this table.

Mysql > desc rights +-+ | Field | Type | Null | Key | Default | Extra | +-+- -- + | rightid | bigint (20) unsigned | NO | PRI | 0 | groupid | bigint (20) unsigned | NO | MUL | 0 | | permission | int (11) | NO | | 0 | | id | bigint (20) unsigned | YES | MUL | NULL | | +-+-+ screens

The screens table holds user-defined pictures.

Mysql > desc graphs +-+-+ | Field | Type | Null | Key | Default | Extra | + -+ | graphid | bigint (20) unsigned | NO | PRI | 0 | | name | varchar | NO | MUL | | width | int (11) | | NO | | 0 | | height | int (11) | NO | | 0 | | yaxismin | double (16Power4) | NO | | 0.0000 | | yaxismax | double (16Magin4) | NO | | 0.0000 | | templateid | bigint (20) unsigned | | | NO | | 0 | | show_work_period | int (11) | NO | | 1 | | show_triggers | int (11) | NO | | 1 | | graphtype | int (11) | NO | | 0 | | show_legend | int (11) | | | NO | | 0 | | show_3d | int (11) | NO | | 0 | | percent_left | double (16Power4) | NO | | 0.0000 | | percent_right | double (16Pione4) | NO | | 0.0000 | | ymin_type | Int (11) | NO | | 0 | | ymax_type | int (11) | NO | | 0 | | ymin_itemid | bigint (20) unsigned | NO | | 0 | | ymax_itemid | bigint (20) unsigned | NO | | 0 | | +- -+-+ screens_items

Same as graphs_items.

Mysql > desc screens_items +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | screenitemid | bigint (20) unsigned | NO | PRI | 0 | | screenid | bigint (20) unsigned | NO | | 0 | | resourcetype | int (11) | NO | | 0 | | resourceid | bigint (20) ) unsigned | NO | | 0 | | width | int (11) | NO | | 320 | | height | int (11) | NO | | 200 | | x | int (11) | NO | | 0 | | y | int ( 11) | NO | | 0 | | colspan | int (11) | NO | | 0 | | rowspan | int (11) | NO | | 0 | | elements | int (11) | NO | | 25 | | valign | int ( 11) | NO | | 0 | | halign | int (11) | NO | | 0 | | style | int (11) | NO | | 0 | | url | varchar (255) | NO | | dynamic | Int (11) | NO | | 0 | | +-+-+ sessions

The sessions table is very important. It stores the sessions of each user and operates when logging in or logging out.

It's the list.

When doing unified authentication such as cas, you need to understand the form and the related login and verification process. If you are interested, look at me.

The previous article.

Mysql > desc sessions +-+ | Field | Type | Null | Key | Default | Extra | +-+- -- + | sessionid | varchar (32) | NO | PRI | | userid | bigint (20) unsigned | NO | MUL | 0 | | lastaccess | int (11) | NO | | 0 | | status | int (11) | | NO | | 0 | | +-- + triggers |

Triggers holds all the information about trigger as its name implies.

Mysql > desc triggers +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | triggerid | bigint (20) unsigned | NO | PRI | 0 | | expression | varchar | NO | | description | varchar (255) | NO | | url | varchar ( | NO | | status | int (11) | NO | MUL | 0 | | value | int (11) | NO | MUL | 0 | | priority | int (11) | NO | | 0 | | lastchange | int (11) | | NO | | 0 | | dep_level | int (11) | NO | | 0 | | comments | blob | NO | | NULL | | error | varchar | NO | | templateid | bigint (20) unsigned | NO | | 0 | | type | int (11) | NO | | 0 | | +-+-+ trigger_depends |

Trigger_depends preserves trigger dependencies.

Mysql > desc trigger_depends +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | triggerdepid | bigint (20) unsigned | NO | PRI | 0 | | triggerid_down | bigint (20) unsigned | NO | MUL | 0 | | triggerid_up | bigint (20) unsigned | NO | MUL | 0 | | + -+-+ users

There is no need to explain, some of the user configurations mentioned by the value will be in the table, such as auotlogin, autologout,

Url, theme and other information.

Mysql > desc users +-+ | Field | Type | Null | Key | Default | Extra | +-+-- -+ | userid | bigint (20) unsigned | NO | PRI | 0 | | alias | varchar | NO | MUL | | name | Varchar | NO | | surname | varchar (100) | NO | | passwd | char (32) | NO | | url | varchar (255) | NO | | | autologin | int (11) | NO | | 0 | | autologout | int (11) | NO | 900 | | lang | varchar (5) | NO | | en_gb | | refresh | int (11) | NO | | | 30 | type | int (11) | NO | | 0 | | theme | varchar | NO | | default.css | | attempt_failed | int (11) | NO | | 0 | | attempt_ip | varchar (39) | | | NO | | attempt_clock | int (11) | NO | | 0 | | rows_per_page | int (11) | NO | | 50 | | +-- | -+ answers to questions about what ZABBIX database tables are like are shared here. I hope the above content can help you to a certain extent, if you still have a lot of doubts to be solved, you can follow the industry information channel to learn more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.