Router Statistics Collector

This Go application is designed to periodically collect network traffic statistics from your router (specifically, devices running OpenWrt or similar firmware that expose cgi-bin endpoints for stats and DHCP leases) and store them in SQLite databases. It tracks cumulative and monthly traffic for connected clients and the main WAN interface, as well as current DHCP lease information. The application also provides an internal web server for API access, which is intended to be used with a reverse proxy like Nginx.

Features

• Traffic Monitoring: Collects RX (received) and TX (transmitted) bytes for WiFi clients and the main WAN interface.
• Monthly Aggregation: Aggregates traffic data on a monthly basis, resetting totals at the start of each new month.
• Router Reset Handling: Intelligently handles router reboots by detecting decreases in cumulative byte counters and adjusting incremental calculations.
• DHCP Lease Tracking: Records DHCP lease details including MAC address, IP address, hostname, and lease expiration time.
• Concurrent Processing: Uses Go goroutines to fetch data from multiple routers concurrently.
• SQLite Storage: Stores all data in local SQLite database files (network_stats.db and dhcp_leases.db).
• Internal Scheduling: The application runs in a continuous loop, performing data collection every 30 minutes.
• Built-in API Server: Provides JSON API endpoints for accessing the collected data, designed to be proxied by a web server like Nginx.

---

Setup and Usage

Prerequisites

• Go Language: Go 1.16 or newer installed on your Orange Pi Zero 3.

  sudo apt update && sudo apt install golang

• C Compiler (GCC): Required for compiling the SQLite3 Go driver.

  sudo apt update && sudo apt install build-essential

• Nginx: A web server to serve the frontend and act as a reverse proxy for the Go application.

• Router Endpoints: Your router must expose endpoints for:

  • totalwifi.cgi (WiFi client stats)
  • wan.cgi (WAN interface stats)
  • dhcp.cgi (DHCP leases)

---

1. Application Files

Place the following files in a dedicated directory on your Orange Pi Zero 3, for example: /home/wan/netstat/

• main.go: The Go source code.
• routers.json: The configuration file specifying your router(s).

Example routers.json:

{
    "192.168.1.1": {
        "ap_stats": "http://192.168.1.1/cgi-bin/totalwifi.cgi",
        "wan_stats": "http://192.168.1.1/cgi-bin/wan.cgi",
        "dhcp_leases": "http://192.168.1.1/cgi-bin/dhcp.cgi"
    },
    "192.168.1.2": {
        "ap_stats": "http://192.168.1.2/cgi-bin/totalwifi.cgi",
        "wan_stats": "",
        "dhcp_leases": ""
    }
}

Note: If a URL is empty, the script will skip fetching that endpoint.

---

2. Compile the Go Application

cd /home/wan/netstat/

# Initialize Go module (only once)
go mod init router_stats

# Get SQLite driver
go get github.com/mattn/go-sqlite3

# Build
go build -o router_stats_go

# Make executable
chmod +x router_stats_go

This creates /home/wan/netstat/router_stats_go.

---

3. Database Location and Permissions

Databases are stored in /var/www/netstat-data/.

sudo mkdir -p /var/www/netstat-data/
sudo chown -R wan:wan /var/www/netstat-data/
sudo usermod -a -G wan www-data
sudo chmod 775 /var/www/netstat-data/

After first run, ensure database file permissions:

sudo chmod 664 /var/www/netstat-data/*.db

---

4. Run as a Systemd Service

Create /etc/systemd/system/router-stats.service:

[Unit]
Description=Router Statistics Collector (Continuous)
After=network.target

[Service]
Type=simple
User=wan
Group=wan
WorkingDirectory=/home/wan/netstat/
ExecStart=/home/wan/netstat/router_stats_go
Restart=on-failure
RestartSec=5s
StandardOutput=journal
StandardError=journal

[Install]
WantedBy=multi-user.target

Reload and enable:

sudo systemctl daemon-reload
sudo systemctl enable router-stats.service
sudo systemctl start router-stats.service

---

5. Nginx Reverse Proxy Configuration

Place index.html in /var/www/html/netstat/.

Example Nginx config:

server {
    listen 80;
    server_name data.home;

    root /var/www/html/netstat;
    index index.html;

    location / {
        try_files $uri $uri/ =404;
    }

    location /api/ {
        proxy_pass http://127.0.0.1:8080/;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
    }
}

Restart Nginx:

sudo systemctl restart nginx

---

API Endpoints

• http://your-server-ip/api/combined → Combined monthly client + WAN traffic
• http://your-server-ip/api/wan-history → Monthly WAN history
• http://your-server-ip/api/wan → Current monthly WAN traffic
• http://your-server-ip/api/leases → DHCP leases

---