Comprehensive Network Traffic Monitoring: A Deep Dive into Zeek, MySQL, and Grafana Integration

Introduction – The Critical Nature of Network Visibility

In today’s digital landscape, understanding what happens on your network isn’t just a technical curiosity—it’s a fundamental security requirement. As infrastructures grow more complex and cyber‑threats more sophisticated, comprehensive monitoring has evolved from best practice to absolute necessity.

Yet most organisations struggle not with capturing traffic, but with making sense of the data flood. How do we transform raw packets into actionable intelligence? How can we visualise patterns that flag breaches, performance issues, or compliance violations?

This guide documents an open‑source, production‑proven stack that delivers enterprise‑grade visibility with no licence fees:

• Zeek (formerly Bro) for deep‑packet inspection & protocol analysis
• MySQL for structured, query‑friendly storage
• Grafana for real‑time, intuitive visualisation

Whether you’re a security analyst, network admin, or curious professional, this framework will give you unprecedented insights into your traffic.

Understanding the Problem Space

How This Stack Solves It

1. Zeek extracts rich protocol‑aware metadata—not just packets.
2. MySQL stores those records efficiently for long‑term querying.
3. Grafana turns technical data into human‑friendly dashboards.
4. Automation stitches everything together so monitoring is hands‑off.

Project Architecture — A Holistic Approach

Network Interface
    ↓  (tcpdump)
Raw Packets
    ↓  (Zeek)
Enriched Logs
    ↓  (Python ETL)
MySQL
    ↓  (Grafana)
Dashboards

Why Modular Beats Monolithic

• Flexibility — swap components without re‑architecting
• Transparency — everything is open & customisable
• Scalability — distribute capture / analysis across hosts
• Depth — Zeek’s protocol parsing trumps basic flow tools

1 • Traffic Capture with tcpdump

Essential CLI Flags

Wrapper Script — Rotating 5‑min Captures

#!/usr/bin/env bash
# /opt/zeek_monitor/scripts/capture.sh
INTERFACE="eth0"
DURATION=300      # seconds
RETENTION=60      # minutes to keep pcaps

while true; do
  TS=$(date +%Y%m%d-%H%M%S)
  tcpdump -i "$INTERFACE" -G "$DURATION" -W 1 \
          -w /opt/zeek_monitor/captures/traffic_"$TS".pcap

  # prune old files
  find /opt/zeek_monitor/captures -name 'traffic_*.pcap' -type f -mmin +"$RETENTION" -delete
done

Production tips — consider excluding backup VLANs, tuning snapshot length (-s 128), or deploying capture cards for >10 Gbps links.

2 • Zeek — The Network Analysis Engine

Out‑of‑the‑Box Super‑powers

• Parses 40+ protocols (DNS, HTTP, TLS, …)
• Tracks stateful connections
• Extracts files in transit
• Logs SSL/TLS certs & ciphers
• Extensible via Zeek script language

Enrichments I Add

GeoIP

@load packages/geoip-conn

event zeek_init() {
  lookup_location(Conn::Info);
}

ASN data

@load packages/asn

JA3 / JA4 TLS Fingerprinting

@load packages/ja3

event ssl_client_hello(c: connection, version: count, record_version: count,
                       possible_ts: time, client_random: string,
                       session_id: string, ciphers: index_vec,
                       comp_methods: index_vec)
{
  local ja3 = md5_hash( ja3_client_string(c$ssl) );
  print fmt("JA3 %s → %s", c$id$orig_h, ja3);
}

Key Logs Produced

3 • Data Storage with MySQL

Core Schema (excerpt)

CREATE TABLE connections (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  ts TIMESTAMP,
  id_orig_h VARCHAR(39),
  id_orig_p INT UNSIGNED,
  id_resp_h VARCHAR(39),
  id_resp_p INT UNSIGNED,
  proto ENUM('tcp','udp','icmp'),
  service VARCHAR(64),
  duration DOUBLE,
  orig_bytes BIGINT UNSIGNED,
  resp_bytes BIGINT UNSIGNED,
  conn_state VARCHAR(16),
  INDEX(ts), INDEX(id_orig_h), INDEX(id_resp_h), INDEX(id_resp_p)
);

CREATE TABLE geo_info (
  connection_id BIGINT,
  country_code CHAR(2),
  city VARCHAR(64),
  latitude DOUBLE,
  longitude DOUBLE,
  FOREIGN KEY (connection_id) REFERENCES connections(id)
);

CREATE TABLE ssl_fingerprints (
  connection_id BIGINT,
  ja3 CHAR(32),
  ja3s CHAR(32),
  FOREIGN KEY (connection_id) REFERENCES connections(id),
  INDEX (ja3)
);

Log → SQL ETL (Python snippet)

# zeek_to_mysql.py (simplified)
import glob, json, mysql.connector
from zeek_log_reader import ZeekLogReader

db = mysql.connector.connect(
    host='localhost', user='zeek_user',
    password=os.getenv('ZEEK_DB_PASSWORD'),
    database='zeek_logs')
cur = db.cursor()

for f in glob.glob('/opt/zeek/logs/current/conn*.log'):
    for row in ZeekLogReader(f):
        cur.execute("""
          INSERT INTO connections
          (ts,id_orig_h,id_orig_p,id_resp_h,id_resp_p,proto,service,
           duration,orig_bytes,resp_bytes,conn_state)
          VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
        """, (
            row['ts'], row['id.orig_h'], row['id.orig_p'],
            row['id.resp_h'], row['id.resp_p'], row['proto'],
            row.get('service',''), row.get('duration',0),
            row.get('orig_bytes',0), row.get('resp_bytes',0),
            row.get('conn_state','')
        ))
db.commit()

Example Hunts

-- Possible beaconing: low jitter, frequent connections
SELECT id_orig_h, id_resp_h, id_resp_p,
       COUNT(*) AS cnt, STDDEV(UNIX_TIMESTAMP(ts)) AS jitter
FROM connections
WHERE ts > NOW() - INTERVAL 1 DAY
GROUP BY id_orig_h, id_resp_h, id_resp_p
HAVING cnt > 10 AND jitter < 5;

-- Rare JA3 hashes
SELECT ja3, COUNT(*) AS hits
FROM ssl_fingerprints JOIN connections USING (connection_id)
WHERE ts > NOW() - INTERVAL 7 DAY
GROUP BY ja3 HAVING hits < 5
ORDER BY hits;

4 • Visualisation with Grafana

Dashboard Line‑up

1. Executive Overview – KPI trends for leadership
2. Security Ops – Incident‑centric panels (beacons, rare certs, weird.log)
3. Network Ops – Bandwidth, retransmissions, utilisation by interface
4. Protocol Deep‑Dive – HTTP verbs, DNS TLDs, TLS versions

Highlight Panels

[world‑map]  Traffic by country  (Geo heat‑map)
[time‑series] Connections / protocol
[table] Top talkers  (bytes)
[heat‑map] Rare service ports
[sankey] JA3 fingerprints → Hosts

MySQL Data‑source JSON

{
  "name": "Zeek MySQL",
  "type": "mysql",
  "url": "localhost:3306",
  "database": "zeek_logs",
  "user": "grafana_user",
  "secureJsonData": { "password": "grafana_password" },
  "jsonData": {
    "maxOpenConns": 100,
    "maxIdleConns": 10,
    "connMaxLifetime": 14400
  }
}

Implementation Guide

Prerequisites

• Ubuntu 20.04+ server, sudo/root access
• ≥ 4 GB RAM, 100 GB+ free disk
• SPAN / TAP / mirror port to ingest traffic

1 • Base System

sudo apt update && sudo apt upgrade -y
sudo apt install -y tcpdump libpcap-dev python3-pip build-essential \
                    default-libmysqlclient-dev
sudo mkdir -p /opt/zeek_monitor/{captures,logs,scripts}

2 • Install Zeek

echo 'deb http://download.opensuse.org/repositories/security:/zeek/xUbuntu_20.04/ /' \
  | sudo tee /etc/apt/sources.list.d/security:zeek.list
curl -fsSL https://download.opensuse.org/repositories/security:zeek/xUbuntu_20.04/Release.key \
  | gpg --dearmor | sudo tee /etc/apt/trusted.gpg.d/zeek.gpg > /dev/null

sudo apt update && sudo apt install -y zeek
echo 'export PATH=$PATH:/opt/zeek/bin' >> ~/.bashrc && source ~/.bashrc
zeek --version

3 • MySQL

sudo apt install -y mysql-server
sudo mysql_secure_installation
sudo mysql -e "CREATE DATABASE zeek_logs;"
sudo mysql -e "CREATE USER 'zeek_user'@'localhost' IDENTIFIED BY 'secure_pw';"
sudo mysql -e "GRANT ALL ON zeek_logs.* TO 'zeek_user'@'localhost'; FLUSH PRIVILEGES;"
sudo mysql zeek_logs < /opt/zeek_monitor/scripts/schema.sql

4 • Python ETL

pip3 install mysqlclient zeek-log-reader geoip2
cp zeek_to_mysql.py /opt/zeek_monitor/scripts/ && chmod +x $_

5 • Grafana

sudo apt install -y apt-transport-https software-properties-common
echo "deb https://packages.grafana.com/oss/deb stable main" \
  | sudo tee /etc/apt/sources.list.d/grafana.list
wget -q -O - https://packages.grafana.com/gpg.key | sudo apt-key add -
sudo apt update && sudo apt install -y grafana
sudo systemctl enable --now grafana-server

6 • Automation (cron)

cat <<'EOF' | sudo tee /opt/zeek_monitor/scripts/monitor.sh
#!/usr/bin/env bash
tcpdump -i eth0 -G 300 -W 1 -w /opt/zeek_monitor/captures/recent.pcap &
TCPDUMP_PID=$!

cd /opt/zeek_monitor/scripts
zeek -r /opt/zeek_monitor/captures/recent.pcap local asn_enrichment.zeek
python3 zeek_to_mysql.py

kill $TCPDUMP_PID
EOF
sudo chmod +x /opt/zeek_monitor/scripts/monitor.sh
(crontab -l 2>/dev/null; echo "*/5 * * * * /opt/zeek_monitor/scripts/monitor.sh") | crontab -

Security & Performance

Hardening Checklist

1. Database — non‑root creds, TLS, credential rotation
2. RBAC — restrict Grafana dashboards per role
3. Data at rest — consider full‑disk or table‑level encryption
4. Network ACLs — allow Grafana/MySQL only from trusted IPs

Performance Tips

• SSDs for MySQL, bump innodb_buffer_pool_size
• Proper indexing on ts, id_resp_h etc.
• Partition or archive old tables for speed
• Cluster Zeek workers for >1 Gbps links

Advanced Use‑Cases

Threat‑Hunting Ideas

• Beaconing — low jitter flows to rare ASNs
• Rare JA3 — fingerprint‑based malware detection
• Unusual geo — new countries in outbound traffic
• Data exfil — oversized uploads off‑hours

Compliance & Performance

• Retain logs per policy, auto‑generate audit PDFs
• Measure DNS resolution latency, HTTP error bursts
• Capacity‑plan via month‑over‑month bandwidth trends

Troubleshooting Quick‑Ref

Future Enhancements

• SIEM forwarders (Wazuh, Elastic)
• Threat‑intel feeds (MISP, OpenCTI)
• Discord/Slack alerts via Grafana Notifier
• ClickHouse or TimescaleDB for petabyte‑scale retention
• ML anomaly detection on stored flows

Conclusion

By blending Zeek’s deep protocol analysis, MySQL’s structured storage, and Grafana’s beautiful dashboards, you get a scalable, affordable, and highly customisable network‑visibility platform.

Ready to deploy? Grab all scripts & panel JSON here → https://github.com/nthuls/Network-Traffic-Monitoring-with-grafana

Happy hunting! 🎯 ```