Level of Difficulty: Beginner to Intermediate.

This blog post is aimed at providing basic (automated) server (or computer) monitoring by using a Python script that can write to a SQL Server database or to a CSV.
It is important to note that there are various platforms that allow for advanced server monitoring along with a mature system, purchasable for a “mature” price. This post is an introduction to how basic usage monitoring may be done through the use of Python libraries and functionality.
What are the steps?
The steps that we will be following are:
- Create the Python script
- Configure data source connection (SQL Server or CSV storage)
- Import necessary libraries (based on data source)
- Tweak usage metrics to be monitored
- Tweak any alerting (if and where necessary)
- Schedule the script to run automatically
- Create the Windows task scheduled job
Deep Dive
Let’s dive deeper into the steps listed above.
Create The Python Script – CSV Integration
Ensure that the pandas and psutil libraries are installed. You can install these libraries by executing the following commands:
pip install pandas pip install psutil
Import libraries
Use the code below to import the correct libraries:
from datetime import datetime from subprocess import call import pandas as pd import psutil import numpy as np
Create Record Metrics Method – Written to CSV
The code that we will be using in this post will monitor the following metrics:
- CPU percentage
- Virtual memory
- Drive usage
- Last Booted Time
Other metrics that can be monitored using the psutil library include:
- Disks
- Network
- Sensors
- Other system info
- Process management
- Windows services
Examples of the above mentioned functionality can be found here.
Use and adapt the code below as you see fit, in order to create a method with functionality that records the following metrics:
def RecordMetrics(): # gets the disk partitions in order to get a list of NFTS drives drps = psutil.disk_partitions() drives = [dp.device for dp in drps if dp.fstype == 'NTFS'] # reads the existing csv df_csv = pd.read_csv('ServerMonitoring.csv') # assigns a new history ID for data integrity purposes history_id = max(df_csv['HistoryID']) + 1 # initialises the data frame with the appropriate values df = pd.DataFrame( { 'CPU': [psutil.cpu_percent()], 'VirtualMemory': [psutil.virtual_memory()[2]], 'HistoryID' : [history_id], 'LastBootedAt' : datetime.fromtimestamp(psutil.boot_time()).strftime('%Y-%m-%d %H:%M:%S') }) # records the drive usage for each drive found for drive in drives: df['{}_DriveUsage'.format(drive.replace(":\\",""))] = psutil.disk_usage(drive)[3] # adds the current date and time stamp df['LoadDate'] = datetime.now() # appends the data to the existing csv file df = df.append(df_csv) df.to_csv('ServerMonitoring.csv', index=False)
Create The Python Script – Database Integration
Ensure that the following are installed:
- urllib
- pandas
- sqlalchemy
- psutil
- pyodbc
You can install these libraries by executing the following commands:
pip install urllib pip install psutil pip install pandas pip install sqlalchemy pip install pyodbc
Import libraries
Use the code below to import the correct libraries:
from sqlalchemy import create_engine, event from datetime import datetime from subprocess import call import urllib import pandas as pd import psutil import pyodbc import numpy as np
Create Record Metrics Method – Written to Database
The code that we will be using in this post will monitor the following metrics:
- CPU percentage
- Virtual memory
- Drive usage
- Last Booted Time
Other metrics that can be monitored using the psutil library include:
- Disks
- Network
- Sensors
- Other system info
- Process management
- Windows services
Examples of the above mentioned functionality can be found here.
Use and adapt the code below as you see fit, in order to create a method with functionality that records the following metrics:
def RecordMetrics(): # dataabase connection - assign to the correct values driver = '{SQL Server}' server = '' user = '' password = '' database = '' table = 'ServerMonitoring' params = urllib.parse.quote_plus(r'DRIVER={};SERVER={};DATABASE={};UID={};PWD={}'.format(driver, server, database, user, password)) conn_str = 'mssql+pyodbc:///?odbc_connect={}'.format(params) engine = create_engine(conn_str) # gets the disk partitions in order to get a list of NFTS drives drps = psutil.disk_partitions() drives = [dp.device for dp in drps if dp.fstype == 'NTFS'] # initialises the data frame with the appropriate values df = pd.DataFrame( { 'CPU': [psutil.cpu_percent()], 'VirtualMemory': [psutil.virtual_memory()[2]], 'LastBootedAt' : datetime.fromtimestamp(psutil.boot_time()).strftime('%Y-%m-%d %H:%M:%S') }) # records the drive usage for each drive found for drive in drives: df['{}_DriveUsage'.format(drive.replace(":\\",""))] = psutil.disk_usage(drive)[3] # adds the current date and time stamp df['LoadDate'] = datetime.now() #if_exists="replace" if the table does not yet exist, then add HistoryID (or ID) as the auto-incremented primary key df.to_sql(table, engine, if_exists="append", index=False)
Create CPU Alerting Method – Database Integration
The method below pulls the last five records from SQL, gets an average and based on the average, makes a decision. The tolerance may be set accordingly and the print may be replace with instant message notifications (available here):
def CheckCPU(): df_CPU = df.read_sql('SELECT TOP(5) CPU FROM dbo.ServerMonitoring ORDER BY LoadDate DESC', conn_str) avg_CPU = df_CPU['CPU'].mean() # if the CPU Average is above 90% if avg_CPU > 90: print('High CPU Usage Detected: {}%'.format(round(avg_CPU,2)))
Create Boot Time Alerting Method – Database Integration
The method below pulls the last boot time record from SQL, and makes a decision accordingly. The tolerances may be set accordingly and the print may be replace with instant message notifications (available here):
def CheckBootTime(): df_CPU = df.read_sql('SELECT TOP(1) LastBootedAt FROM dbo.ServerMonitoring ORDER BY LoadDate DESC', conn_str) current_timestamp = datetime.now() up_time = current_timestamp - boot_time days = str(up_time).split(" days, ")[0] time = str(up_time).split(" days, ")[1] hours = time.split(':')[0] mins = time.split(':')[1] secs = time.split(':')[2] # if PC last rebooted in the last 9 hours if days == 0 and hours < 10: print('Server was rebooted {} hours and {} minutes ago'.format(hours, minutes))
That’s it folks! The Github repo is open for contribution so please do feel free to contribute or leave a comment below on how your solution turns out!
If you have any questions, issues or feedback, drop a comment below or reach out to jacqui.jm77@gmail.com