ZCP_day_2_slides2_Part1.pdf

Y P E D ODBC monitoring U T S 6.0 Certified Professional ● Day 2 T N O C © 2023 by Zabbix. All rights reserved 20 minutes 4 Open Database Connectivity (ODBC) GET DATA FROM EXTERNAL DATABASE Y P Standard Programming Interface (API) for accessing database management systems This API is i...

Y P E D ODBC monitoring U T S 6.0 Certified Professional ● Day 2 T N O C © 2023 by Zabbix. All rights reserved 20 minutes 4 Open Database Connectivity (ODBC) GET DATA FROM EXTERNAL DATABASE Y P Standard Programming Interface (API) for accessing database management systems This API is independent from any of the DBMS, or an operating system O C Zabbix may query any database, which is supported by ODBC: T N A unixODBC database driver should be installed for the database, which will be monitored Zabbix documentation has recommended settings for MySQL, PostgreSQL and MS SQL Server E D Database monitor item type must be used U T The Timeout parameter is used as the ODBC login timeout S Zabbix does not limit the query execution time 6.0 Certified Professional ● Day 2 © 2023 by Zabbix. All rights reserved Theory 5 DATABASE MONITORING - DATA FLOW Database monitoring begins with Zabbix making a call to unixODBC Driver Manager Y P The Driver Manager makes a call to the ODBC Driver The ODBC Driver accesses the database over the network using the database API Zabbix Server E D U T S Remote database server 6.0 Certified Professional ● Day 2 T N O C UNIXODBC ODBC Driver MySQL ODBC Driver ORACLE ODBC Driver MSSQL Database API Database API Database API MySQL Database © 2023 by Zabbix. All rights reserved Oracle Database MSSQL Database Theory 6 ODBC POLLERS ODBC monitoring is performed by ODBC poller processes Y P At least one ODBC poller must be started if ODBC checks are done ## Option: StartODBCPollers # Number of pre-forked ODBC poller instances. # # Mandatory: no # Range: 0-1000 # Default: StartODBCPollers=5 T N O C E D ODBC polling loop will work as follows: Retrieve ODBC items for an ODBC poller U T Prepare them (expand macros) S Requeue ODBC items, calculate next checks Clean the ODBC item cache 6.0 Certified Professional ● Day 2 © 2023 by Zabbix. All rights reserved Theory 7 DATABASE MONITORING - CONFIGURATION 1. Installing unixODBC driver on Zabbix server Y P To install MySQL database driver on RedHat based systems run: # dnf install unixODBC Out-of-the-box drivers are available for: MySQL MariaDB PostgreSQL Free Sybase & MS SQL Driver T N O C U T E D Default ODBC drivers might not work with the most recent database versions: Install the ODBC driver that matches DB version S Multiple ODBC drivers for different DB versions can be installed 6.0 Certified Professional ● Day 2 © 2023 by Zabbix. All rights reserved Theory 8 DATABASE MONITORING - CONFIGURATION Configuring unixODBC: Y P ODBC configuration is done by editing the /etc/odbcinst.ini and /etc/odbc.ini files odbcinst.ini is used to list the installed ODBC drivers Database driver name [MySQL] Description = ODBC for MySQL Driver = /usr/lib/libmyodbc5.so T N O C odbc.ini is used to define data sources/databases (create, if necessary) Data Source Name (DSN) E D [mysql01] Description = mysql01.zabbix.com database U T Driver name (odbcinst.ini) S Driver = MySQL Server = 10.132.11.56 It is possible to specify many additional parameters; the list depends on the driver used Database, User, Password, Socket etc. 6.0 Certified Professional ● Day 2 © 2023 by Zabbix. All rights reserved Theory 9 DATABASE MONITORING - CONFIGURATION Verify ODBC connection (-v for debug): Y P # isql -v <DSN> <MySQL user> <Password> Example: # isql mysql01 zabbix zabbix +------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +------------------------------------+ SQL> E D U T S T N O C Test SQL queries: SQL> show tables 6.0 Certified Professional ● Day 2 © 2023 by Zabbix. All rights reserved Theory 10 DATABASE MONITORING - CONFIGURATION Add an item in Zabbix frontend: Y P Key: db.odbc.select[<unique short description>,<dsn>,<connection string>] Returns one value (the first column of the first row of the SQL query result) If a query returns more than one column, only the first column is read If a query returns more than one line, only the first line is read The query must return a value (empty result = not supported item) E D U T S 6.0 Certified Professional ● Day 2 T N © 2023 by Zabbix. All rights reserved O C DSN from odbc.ini Theory 11 DATABASE MONITORING - CONFIGURATION Key: db.odbc.get[<unique short description>,<dsn>,<connection string>] Y P Returns multiple rows/columns in JSON format May be used as a master item that collects all data in a single system call JSONPath preprocessing must be used in dependent items to extract individual values E D U T S T N O C Get all data in master item Extract data using preprocessing in dependent item 6.0 Certified Professional ● Day 2 © 2023 by Zabbix. All rights reserved Theory 12 DATABASE MONITORING - CONFIGURATION Alternatively, ODBC item keys can use a "connection string" instead of the DSN: db.odbc.select[<unique short description>,<dsn>,<connection string>] db.odbc.get[<unique short description>,<dsn>,<connection string>] Syntax: Y P O C "Driver=<DriverName or </path/to/driver.so>;Database=<DB NAME>;Server=<IP/DNS>;Port=3306" T N If fields "Username" and "Password" are set, then: E D string: "UID=<user>;PWD=<password>" will be added to the "connection string" automatically U T Examples: S "Driver=MariaDB;Database=zabbix;Server={HOST.CONN};Port=3306" "Driver=/usr/lib64/libmaodbc.so;Database=zabbix;Server={HOST.CONN};Port=3306" "Driver=MariaDB;Database=zabbix;Server={HOST.CONN};Port=3306;UID=<username>;PWD=<pass>" 6.0 Certified Professional ● Day 2 © 2023 by Zabbix. All rights reserved Theory 13 PRACTICAL SETUP Y P 1) Modify zbx_monitor database user: grant SELECT on zabbix.* to zbx_monitor@localhost; 2) Install "mariadb-connector-odbc" O C 3) Create the new template "Training ODBC monitoring" T N Item: Number of hosts in Zabbix Key: db.odbc.select[zabbixhosts,,"Driver=/usr/lib64/libmaodbc.so;Database=zabbix; Server={HOST.CONN};Port=3306"] SQL query: SELECT count(*) FROM zabbix.hosts WHERE status IN (0,1) AND flags IN (0,4) 4) Check the results U T E D S 6.0 Certified Professional ● Day 2 © 2023 by Zabbix. All rights reserved 20 minutes Practical task No: 9 14 Y P E DPreprocessing U T S 6.0 Certified Professional ● Day 2 T N O C © 2023 by Zabbix. All rights reserved 20 minutes 15 ITEM- PREPROCESSING Transformations are executed in the order in which they are defined Y P The order can be changed by dragging and dropping All the preprocessing is done by Zabbix server and proxies If any of preprocessing steps fails, then: O C it is possible to react to errors and introduce recovery options. Otherwise , the item becomes unsupported E D U T i S T N https://www.zabbix.com/documentation/6.0/manual/config/items/preprocessing 6.0 Certified Professional ● Day 2 © 2023 by Zabbix. All rights reserved Theory 16 PREPROCESSING - HOW IT WORKS Every collected value goes through the preprocessing: Y P Even the values without preprocessing steps are sent to the preprocessing manager The values collected by proxies are processed on proxies and skip queue on a server O C Data are passed to the preprocessing manager and placed into the preprocessing queue: T N The preprocessing worker takes the value and performs all steps (if any exists) E D After all the steps are done, the value is put back into the queue as processed All the processed values are flushed into a history cache U T S 6.0 Certified Professional ● Day 2 © 2023 by Zabbix. All rights reserved Theory 17 PREPROCESSING WORKERS Multiple preprocessing workers can process values in parallel: Y P ### Option: StartPreprocessors # Number of pre-forked instances of preprocessing workers. # The preprocessing manager process is automatically started when preprocessor worker is started. # Range: 1-1000 # Default: 3 StartPreprocessors=10 T N O C Watch Zabbix internal metrics to monitor the preprocessing performance: E D The items waiting in preprocessing queue How busy are the preprocessing workers U T S Optimal number of preprocessing workers can be determined by many factors: The number of items that require any preprocessing steps Heavy operations like parsing of large XML / JSON chunks may require more preprocessors. 6.0 Certified Professional ● Day 2 © 2023 by Zabbix. All rights reserved Theory 18 THROTTLING "Throttling" preprocessing step discards a value, if it has not changed: Y P Only one throttling option can be specified for an item Discard unchanged Discard unchanged with heartbeat T N O C If a value is discarded, history syncer has no idea that this value was collected No trigger expressions based on this value will be evaluated nodata() function will detect a false positive If history cache does not contain values for an hour, trends cannot be calculated E D U T S 6.0 Certified Professional ● Day 2 /dev/null © 2023 by Zabbix. All rights reserved Theory 19 CHECK FOR NOT SUPPORTED VALUE "Check for not supported value" is a very specific preprocessing step: Y P It is always placed as a first step (even if defined initially as the second, third, etc.) Custom on fail option is always enabled O C It can only fix problems detected during data collection time (wrong key, SNMP OID, etc.) Cannot fix problems with the wrong data type written into a database U T E D Collect data S 6.0 Certified Professional ● Day 2 T N Check not supported © 2023 by Zabbix. All rights reserved Write into database Theory 20 Y P E DPreprocessing U T S 6.0 Certified Professional ● Day 2 T N O C XML XPath © 2023 by Zabbix. All rights reserved 10 minutes 21 PREPROCESSING - XML PATH LANGUAGE (XPATH) XPath (XML Path Language) is a syntax for defining parts of an XML document Y P Navigate through elements and attributes in an XML document Extract a value or a fragment from XML data XML documents are treated as trees of nodes T N The node is selected by following a path or steps E D Zabbix supports XPath 1.0 version Example: U T S Path to a price is /bookstore/book/price i O C <?xml version="1.0" encoding="UTF-8"?> <bookstore> <book> <title lang="en">Harry Potter</title> <author>J K. Rowling</author> <year>2005</year> <price>29.99</price> </book> </bookstore> https://blog.zabbix.com/zabbix-xpath-preprocessing/7936/ 6.0 Certified Professional ● Day 2 © 2023 by Zabbix. All rights reserved Theory 22 XML XPATH FUNCTIONS XPath functions have the following four types: Y P Node functions, Numeric functions, String functions, Boolean functions <?xml version="1.0" encoding="UTF-8"?> <bookstore> string(/bookstore/book[3]/author) <book category="cooking"> <title lang="en">Everyday Italian</title> <author>Giada De Laurentiis</author> <year>2005</year> <price>30.00</price> </book> T N U T <book category="web"> <title lang="en">Learning XML</title> <author>Erik T. Ray</author> <year>2003</year> <price>39.95</price> </book> sum(/bookstore/book/price) 99.94 Count the number of books count(/bookstore/book) 3 Select the price node with a price that is less than 30 number(/bookstore/book[price<30]/price) 29.99 </bookstore> 6.0 Certified Professional ● Day 2 Erik T. Ray Select the sum of all prices E D <book category="children"> <title lang="en">Harry Potter</title> <author>J K. Rowling</author> <year>2005</year> <price>29.99</price> </book> S O C Select the author of the 3rd book © 2023 by Zabbix. All rights reserved Theory 23