Extract SNMP and Syslog Data from InfluxDB

After setting up our tools for monitoring in the first series of our Machine Learning Project we have gathers a lot of data. The next step is to query InfluxDB for the relevant SNMP metrics and syslog events. We’ll use Python with the InfluxDB client to connect and extract data into Pandas DataFrames.

Set Up the InfluxDB Client Connection:

Copied!from influxdb_client import InfluxDBClient
import pandas as pd

# InfluxDB connection details
client = InfluxDBClient(url="http://localhost:8086", token="your-token", org="your-org")
query_api = client.query_api()
from influxdb_client import InfluxDBClient
import pandas as pd

# InfluxDB connection details
client = InfluxDBClient(url="http://localhost:8086", token="your-token", org="your-org")
query_api = client.query_api()

Define Queries for SNMP and Syslog Data: We’ll extract relevant SNMP metrics like CPU load, network I/O, and syslog events with error and warning severities.

Copied!# SNMP Metrics Query (e.g., CPU load, network in/out)
snmp_query = '''
from(bucket: "snmp_monitoring")
  |> range(start: -30d)
  |> filter(fn: (r) => r._measurement == "cpu_load" or r._measurement == "network_in" or r._measurement == "network_out")
'''
snmp_data = query_api.query_data_frame(snmp_query)

# Syslog Events Query (e.g., errors and warnings)
syslog_query = '''
from(bucket: "syslog_data")
  |> range(start: -30d)
  |> filter(fn: (r) => r._measurement == "syslog" and (r.severity == "error" or r.severity == "warning"))
'''
syslog_data = query_api.query_data_frame(syslog_query)
# SNMP Metrics Query (e.g., CPU load, network in/out)
snmp_query = '''
from(bucket: "snmp_monitoring")
  |> range(start: -30d)
  |> filter(fn: (r) => r._measurement == "cpu_load" or r._measurement == "network_in" or r._measurement == "network_out")
'''
snmp_data = query_api.query_data_frame(snmp_query)

# Syslog Events Query (e.g., errors and warnings)
syslog_query = '''
from(bucket: "syslog_data")
  |> range(start: -30d)
  |> filter(fn: (r) => r._measurement == "syslog" and (r.severity == "error" or r.severity == "warning"))
'''
syslog_data = query_api.query_data_frame(syslog_query)

Step 2: Clean and Standardize the Data

Convert Timestamps: Set the timestamp as the index for both DataFrames, making it easier to align data points over time.

Copied!# Set timestamp as index for SNMP data
snmp_data['timestamp'] = pd.to_datetime(snmp_data['_time'])
snmp_data.set_index('timestamp', inplace=True)

# Set timestamp as index for syslog data
syslog_data['timestamp'] = pd.to_datetime(syslog_data['_time'])
syslog_data.set_index('timestamp', inplace=True)
# Set timestamp as index for SNMP data
snmp_data['timestamp'] = pd.to_datetime(snmp_data['_time'])
snmp_data.set_index('timestamp', inplace=True)

# Set timestamp as index for syslog data
syslog_data['timestamp'] = pd.to_datetime(syslog_data['_time'])
syslog_data.set_index('timestamp', inplace=True)

Select Relevant Columns: We only need specific columns (_value and _measurement), which we’ll pivot to have each measurement as a separate column.

Copied!# Pivot SNMP data for CPU load, network in, and network out
snmp_data = snmp_data[['_value', '_measurement']].pivot(columns='_measurement', values='_value')

# Keep only relevant fields in syslog data
syslog_data = syslog_data[['_value', 'severity']]
# Pivot SNMP data for CPU load, network in, and network out
snmp_data = snmp_data[['_value', '_measurement']].pivot(columns='_measurement', values='_value')

# Keep only relevant fields in syslog data
syslog_data = syslog_data[['_value', 'severity']]

Remove Duplicates and Handle Missing Values: Duplicate entries and missing values can distort our model, so we’ll clean them up

Copied!# Remove duplicates in SNMP and syslog data
snmp_data = snmp_data.drop_duplicates()
syslog_data = syslog_data.drop_duplicates()

# Fill any missing values with interpolation or zeroes
snmp_data.fillna(method='ffill', inplace=True)
syslog_data.fillna(0, inplace=True)
# Remove duplicates in SNMP and syslog data
snmp_data = snmp_data.drop_duplicates()
syslog_data = syslog_data.drop_duplicates()

# Fill any missing values with interpolation or zeroes
snmp_data.fillna(method='ffill', inplace=True)
syslog_data.fillna(0, inplace=True)

Step 3: Feature Engineering for Model Training

Create Derived Features: We can create useful features like the rate of change for each metric, which can help detect sudden increases or drops in load.

Copied!# Calculate rate of change for SNMP metrics
snmp_data['cpu_load_rate'] = snmp_data['cpu_load'].diff()
snmp_data['network_in_rate'] = snmp_data['network_in'].diff()
snmp_data['network_out_rate'] = snmp_data['network_out'].diff()
# Calculate rate of change for SNMP metrics
snmp_data['cpu_load_rate'] = snmp_data['cpu_load'].diff()
snmp_data['network_in_rate'] = snmp_data['network_in'].diff()
snmp_data['network_out_rate'] = snmp_data['network_out'].diff()

Aggregate Syslog Events by Time Interval: Syslog messages can be grouped by time intervals (e.g., hourly) to create features based on the count of errors or warnings over that period.

Copied!# Resample syslog data to hourly and count errors/warnings
syslog_data['error_count'] = syslog_data['severity'].apply(lambda x: 1 if x == 'error' else 0).resample('H').sum()
syslog_data['warning_count'] = syslog_data['severity'].apply(lambda x: 1 if x == 'warning' else 0).resample('H').sum()
# Resample syslog data to hourly and count errors/warnings
syslog_data['error_count'] = syslog_data['severity'].apply(lambda x: 1 if x == 'error' else 0).resample('H').sum()
syslog_data['warning_count'] = syslog_data['severity'].apply(lambda x: 1 if x == 'warning' else 0).resample('H').sum()

Step 4: Combine and Normalize the Data

Merge DataFrames: Combine the SNMP and syslog DataFrames based on timestamps.

Copied!combined_data = snmp_data.join(syslog_data, how='outer').fillna(0)
combined_data = snmp_data.join(syslog_data, how='outer').fillna(0)

Normalize the Data: Standardize the data to ensure consistent scaling for model training.

Copied!from sklearn.preprocessing import StandardScaler

scaler = StandardScaler()
combined_data[['cpu_load', 'network_in', 'network_out', 'cpu_load_rate', 'network_in_rate', 'network_out_rate']] = scaler.fit_transform(
    combined_data[['cpu_load', 'network_in', 'network_out', 'cpu_load_rate', 'network_in_rate', 'network_out_rate']]
)
from sklearn.preprocessing import StandardScaler

scaler = StandardScaler()
combined_data[['cpu_load', 'network_in', 'network_out', 'cpu_load_rate', 'network_in_rate', 'network_out_rate']] = scaler.fit_transform(
    combined_data[['cpu_load', 'network_in', 'network_out', 'cpu_load_rate', 'network_in_rate', 'network_out_rate']]
)

Next Steps

With this prepared dataset, we can now proceed to the model training phase. By structuring and cleaning the data from SNMP and syslog sources, we’re ready to train a model that can provide predictive insights and alert us to potential system failures before they happen.