Prerequisites

• A database instance (MySQL, PostgreSQL, or SQL Server) to serve as Grafana's data source
• A working Grafana installation (follow official guides for setup)
• Navicat for database management
• PyCharm or another Python IDE for data processing

Step 1: Extract Raw Data from Zabbix

Connect to your Zabbix MySQL database using Navicat, then run the following queries to retrivee necessary identifiers and raw traffic data:

SELECT host, hostid FROM hosts WHERE host = 'target-server-name'; -- Replace with your server's name to get its Host ID

SELECT itemid, name, key_ FROM items WHERE hostid = 'your-host-id' AND key_ = 'net.if.out[ifHCOutOctets.20]'; -- Use the Host ID from the previous query to find the Item ID for outbound traffic metrics

SELECT * FROM trends_uint WHERE itemid = 'your-item-id' AND clock >= UNIX_TIMESTAMP('2024-05-01 00:00:00') AND clock < UNIX_TIMESTAMP('2024-06-01 00:00:00'); -- Fetch time-bound traffic data using the Item ID; UNIX_TIMESTAMP optimizes date filtering

Export the final query results as a UTF-8 encoded CSV file via Navicat's export tool.

Step 2: Convert Timestamp Format with Python

Use the following Python script to convert Unix timestamps in the CSV files to human-readable datetime values:

import os
import pandas as pd

# Directory containing raw Zabbix CSV exports (use raw string to avoid escape character issues)
raw_data_directory = r"C:\monitoring\zabbix_traffic_data"

# Process all CSV files in the target directory
for filename in os.listdir(raw_data_directory):
    if filename.lower().endswith('.csv'):
        file_path = os.path.join(raw_data_directory, filename)
        print(f"Processing file: {file_path}")
        
        try:
            # Load CSV data into a DataFrame
            traffic_data = pd.read_csv(file_path)
            
            # Verify the presence of the timestamp column
            if 'clock' in traffic_data.columns:
                # Convert Unix timestamp (seconds) to ISO datetime format
                traffic_data['record_timestamp'] = pd.to_datetime(traffic_data['clock'], unit='s')
                
                # Overwrite the original file with processed data (modify path to save to a new location if preferred)
                traffic_data.to_csv(file_path, index=False)
                print(f"✅ Successfully processed: {filename}")
            else:
                print(f"⚠️ Skipping file: {filename} (missing 'clock' column)")
        except Exception as e:
            print(f"❌ Error processing {filename}: {str(e)}")

Step 3: Prepare Dedicated Database for Grafana

Set up a new database (e.g., traffic_metrics) with a table (e.g., interface_traffic) to store processed traffic data. The table should include these fields:

• record_timestamp (datetime): Timestamp of the traffic reading
• incoming_bits_per_sec (bigint): Incoming traffic in bits per second
• outgoing_bits_per_sec (bigint): Outgoing traffic in bits per second
• incoming_mbps (float): Calculated incoming traffic in Mbps
• outgoing_mbps (float): Calculated outgoing traffic in Mbps

Import Processed Data

1. Format your processed CSV data to match the table schema
2. Copy the data from the CSV and paste it into the database table using Navicat
3. Execute the following query to convert bps values to Mbps:

UPDATE traffic_metrics.interface_traffic
SET
    incoming_mbps = incoming_bits_per_sec / 1000000,
    outgoing_mbps = outgoing_bits_per_sec / 1000000;

Step 4: Configure Grafana for Visualization

1. Initial Grafana Setup: Update the default password and confirm the time zone matches your monitoring infrastructure (use Asia/Shanghai for China-based systems).
2. Add Data Source: Navigate to Data Sources > Add data source, select MySQL, and configure the connection to your traffic_metrics database. Use the "Test Connection" button to verify connectivity.
3. Create Dashboard:
   • Go to Dashboards > Create > Dashboard > Add new panel
   • Select your MySQL data source, then switch to the Code editor for the query.

Single Interface Traffic Query

SELECT
  record_timestamp AS time,
  ROUND(incoming_mbps, 2) AS "Incoming Traffic (Mbps)",
  ROUND(outgoing_mbps, 2) AS "Outgoing Traffic (Mbps)"
FROM
  traffic_metrics.interface_traffic
WHERE
  $__timeFilter(record_timestamp)
ORDER BY record_timestamp;

Multiple Interface Aggregation

For combined traffic from multiple interfaces, import each interface's data into separate tables, then use these queries and Grafana's transformation tools to merge results:

-- Query for Core Switch Port 2
SELECT
  record_timestamp AS time,
  ROUND(incoming_mbps, 2) AS "Core Switch Po2 In (Mbps)",
  ROUND(outgoing_mbps, 2) AS "Core Switch Po2 Out (Mbps)"
FROM
  traffic_metrics.core_switch_po2
WHERE
  $__timeFilter(record_timestamp)
ORDER BY record_timestamp;

-- Query for Router Aggregate Port
SELECT
  record_timestamp AS time,
  ROUND(incoming_mbps, 2) AS "Router Aggregate In (Mbps)",
  ROUND(outgoing_mbps, 2) AS "Router Aggregate Out (Mbps)"
FROM
  traffic_metrics.router_aggregate
WHERE
  $__timeFilter(record_timestamp)
ORDER BY record_timestamp;

Use Grafana's Transform tab to merge the datasets by the time field. To display the 95th percentile:

• In the panel's Legend settings, enable values for Max and 95th percentile
• Configure the Y-axis label to "Mbps" and set the unit to "Mb/s"
• Enable the "Show border" option for better readability

Save the dashboard once all adjustments are complete.

Access and Manage Dashboard

Set up role-based access control in Grafana to assign dashboard permissions to different user groups (viewers, editors, admins). Navigate to the dashboard from the Grafana home page to view real-time or historical traffic visualizations with 95th percentile calculations.