Monitoring system performance is critical for maintaining the health and efficiency of any computing environment. Python, with its robust libraries and simplicity, offers an excellent way to achieve real-time monitoring while integrating with databases for data storage and analysis. In this blog, we will explore how to build a basic system monitoring solution using Python, psutil
for performance metrics, and pyodbc
for storing the collected data in a SQL Server database.
System performance monitoring helps in:
Monitoring system performance is critical for maintaining the health and efficiency of any computing environment. Python, with its robust libraries and simplicity, offers an excellent way to achieve real-time monitoring while integrating with databases for data storage and analysis. In this blog, we will explore how to build a basic system monitoring solution using Python, psutil
for performance metrics, and pyodbc
for storing the collected data in a SQL Server database.
System performance monitoring helps in:
psutil
: A cross-platform library to retrieve information on system utilization (CPU, memory, disk, network, etc.).pyodbc
: A Python library for connecting to databases using ODBC.Here is the Python code to monitor system performance and save it to a SQL Server database:
The script connects to a SQL Server database using pyodbc
. Replace connection parameters such as Server
and Database
with your actual server details.
con = pyodbc.connect(‘Driver={SQL Server};’
‘Server=lochan\mssqlserver01;’
‘Database=ds;’
‘Trusted_Connection=yes;’)
cursor = con.cursor()
Using psutil
, the script gathers the following metrics:
cpu_usage = psutil.cpu_percent()
memory_usage = psutil.virtual_memory()[2]
cpu_interrupts = psutil.cpu_stats()[1]
cpu_calls = psutil.cpu_stats()[3]
memory_used = psutil.virtual_memory()[3]
memory_free = psutil.virtual_memory()[4]
bytes_sent = psutil.net_io_counters()[0]
bytes_received = psutil.net_io_counters()[1]
disk_usage = psutil.disk_usage(‘/’)[3]
Each metric is inserted into a database table named performance
. The table should be predefined with columns matching the metrics collected.
CREATE TABLE performance (
time DATETIME,
cpu_usage FLOAT,
memory_usage FLOAT,
cpu_interrupts INT,
cpu_calls INT,
memory_used BIGINT,
memory_free BIGINT,
bytes_sent BIGINT,
bytes_received BIGINT,
disk_usage FLOAT
);
cursor.execute(‘INSERT INTO performance (time, cpu_usage, memory_usage, cpu_interrupts, cpu_calls, memory_used, memory_free, bytes_sent, bytes_received, disk_usage) VALUES (GETDATE(), ?, ?, ?, ?, ?, ?, ?, ?, ?)’,
(cpu_usage, memory_usage, cpu_interrupts, cpu_calls, memory_used, memory_free, bytes_sent, bytes_received, disk_usage))
con.commit()
The script runs for a specified duration (timeout
), inserts data every second, and handles errors gracefully.
try:
# Data collection and insertion logic
except Exception as e:
print(f”Error occurred: {e}”)
con.rollback()
This Python script demonstrates how easy it is to implement a basic system monitoring solution that integrates with a database. With slight modifications, it can be customized for more complex scenarios or scaled to handle large infrastructures. System monitoring is vital for ensuring reliability, and Python’s flexibility makes it a go-to choice for such tasks.
What’s next? Enhance this script to include real-time alerts, log file monitoring, or integrate it with a cloud-based monitoring solution.
Lochan R