133 lines
4.0 KiB
Python
133 lines
4.0 KiB
Python
import sqlite3
|
|
from datetime import datetime
|
|
|
|
DB_PATH = 'snmp_monitor.db'
|
|
|
|
def init_db():
|
|
conn = sqlite3.connect(DB_PATH)
|
|
c = conn.cursor()
|
|
|
|
# Targets table (devices to monitor)
|
|
c.execute('''
|
|
CREATE TABLE IF NOT EXISTS targets (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT,
|
|
ip_address TEXT NOT NULL,
|
|
port INTEGER DEFAULT 161,
|
|
community TEXT NOT NULL,
|
|
period_minutes INTEGER DEFAULT 5,
|
|
modem_type TEXT DEFAULT 'rv50',
|
|
enabled INTEGER DEFAULT 1,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
''')
|
|
|
|
# Readings table (stored SNMP data)
|
|
c.execute('''
|
|
CREATE TABLE IF NOT EXISTS readings (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
target_id INTEGER NOT NULL,
|
|
bytes_sent INTEGER,
|
|
bytes_recv INTEGER,
|
|
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (target_id) REFERENCES targets(id)
|
|
)
|
|
''')
|
|
|
|
# Check if modem_type column exists, add it if not (for existing databases)
|
|
try:
|
|
c.execute('SELECT modem_type FROM targets LIMIT 1')
|
|
except sqlite3.OperationalError:
|
|
c.execute('ALTER TABLE targets ADD COLUMN modem_type TEXT DEFAULT "rv50"')
|
|
conn.commit()
|
|
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
def add_target(name, ip_address, port, community, period_minutes, modem_type='rv50'):
|
|
conn = sqlite3.connect(DB_PATH)
|
|
c = conn.cursor()
|
|
c.execute('''
|
|
INSERT INTO targets (name, ip_address, port, community, period_minutes, modem_type)
|
|
VALUES (?, ?, ?, ?, ?, ?)
|
|
''', (name, ip_address, port, community, period_minutes, modem_type))
|
|
conn.commit()
|
|
target_id = c.lastrowid
|
|
conn.close()
|
|
return target_id
|
|
|
|
def get_all_targets():
|
|
conn = sqlite3.connect(DB_PATH)
|
|
conn.row_factory = sqlite3.Row
|
|
c = conn.cursor()
|
|
c.execute('SELECT * FROM targets ORDER BY id')
|
|
targets = c.fetchall()
|
|
conn.close()
|
|
return targets
|
|
|
|
def get_target(target_id):
|
|
conn = sqlite3.connect(DB_PATH)
|
|
conn.row_factory = sqlite3.Row
|
|
c = conn.cursor()
|
|
c.execute('SELECT * FROM targets WHERE id = ?', (target_id,))
|
|
target = c.fetchone()
|
|
conn.close()
|
|
return target
|
|
|
|
def update_target(target_id, name, ip_address, port, community, period_minutes, enabled, modem_type='rv50'):
|
|
conn = sqlite3.connect(DB_PATH)
|
|
c = conn.cursor()
|
|
c.execute('''
|
|
UPDATE targets
|
|
SET name=?, ip_address=?, port=?, community=?, period_minutes=?, enabled=?, modem_type=?
|
|
WHERE id=?
|
|
''', (name, ip_address, port, community, period_minutes, enabled, modem_type, target_id))
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
def delete_target(target_id):
|
|
conn = sqlite3.connect(DB_PATH)
|
|
c = conn.cursor()
|
|
c.execute('DELETE FROM readings WHERE target_id = ?', (target_id,))
|
|
c.execute('DELETE FROM targets WHERE id = ?', (target_id,))
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
def add_reading(target_id, bytes_sent, bytes_recv):
|
|
conn = sqlite3.connect(DB_PATH)
|
|
c = conn.cursor()
|
|
c.execute('''
|
|
INSERT INTO readings (target_id, bytes_sent, bytes_recv)
|
|
VALUES (?, ?, ?)
|
|
''', (target_id, bytes_sent, bytes_recv))
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
def get_readings(target_id, hours=24):
|
|
conn = sqlite3.connect(DB_PATH)
|
|
conn.row_factory = sqlite3.Row
|
|
c = conn.cursor()
|
|
c.execute('''
|
|
SELECT * FROM readings
|
|
WHERE target_id = ?
|
|
AND timestamp >= datetime('now', '-{} hours')
|
|
ORDER BY timestamp
|
|
'''.format(hours), (target_id,))
|
|
readings = c.fetchall()
|
|
conn.close()
|
|
return readings
|
|
|
|
def get_latest_reading(target_id):
|
|
conn = sqlite3.connect(DB_PATH)
|
|
conn.row_factory = sqlite3.Row
|
|
c = conn.cursor()
|
|
c.execute('''
|
|
SELECT * FROM readings
|
|
WHERE target_id = ?
|
|
ORDER BY timestamp DESC
|
|
LIMIT 1
|
|
''', (target_id,))
|
|
reading = c.fetchone()
|
|
conn.close()
|
|
return reading
|