Applies To:

Show Versions Show Versions

Manual Chapter: Database Schema Overview
Manual Chapter
Table of Contents   |   Next Chapter >>

Customized health and performance monitoring statistics queries

Enterprise Manager collects and displays statistics for each managed device in your network. This document provides you with information about the health and performance monitoring database including collected statistics types, query samples, the database schema, and details about each field in the database tables. You can use this information, in conjunction with any MySQL Connector, (available at http://dev.mysql.com/downloads/connector) to create your own queries to the health and performance monitoring database for customized graphs and reports.

Types of statistics

Enterprise Manager stores in its health and performance monitoring database three types of statistics: counter statistics, gauge statistics, and threshold state statistics.

Counter statistics increment periodically to indicate a rate of change. To report the rate of change for a statistic during a specific period of time, the system performs a query for the counter statistics values and the timestamps for the beginning and end of the specified time period. Once the system receives the query response, it calculates and reports the rate of change.

Gauge statistics are absolute values for certain components, such as temperature, fan speed, and current connections. The system does not have to perform a calculation to report the absolute value of a gauge statistic.

A threshold state statistic is the current state of a specific statistic value as it relates to its threshold. That is, the threshold state indicates if the statistic value is above, below, or within a specified threshold.

Counter statistic query sample

The following SQL query example retrieves the counter statistic values for the server_pkts_in field for a node on the host device, bigip-central.

To calculate the rate of change for this example, the system compares the previous value (perfmon_node_stat p) and the current value (perfmon_node_stat c) in the perfmon_node_stat table. With this data, the system calculates the rate of change for the counter statistic using the calculation (V1-V0) / (T1-T0), where V1 is the value of the server_pkts_in field at time c.t, and V0 is the value of the server_pkts_in field at time p.t. (The insert_order field indicates the previous timestamp and value for that particular node.)

SELECT c.t, IFNULL(ABS(ROUND((p.server_pkts_in - c.server_pkts_in) / TIMESTAMPDIFF(SECOND, p.t, c.t))), 0) AS server_pkts_in_per_sec FROM perfmon_device d, perfmon_device_object o, perfmon_node_stat c, perfmon_node_stat p WHERE d.host_name = 'bigip-central' AND o.perfmon_device_uid = d.uid AND c.perfmon_device_object_uid = o.uid AND (p.insert_order = (c.insert_order – 1) AND p.perfmon_device_object_uid = c.perfmon_device_object_uid) AND c.t BETWEEN TIMESTAMPADD(MINUTE, -60, NOW()) AND NOW() AND p.t BETWEEN TIMESTAMPADD(MINUTE, -60, NOW()) AND NOW();

Gauge statistic query sample

The following example shows a gauge statistic SQL query that retrieves all values over the past hour for the chassis temperature on the host device, bigip-central.

SELECT s.t, s.temperature FROM perfmon_device d, perfmon_device_object o, perfmon_chassis_stat s WHERE d.host_name = 'bigip-central' AND o.perfmon_device_uid = d.uid AND s.perfmon_device_object_uid = o.uid AND s.t BETWEEN TIMESTAMPADD(MINUTE, -60, NOW()) AND NOW();

Threshold state statistic query sample

The following SQL query retrieves the current threshold state values for the perfmon_vip_stat.client_pkts_in field for all virtual servers associated with managed devices.

SELECT d.host_name, do.name, t.min_threshold, t.max_threshold FROM perfmon_threshold_state t, perfmon_device_object do, perfmon_device d WHERE t.perfmon_device_object_uid = do.uid AND t.stat_column_name = 'client_pkts_in' AND d.uid = do.perfmon_device_uid

Health and performance monitoring database structure

The Enterprise Manager health and performance monitoring database consists of fact tables and dimension tables. The following diagram is an overview of the basic structure of the statistics database.

Table of Contents   |   Next Chapter >>

Was this resource helpful in solving your issue?




NOTE: Please do not provide personal information.



Incorrect answer. Please try again: Please enter the words to the right: Please enter the numbers you hear:

Additional Comments (optional)