Monitoring network routers is crucial for maintaining performance and security. Automating the process of collecting logs from routers like Netgear, Asus, and Linksys and storing them in a SQLite database saves time and effort. This guide walks you through creating Python scripts that dynamically collect alarm logs and store them in a database. Importantly, we’ll also show how to securely handle sensitive credentials like usernames and passwords when accessing the routers.
1. Handling Credentials Securely
Before diving into the specific code for each router, it’s important to understand how to hide your router credentials (username and password) within the script. This can be done by storing the credentials in environment variables or a configuration file that’s not hard-coded into your Python script.
Using Environment Variables for Security
You can store your credentials in environment variables to keep them out of your code. Here’s how to do it:
- Set environment variables:
On Linux/macOS, add these lines to your .bashrc or .zshrc file:
export ROUTER_USERNAME="your_username"
export ROUTER_PASSWORD="your_password"On Windows, use the command prompt or PowerShell:
setx ROUTER_USERNAME "your_username"
setx ROUTER_PASSWORD "your_password"2. Access environment variables in your Python code:
import os
username = os.getenv('ROUTER_USERNAME')
password = os.getenv('ROUTER_PASSWORD')Now that we’ve handled the credentials securely, let’s move to collecting logs from each router.
2. Fetching Logs from Netgear Routers
Netgear routers, such as the Nighthawk series, provide access to logs via HTTP. The following Python script dynamically collects logs, creates a corresponding SQLite database table, and stores the logs.
Full Python Code for Netgear Routers
import os
import requests
import sqlite3
def get_netgear_logs(ip_address):
username = os.getenv('ROUTER_USERNAME') # Get username from environment variable
password = os.getenv('ROUTER_PASSWORD') # Get password from environment variable
url = f"http://{ip_address}/log_get.cgi"
auth = (username, password)
response = requests.get(url, auth=auth)
if response.status_code == 200:
return response.text
else:
print(f"Failed to retrieve logs from Netgear router. Status code: {response.status_code}")
return None
def create_dynamic_table(cursor, log_data):
log_lines = log_data.splitlines()
if log_lines:
headers = log_lines[0].split() # First line contains headers
columns = ', '.join([f'{header} TEXT' for header in headers])
cursor.execute(f"CREATE TABLE IF NOT EXISTS netgear_logs ({columns})")
return headers
return None
def insert_dynamic_logs(cursor, headers, log_data):
log_lines = log_data.splitlines()[1:] # Skip header row
for line in log_lines:
values = line.split()
placeholders = ', '.join('?' * len(values))
cursor.execute(f"INSERT INTO netgear_logs ({', '.join(headers)}) VALUES ({placeholders})", values)
def fetch_netgear_logs():
conn = sqlite3.connect('router_logs.db')
cursor = conn.cursor()
netgear_logs = get_netgear_logs('<netgear_ip>')
if netgear_logs:
headers = create_dynamic_table(cursor, netgear_logs)
if headers:
insert_dynamic_logs(cursor, headers, netgear_logs)
conn.commit()
conn.close()
# Example usage:
fetch_netgear_logs()In this script, credentials are stored securely in environment variables, accessed with os.getenv(). Replace <netgear_ip> with your actual Netgear router IP address.
3. Fetching Logs from Asus Routers
Asus routers, such as the RT-AC86U, provide log access through HTTP. Below is the Python script for dynamically collecting logs from an Asus router and storing them in a SQLite database.
Full Python Code for Asus Routers
import os
import requests
import sqlite3
def get_asus_logs(ip_address):
username = os.getenv('ROUTER_USERNAME') # Get username from environment variable
password = os.getenv('ROUTER_PASSWORD') # Get password from environment variable
url = f"http://{ip_address}/Main_LogStatus_Content.asp"
auth = (username, password)
response = requests.get(url, auth=auth)
if response.status_code == 200:
return response.text
else:
print(f"Failed to retrieve logs from Asus router. Status code: {response.status_code}")
return None
def create_dynamic_table(cursor, log_data):
log_lines = log_data.splitlines()
if log_lines:
headers = log_lines[0].split() # First line contains headers
columns = ', '.join([f'{header} TEXT' for header in headers])
cursor.execute(f"CREATE TABLE IF NOT EXISTS asus_logs ({columns})")
return headers
return None
def insert_dynamic_logs(cursor, headers, log_data):
log_lines = log_data.splitlines()[1:] # Skip header row
for line in log_lines:
values = line.split()
placeholders = ', '.join('?' * len(values))
cursor.execute(f"INSERT INTO asus_logs ({', '.join(headers)}) VALUES ({placeholders})", values)
def fetch_asus_logs():
conn = sqlite3.connect('router_logs.db')
cursor = conn.cursor()
asus_logs = get_asus_logs('<asus_ip>')
if asus_logs:
headers = create_dynamic_table(cursor, asus_logs)
if headers:
insert_dynamic_logs(cursor, headers, asus_logs)
conn.commit()
conn.close()
# Example usage:
fetch_asus_logs()Here, as with the Netgear script, the credentials are securely handled via environment variables, making sure your sensitive information is not hard-coded into the script.
4. Fetching Logs from Linksys Routers
Linksys routers, like the WRT3200ACM, also allow access to logs through their web interface. The script below dynamically collects logs from a Linksys router and stores them in a SQLite database.
Full Python Code for Linksys Routers
import os
import requests
import sqlite3
def get_linksys_logs(ip_address):
username = os.getenv('ROUTER_USERNAME') # Get username from environment variable
password = os.getenv('ROUTER_PASSWORD') # Get password from environment variable
url = f"http://{ip_address}/SysInfo.htm"
auth = (username, password)
response = requests.get(url, auth=auth)
if response.status_code == 200:
return response.text
else:
print(f"Failed to retrieve logs from Linksys router. Status code: {response.status_code}")
return None
def create_dynamic_table(cursor, log_data):
log_lines = log_data.splitlines()
if log_lines:
headers = log_lines[0].split() # First line contains headers
columns = ', '.join([f'{header} TEXT' for header in headers])
cursor.execute(f"CREATE TABLE IF NOT EXISTS linksys_logs ({columns})")
return headers
return None
def insert_dynamic_logs(cursor, headers, log_data):
log_lines = log_data.splitlines()[1:] # Skip header row
for line in log_lines:
values = line.split()
placeholders = ', '.join('?' * len(values))
cursor.execute(f"INSERT INTO linksys_logs ({', '.join(headers)}) VALUES ({placeholders})", values)
def fetch_linksys_logs():
conn = sqlite3.connect('router_logs.db')
cursor = conn.cursor()
linksys_logs = get_linksys_logs('<linksys_ip>')
if linksys_logs:
headers = create_dynamic_table(cursor, linksys_logs)
if headers:
insert_dynamic_logs(cursor, headers, linksys_logs)
conn.commit()
conn.close()
# Example usage:
fetch_linksys_logs()The credentials are again handled securely using environment variables, and the logs are dynamically parsed and stored in the SQLite database.
Thank you for following along with this tutorial. We hope you found it helpful and informative. If you have any questions, or if you would like to suggest new Python code examples or topics for future tutorials/articles, please feel free to join and comment. Your feedback and suggestions are always welcome!
You can find the same tutorial on Medium.com.
