node-db4bix
Nodejs-rewritten DB monitoring plugin for Zabbix.
It is evolution of DBforBIX by SmartMarmot and DBforBIX by Vagabondan that were written in Java earlier.
Features
- Configuration is through Zabbix Web Interface
- Zabbix Preprocessing support!
- Whole table of items with only one SELECT: your databases remain in rest
- Connection pooling control: no reconnection DDoS from monitoring
- Several Zabbix Server instances support at a time
- Zabbix templates for every supported DB type
- Resolves Zabbix Host/Templates macros (i.e.
{$DSN}, {$ANY_OTHER_STUFF}, etc.) - Compatible with Zabbix 4.2.4++
Supported DB types
- MySQL
- PostgreSQL
- Oracle
- MSSQL
We are planning to extend it to other DBs...
You can easily extend this list, see ./libs/dbs/db-plugins
Documentation
- node-db4bix
Installation and run
With Node.js
- Download this repo:
git clone https://github.com/vagabondan/node-db4bix.git - Go to downloaded repo dir and install node packages:
cd node-db4bix && npm install - Create appropriate db4bix.conf configuration file inside ./config subdirectory:
cp ./config/db4bix_sample.conf ./config/db4bix.conf && vi ./config/db4bix.conf - Run:
or with full debug messages:node index.jsDEBUG='*' node index.js
With docker-compose
- Create or download simple docker-compose.yml directly:
or just clone the whole repo and go inside created folder:wget https://raw.githubusercontent.com/vagabondan/node-db4bix/master/docker-compose.ymlgit clone https://github.com/vagabondan/node-db4bix.git && cd node-db4bix - Create appropriate db4bix.conf configuration file inside ./config subdirectory, see example:
mkdir ./config && vi ./config/db4bix.conf - Run:
docker-compose up -d
You can choose appropriate debug level under environment section in docker-compose.yml by setting DEBUG variable. By default, it is set to info level.
With docker
- Create appropriate db4bix.conf configuration file inside /path/to/config directory, see config file example:
vi /path/to/config/db4bix.conf - Run docker:
or with full DEBUG messages:docker run -d -v /path/to/config:/app/config vagabondan/db4bixdocker run -d -v /path/to/config:/app/config -e 'DEBUG=*' vagabondan/db4bix
Logs
How to read logs with docker-compose
Read logs with command (from directory with docker-compose.yaml file):
docker-compose logs -f db4bix
How to read logs with docker
Do it with container id:
docker logs -f <container_id>
you can get container id with command:
docker ps|grep db4bix
this should read something like this:
fd4c14dba6f6 vagabondan/db4bix "docker-entrypoint.s…" 5 seconds ago Up 3 seconds silly_mclean
where the first hexademical identifier fd4c14dba6f6 is the container id.
Choose log verbosity
Log verbosity is managed with variable DEBUG. It is set differently depending of what type of db4bix run you have chosen:
- Nodejs: right in the command line of terminal where you start db4bix:
DEBUG='*' node index.js - docker-compose: inside docker-compose.yml file under environment section of db4bix service.
- docker: as the parameter to docker run command right after -e option:
docker run -d -v /path/to/config:/app/config -e 'DEBUG=*' vagabondan/db4bix
Below are some examples of DEBUG variable with short explanations:
- Show all messages from all modules (not only db4bix):
DEBUG='*' - Show debug messages from only db4bix modules:
DEBUG='db4bix:*' - Show only info messages from only db4bix modules:
DEBUG='db4bix:*:info' - Show all messages from ZabbixSender module of db4bix:
DEBUG='db4bix:ZabbixSender:*' - Show only info messages from ZabbixSender module of db4bix:
DEBUG='db4bix:ZabbixSender:info'
Configuration
DB4bix configuration is divided in two main parts described below:
Configuration file db4bix.conf
We use ini-file format and syntax inside db4bix.conf which is described in details here.
Configuration file keeps the following parameters listed in the table below:
| Section | Parameter | Example | Description |
|---|---|---|---|
| Global | updateConfigPeriod | updateConfigPeriod=30 |
Time interval in seconds between consequent updating configuration from Zabbix Servers. |
| [Zabbix.Name] | [Zabbix.Prod] |
Section name for Zabbix server instance connection parameters. You can specify any number of different Zabbix Servers and they will be served by DB4bix independently and simultaneously. | |
| [DB.Name] | [DB.BillingProd] |
Section name for Database instance connection parameters. You can specify any number of different databases. | |
| [Pool.Name] | [Pool.Common] |
Section name for DB connection Pool configuration parameters. You can specify any number of different Pools. Pool names are then referenced in DB sections in pool parameters. Pools manage network connections from DB4bix to DB instances. | |
| [Zabbix.Name] section | |||
| [Zabbix.Name] | host | host=zbxsrv01.yourdomain |
FQDN or IP address of Zabbix server instance, described in current Zabbix section |
| port | port=10051 |
Zabbix Server port | |
| proxyName | proxyName=DB4bix.01 |
Name of Zabbix Proxy that should be defined at current Zabbix Server instance to allow DB4bix communicate with Zabbix Server. Proxy mode should be set to Active. | |
| timeoutMillis | timeoutMillis=10000 |
Network timeout for Db4bix to wait response from Zabbix Server | |
| sendDataPeriod | sendDataPeriod=61 |
Time interval between consequent data sending actions to Zabbix Server. DB4bix keeps metrics from databases in local buffer and send bulk requests to Zabbix Server trappers with frequency configured with this parameter. | |
| configSuffix | configSuffix=DB4bix.config |
Zabbix Server item keys suffix where users expect to define DB4bix configuration on Zabbix Server Frontend side. This configuration should define SQL selects with some metadata for DB4bix to understand where it should put the resuts. See below section Zabbix Server configuration items for details. | |
| version | version=4.2.4 |
Zabbix Server version for DB4bix to choose the right Zabbix internal protocol to communicate with Zabbix Server. For now only 4.2.4 and higher versions are supported. We haven't tested it with lower versions yet. | |
| dbs[] | dbs[] = DB01 |
List of databases names allowed to monitor with current Zabbix Server instance. Syntax expects user to add to list one DB per row, so you might have to define several rows with dbs[] inside one Zabbix Server section | [DB.Name] section |
| [DB.Name] | type | type=mssql |
Defines which client driver will be used to establish connections to DB instance. There are slight differences in parameters for different DB types. |
| instance | instance=BillingDB |
DB instance name or SID for Oracle DB type. You should know that information from database administrators. | |
| host | host=billingdb.yourdomain |
FQDN or IP address of a host of your DBMS. | |
| port | port=5432 |
Port for connection establish with your DBMS. If null or empty, default will be used for this type of DB. | |
| user | user=thom |
Username/schemaname for your DB. | |
| password | password=your.supersecret.password |
Password for user for your DB. Yes, keep open passwords in plain text is not very good idea. We will be glad to see more reasonable proposals or even PRs ;-) | |
| pool | pool=Common |
Pool name which settings will be used to manage network connections from Db4bix to current DB. | |
| connectString | connectString= |
[ORACLE only]: Connection string used to connect to Oracle DBs. Possible formats are described here. | [Pool.Name] section |
| [Pool.Name] | max | max=10 |
Maximum number of concurrent connections that DB4bix can establish to each DB referencing this Pool. |
| min | min=0 |
Minimum number of concurrent connections that DB4bix will keep open even if no activity with DB is planning. | |
| idleTimeoutMillis | idleTimeoutMillis=30000 |
Timeout in millisecconds after which DB4bix starts to terminate unused connections. Specific behaviour is defined by DB client library. | |
| connectionTimeoutMillis | connectionTimeoutMillis=30000 |
Timeout in millisecconds after which DB4bix reports unsuccessful connection attempt to DB. Specific behaviour is defined by DB client library. | |
| keepAliveSec | keepAliveSec=60 |
Period in seconds for sending keepalive request from DB4bix to DB. Some databases requires keepalive checks from clients. | |
Zabbix Server configuration items
Being connected to Zabbix Server DB4bix requests all configuration for its Zabbix Proxy name (it performs this action periodically according to parameter updateConfigPeriod defined at Global section of local DB4bix config file). It scans enabled hosts items for item keys ending with DB4bix.config (or what you have defined in configSuffix parameter under Zabbix section in local db4bix config file). You can specify any other config suffix in local DB4bix config at will, but important is to keep in mind that this suffix should uniquely identify items where DB4bix will search its configuration.
This Zabbix item keys (we will name it configuration item key) should also have 2 parameters and look like this:
.*DB4bix.config[someid,DBName]
where someid - some your identifier (whatever you want), DBName - DB name which should be present in local configuration file in dbs[] parameter under Zabbix section. It also may be a macro (we often use {$DSN} macro in our templates), then its value should be defined in host or template macros. Db4bix will resolve it prior to look for corresponding DB name at local config file.
Configuration items, i.e. those having item keys with .*DB4bix.config suffix should be of type Database monitor and contain DB4bix-like XML configuration in SQL query field, e.g.:
- MySQL example where simple request returns result to single item key mysql.DB4bix.config[version,MySQL01]:
<parms type="mysql" prefix="mysql."> <server> <query time="600" item="DB4bix.config[version,MySQL01]">SHOW VARIABLES LIKE "version"</query> </server> </parms>
- Another example for Oracle DB of Zabbix discovery item with item key:
oracle.discovery.DB4bix.config[instanceid,{$DSN]
It returns several results in one bulk request and has Zabbix host macro {$DSN} which value is defined at host macros with DB name of one of the databases defined at local DB4bix config file:
<parms type="oracle" prefix="oracle."> <server> <discovery time="120" item="discovery.DB4bix.config[instanceid,{$DSN}]" names="INST_ID">select inst_id from gv$instance</discovery> <query time="60" item="stats[%1,%2]">SELECT inst_id, REPLACE(name,' ','_'), value FROM gv$sysstat WHERE name IN ('user I/O wait time','physical read total bytes','physical write total bytes','lob reads','lob writes','db block changes','db block gets','consistent gets','physical reads')</query> </server> </parms>
- Many other examples you can find at Zabbix templates provided with this repo in ./templates subdirectory.
You can place in SQL field of DBforBix config item full XML config with root element - parms. You can make it as complex as you wish. DB4bix creates as many async code flows as needed to serve all config items and avoid mutual influence of different config items.
Final step you should do is to create items-receivers of data. We recommend to use type trappers for them but this is not necessary.
For second example above you should create item prototypes with keys, e.g.:
oracle.stats[{#INST_ID},user_I/O_wait_time] oracle.stats[{#INST_ID}physical_read_total_bytes] ... oracle.stats[{#INST_ID},physical_write_requests_optimized]
XML syntax of Zabbix configuration items
Two examples of XML syntax you've already seen earlier. Below is the table with all possible elements and attributes of XML configuration items.
| Element | Attribute | Examples | Description |
|---|---|---|---|
| parms | type |
<parms type="mysql" prefix="mysql.">...</parms> <parms prefix="mysql.">...</parms> <parms prefix="oracle.">...</parms> <parms prefix="whateveryouwant.">...</parms> <parms prefix="">...</parms> |
(Deprecated) Used in previous versions of DB4bix. Left for backward compatibility only and can be omitted. |
| prefix | You can define prefix which will be used as prefix for item keys where Db4bix will return results to. Or you can leave it empty as in last example. | ||
| server | type |
<parms prefix=""> <server> ... </server> </parms> |
Used in previous versions of DB4bix. Left for backward compatibility only. Should be present. |
| query or multiquery |
time |
<parms prefix="mysql."><server> |
Time interval in seconds between consequent requests to DB with this query. |
| item or items |
item or items - are absolutely identical synonyms of attribute that holds Zabbix Item key or list of item keys separated with "|" (vertical line) where DB4bix should return results to.
Final Zabbix item key will be constructed using prefix attribute value from parms element. So for attribute item with value "one|two|three" DB4bix will be looking for Zabbix items with keys mysql.one, mysql.two and mysql.three to return values to them. The mentioned above item keys should be presented on the same Zabbix Host where current configuration item resides.
There is some extention to this syntax: one can use placeholders (%1, %2, %3, ...) to construct item keys from data returned by query, e.g. item="somekey[%1,%2]" - means that DB4bix should construct item keys getting values from first column for %1 placeholder and from second column for %2 placeholder from query resut and use last column as the value for this item keys. If a query returns multiple rows then DB4bix constructs item keys by substituting placeholders per each row and as many times as many rows returned by the query. Most efficient queries come up when one combines placeholders and list syntax, e.g. item="somekey1[%1,%2]|somekey2[%1,%2]|somekey3[%1,%2]|somekey4[%1,%2]" - this means that query may return any number of rows with 2 (placeholders) + 4 (values for 4 keys) = 7 columns per each row. Suppose query returns 100 rows, so one can get values for 4 (keys) * 100 (rows) = 400 (items) with only one request to database! The whole tables are inserted to Zabbix per single query to database! |
||
| type | column (default) or list - parse type of returning results when several item values from returned from single query/multiquery. Type column means that item values are returned in columns and they will be matched to their keys from the last column to the first column, i.e. last column corresponds to the last item key in item attribute, previous column corresponds to the previous item key and so on. If the number of columns is less than the number of item keys, the remaining item keys are ignored and vice versa. Type list means, that query returns multiple rows with two columns where the first column corresponds to the key and the second column corresponds to the value, i.e. query returns list of key-value pairs. So query element in this case behaves like filter: only keys that matches to item keys in item attribute will be returned. |
||
| nodata | (Optional) What DB4bix should return if no data will be returned by query. | ||
| discovery | names |
<parms prefix="oracle."><server> <discovery |
Discovery elements behaves almost same as query but with major difference: it should return to Zabbix Server JSON LLD (Low Level Discovery) structure. This structure contains information about all possibles values of so called [Zabbix LLD Macros](https://www.zabbix.com/documentation/4.4/manual/config/macros/lld_macros). DB4bix constructs this structures for you, but you should identify list of such LLD Macros in attribute names.
You can use "|" (vertical line) symbol to construct list of LLD macros but remember that the number of columns returned by the query should correspond to the number of LLD macros. Number of rows is not limited but each row should contain unique combination of macros values or Zabbix will report "duplication" error to you.
Another constraint: LLD macros names should be in upper case and may contain only letters and underscore symbols. Please note, it is very convenient to use discovery items as configuration items for DB4bix! Do not forget to choose Zabbix item type as "Database monitor", because only this type contains "params" field in its userform, where you can type and edit DB4bix configuration. |
| item | Should contain item key for discovery item. As you can see from example you are allowed to use [Zabbix User Macros](https://www.zabbix.com/documentation/4.4/manual/config/macros/usermacros) in item keys. They will be substituted by DB4bix prior to return values to Zabbix. | ||
| time | Same as for query elements, see above. |
How it works alltogether
Local file config (db4bix.conf) defines which Zabbix Servers DB4bix may connect to. Also it defines how DB4bix may connect to databases which it can monitor.
DB4bix connects to Zabbix Server like an ordinary Zabbix Proxy. Of course for it may happen, corresponding Zabbix Proxy name should be defined at Zabbix Server and DB4bix should know this name too. This is the reason why you have to define proxyName parameter inside [Zabbix.Name] section of db4bix.conf.
DB4bix requests configuration data and Zabbix Server responds with information about all hosts attached to DB4bix proxy, items, templates, macros etc.
It is rather convenient and reasonable to create its own separate Zabbix Host to each DB instance, though nothing prevents you to keep configuration items of several DBs on one Zabbix Host. It is only our recommendations to separate them.
After configuration is received, DB4bix scans all items of all hosts for configSuffix and reads configurations from matching items. Items type should be Database monitor because only such item type has params field in its user web form that user can edit and read.
While reading configuration items DB4bix:
- groups all queries by Database instances (second parameter in configuration item key) and time attributes (time interval between two consequent queries).
- substitutes Zabbix macros with its values in item keys and queries
- updates or creates periodical async monitors to query databases
Each monitor queries its database when time comes and saves results to local DB4bix storage mapping them to concrete Zabbix item ids.
Separate async periodic procedure sends all storage data with bulk request to Zabbix Servers according to sendDataPeriod parameters in Zabbix sections in local DB4bix config file.
Then everything repeats.