Zabbix Database Schema Overview

For Zabbix 6.0 and later versions, understanding the database schema is essential for crafting efficient monitoring queries. The following queries demonstrate various approaches to extract performance metrics and status information from the Zabbix database.

Comprehensive Host Resource Monitoring

WITH host_info AS (
    SELECT 
        iface.ip_address,
        node.host_name,
        node.machine_id,
        grp.group_label,
        CASE iface.availability_status 
            WHEN '1' THEN 'Operational' 
            ELSE 'Malfunction' 
        END host_condition
    FROM network_interface iface
    JOIN host_node node ON iface.machine_id = node.machine_id
    JOIN host_group_mapping hgm ON iface.machine_id = hgm.machine_id
    JOIN host_group grp ON hgm.group_id = grp.group_id
    WHERE node.flags <> 2
),
memory_metrics AS (
    SELECT 
        item.machine_id,
        ROUND(MIN(trend.min_val), 2) AS lowest_mem,
        ROUND(AVG(trend.avg_val), 2) AS typical_mem,
        ROUND(MAX(trend.max_val), 2) AS highest_mem
    FROM trend_data trend
    JOIN monitor_item item ON trend.item_id = item.item_id
    WHERE item.item_name IN ('Memory utilization', 'Linux: Memory utilization')
        AND trend.timestamp >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 86400 SECOND))
    GROUP BY item.machine_id
),
processor_metrics AS (
    SELECT 
        item.machine_id,
        ROUND(MIN(trend.min_val), 2) AS lowest_cpu,
        ROUND(AVG(trend.avg_val), 2) AS typical_cpu,
        ROUND(MAX(trend.max_val), 2) AS highest_cpu
    FROM trend_data trend
    JOIN monitor_item item ON trend.item_id = item.item_id
    WHERE item.item_name IN ('CPU utilization', 'Linux: CPU utilization')
        AND trend.timestamp >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 86400 SECOND))
    GROUP BY item.machine_id
)
SELECT 
    host_info.group_label,
    host_info.ip_address,
    host_info.host_name,
    host_info.host_condition,
    memory_metrics.lowest_mem,
    memory_metrics.typical_mem,
    memory_metrics.highest_mem,
    processor_metrics.lowest_cpu,
    processor_metrics.typical_cpu,
    processor_metrics.highest_cpu
FROM host_info
LEFT JOIN memory_metrics ON host_info.machine_id = memory_metrics.machine_id
LEFT JOIN processor_metrics ON host_info.machine_id = processor_metrics.machine_id
ORDER BY host_info.group_label;

Oracle Database Status Monitoring

WITH oracle_hosts AS (
    SELECT 
        iface.ip_address,
        node.host_name,
        node.machine_id,
        grp.group_label,
        CASE iface.availability_status 
            WHEN '1' THEN 'Operational' 
            ELSE 'Malfunction' 
        END host_condition
    FROM network_interface iface
    JOIN host_node node ON iface.machine_id = node.machine_id
    JOIN host_group_mapping hgm ON iface.machine_id = hgm.machine_id
    JOIN host_group grp ON hgm.group_id = grp.group_id
    WHERE node.flags <> 2 AND grp.group_label = 'ORACLE'
),
db_status AS (
    SELECT 
        item.machine_id,
        CASE recent.uint_value 
            WHEN '1' THEN 'Active' 
            ELSE 'Inactive' 
        END database_state
    FROM monitor_item item
    JOIN recent_uint_data recent ON item.item_id = recent.item_id
    WHERE item.item_name = 'Alive'
        AND recent.timestamp >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 600 SECOND))
)
SELECT 
    oracle_hosts.group_label,
    oracle_hosts.ip_address,
    oracle_hosts.host_name,
    oracle_hosts.host_condition,
    db_status.database_state
FROM oracle_hosts
LEFT JOIN db_status ON oracle_hosts.machine_id = db_status.machine_id;

MySQL Performance Metrics

WITH mysql_servers AS (
    SELECT 
        iface.ip_address,
        node.host_name,
        node.machine_id,
        grp.group_label
    FROM network_interface iface
    JOIN host_node node ON iface.machine_id = node.machine_id
    JOIN host_group_mapping hgm ON iface.machine_id = hgm.machine_id
    JOIN host_group grp ON hgm.group_id = grp.group_id
    WHERE grp.group_label = 'MYSQL'
),
buffer_stats AS (
    SELECT 
        item.machine_id,
        ROUND(hist.float_value, 2) AS buffer_usage
    FROM monitor_item item
    JOIN history_float hist ON item.item_id = hist.item_id
    WHERE item.item_name = 'MySQL: Buffer pool utilization'
        AND hist.timestamp >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 600 SECOND))
),
query_stats AS (
    SELECT 
        item.machine_id,
        ROUND(hist.float_value, 0) AS queries_per_sec
    FROM monitor_item item
    JOIN history_float hist ON item.item_id = hist.item_id
    WHERE item.item_name = 'MySQL: Queries per second'
        AND hist.timestamp >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 600 SECOND))
)
SELECT 
    mysql_servers.group_label,
    mysql_servers.ip_address,
    mysql_servers.host_name,
    buffer_stats.buffer_usage,
    query_stats.queries_per_sec
FROM mysql_servers
LEFT JOIN buffer_stats ON mysql_servers.machine_id = buffer_stats.machine_id
LEFT JOIN query_stats ON mysql_servers.machine_id = query_stats.machine_id;

Top Resource Consumption Queries

CPU Utilization Leaders

SELECT 
    node.host_name,
    iface.ip_address,
    item.item_name,
    FROM_UNIXTIME(hist.timestamp) AS recorded_time,
    MAX(hist.float_value) AS cpu_load
FROM host_node node
JOIN network_interface iface ON node.machine_id = iface.machine_id
JOIN monitor_item item ON node.machine_id = item.machine_id
JOIN history_float hist ON item.item_id = hist.item_id
WHERE iface.ip_address IS NOT NULL 
    AND item.item_name = 'CPU utilization'
    AND hist.timestamp >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 300 SECOND))
GROUP BY node.machine_id
ORDER BY cpu_load DESC
LIMIT 10;

Memory Usage Leaders

SELECT 
    node.host_name,
    iface.ip_address,
    FROM_UNIXTIME(hist.timestamp) AS recorded_time,
    MAX(hist.float_value) AS memory_usage
FROM host_node node
JOIN network_interface iface ON node.machine_id = iface.machine_id
JOIN monitor_item item ON node.machine_id = item.machine_id
JOIN history_float hist ON item.item_id = hist.item_id
WHERE iface.ip_address IS NOT NULL 
    AND item.item_name = 'Memory utilization'
    AND hist.timestamp >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 300 SECOND))
GROUP BY node.machine_id
ORDER BY memory_usage DESC
LIMIT 10;

Alert Analysis Queries

Alert Severity Distribution

SELECT 
    COUNT(*) AS alert_count,
    alert.severity_level,
    CASE alert.severity_level
        WHEN '0' THEN 'Unclassified'
        WHEN '1' THEN 'Information'
        WHEN '2' THEN 'Warning'
        WHEN '3' THEN 'Average'
        WHEN '4' THEN 'High'
        WHEN '5' THEN 'Critical'
        ELSE 'Unknown'
    END AS severity_label
FROM alert_data alert
JOIN trigger_item_mapping tim ON alert.object_id = tim.trigger_id
JOIN monitor_item item ON tim.item_id = item.item_id
JOIN host_node node ON item.machine_id = node.machine_id
WHERE alert.recovery_event_id IS NULL
    AND node.machine_status = 0
    AND item.item_status = 0
    AND FROM_UNIXTIME(alert.timestamp) >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY alert.severity_level
ORDER BY alert.severity_level;

Active Alert List

SELECT 
    alert.event_identifier,
    FROM_UNIXTIME(alert.timestamp) AS alert_time,
    alert.description AS alert_message,
    CASE alert.severity_level
        WHEN '0' THEN 'Unclassified'
        WHEN '1' THEN 'Information'
        WHEN '2' THEN 'Warning'
        WHEN '3' THEN 'Average'
        WHEN '4' THEN 'High'
        WHEN '5' THEN 'Critical'
    END AS severity_label,
    node.host_name,
    iface.ip_address
FROM alert_data alert
JOIN trigger_item_mapping tim ON alert.object_id = tim.trigger_id
JOIN monitor_item item ON tim.item_id = item.item_id
JOIN host_node node ON item.machine_id = node.machine_id
JOIN network_interface iface ON node.machine_id = iface.machine_id
WHERE alert.recovery_event_id IS NULL
    AND node.machine_status = 0
    AND item.item_status = 0
    AND FROM_UNIXTIME(alert.timestamp) >= DATE_SUB(NOW(), INTERVAL 12 HOUR)
ORDER BY alert.timestamp DESC;

Host Group Status Summary

SELECT 
    grp.group_label,
    COUNT(DISTINCT node.machine_id) AS problematic_hosts,
    (SELECT COUNT(*) FROM host_group_mapping WHERE group_id = grp.group_id) AS total_hosts,
    (SELECT COUNT(*) FROM host_group_mapping WHERE group_id = grp.group_id) - 
    COUNT(DISTINCT node.machine_id) AS healthy_hosts
FROM host_group grp
LEFT JOIN host_group_mapping hgm ON grp.group_id = hgm.group_id
LEFT JOIN host_node node ON hgm.machine_id = node.machine_id
LEFT JOIN trigger_item_mapping tim ON node.machine_id = tim.item_id
LEFT JOIN alert_data alert ON tim.trigger_id = alert.object_id
WHERE alert.recovery_event_id IS NULL
    OR alert.recovery_event_id IS NULL
GROUP BY grp.group_label
ORDER BY problematic_hosts DESC;

Operational Host Count

SELECT 
    SUM(CASE WHEN availability_status = '1' THEN 1 ELSE 0 END) AS operational_count,
    SUM(CASE WHEN availability_status = '0' THEN 1 ELSE 0 END) AS unavailable_count,
    COUNT(*) AS total_monitored
FROM network_interface;

Host Problem Ranking

SELECT 
    node.host_name,
    COUNT(alert.event_identifier) AS incident_count
FROM host_node node
JOIN monitor_item item ON node.machine_id = item.machine_id
JOIN trigger_item_mapping tim ON item.item_id = tim.item_id
JOIN alert_data alert ON tim.trigger_id = alert.object_id
WHERE alert.recovery_event_id IS NULL
    AND node.machine_status = 0
    AND item.item_status = 0
GROUP BY node.host_name
ORDER BY incident_count DESC
LIMIT 20;