Part 2: Collect Cisco Syslog with Rsyslog and MySQL

Series overview | Previous: Open-Source Network Automation: Ubuntu Server Setup | Next: Automate Cisco Switchports with Ansible and Jinja2

Part 2 of 5: In Part 2, we create a local MySQL queue and configure rsyslog to retain only the Cisco events that can trigger an automation workflow.

Safety note: Use an isolated lab, replace every example value, protect all credentials, maintain console access, and back up configurations before allowing automated changes.

Configure MySQL

Begin by limiting MySQL to local connections because every component in this design runs on the same server.

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

In the [mysqld] section, set bind-address = 127.0.0.1. This prevents MySQL from listening on external interfaces.

Also disable symbolic links and local file loading if your installation supports these options and you do not require them. In nano, press Ctrl+O, then Enter to save, and Ctrl+X to exit.

symbolic-links=0
local_infile=0

Save the file, restart MySQL, and run the interactive security utility. Read each prompt rather than copying a fixed answer sequence because prompts can differ between MySQL versions.

sudo systemctl restart mysql
sudo mysql_secure_installation

Create a dedicated database and local service account. Replace REPLACE_WITH_A_LONG_RANDOM_PASSWORD before running the statements. Ubuntu commonly authenticates the local MySQL root account through the Unix socket, so changing the root authentication method is unnecessary for this design.

sudo mysql

# Create the rsyslog database
CREATE DATABASE IF NOT EXISTS rsyslog;

# Create a local-only service account
CREATE USER IF NOT EXISTS 'rsyslog_user'@'localhost' IDENTIFIED BY 'REPLACE_WITH_A_LONG_RANDOM_PASSWORD';

# Grant privileges on the rsyslog database
GRANT SELECT, INSERT, UPDATE, DELETE ON rsyslog.* TO 'rsyslog_user'@'localhost';

# Create the SystemEvents table
USE rsyslog;
CREATE TABLE IF NOT EXISTS SystemEvents (
  ID                 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Message            TEXT,
  Facility           VARCHAR(10),
  FromHost           VARCHAR(100),
  Priority           VARCHAR(10),
  DeviceReportedTime DATETIME,
  ReceivedAt         DATETIME,
  InfoUnitID         INT,
  SysLogTag          VARCHAR(100),
  EventSource        VARCHAR(60),
  InstanceID         INT,
  status             VARCHAR(50),
  completed_date     DATETIME
);

# Apply changes
FLUSH PRIVILEGES;
exit;

Rsyslog Configuration

This rules file stores only messages that can trigger one of the defined workflows. Replace the database password, restrict access to this file, and test the filters against sample messages from your exact switch releases.

sudo nano /etc/rsyslog.d/custom-rules.conf

# Purpose: Listen to syslog messages for specific patterns and execute scripts based on those patterns.
#Created by Matthew
#Last modified by Anthony
#
#Change Log
#7/30/2024 00:00 - Created file - Matthew
#7/31/2024 11:42 - Changed script names to make more sense - Anthony
#
#


#executes script to configure port for non 802.1x compliant devices
if ($msg contains '%DOT1X-5-FAIL:') and not ($msg contains '0892.04') then {
        *.* :ommysql:127.0.0.1,rsyslog,rsyslog_user,MySQL_Password
        stop
}
if ($msg contains 'Security violation occurred, caused by MAC address') then {
        *.* :ommysql:127.0.0.1,rsyslog,rsyslog_user,MySQL_Password
        stop
}
#executes script to revert port to 802.1x if someone removes a non 802.1x compliant device
if $msg contains 'psecure-violation' then {
        *.* :ommysql:127.0.0.1,rsyslog,rsyslog_user,MySQL_Password
        stop
}
if $msg contains 'BLOCK_BPDUGUARD' then {
        *.* :ommysql:127.0.0.1,rsyslog,rsyslog_user,MySQL_Password
        stop
}

Now we need to add the SQL module to the rsyslog.conf file.

sudo nano /etc/rsyslog.conf

Load the MySQL output module. The omprog module is not used by this version of the workflow and does not need to be enabled.

# Load the MySQL output module
module(load="ommysql")

We also need to enable listening for syslog messages on TCP/UDP port 514. If you are using a non-standard port, please configure it accordingly. Uncomment the lines below.

module(load="imudp")
input(type="imudp" port="514")
module(load="imtcp")
input(type="imtcp" port="514")

Whenever the above file is modified, you need to restart the service.

sudo systemctl restart rsyslog

Expected Result

At the end of this part, selected switch messages are written to the local SystemEvents table and unrelated messages are excluded from the automation queue.

Troubleshooting

• Validate rsyslog before restarting it with sudo rsyslogd -N1.
• Check reception with sudo tcpdump -ni any port 514 and confirm UFW permits the switch-management networks.
• Confirm the database account is local-only and that the password matches the rsyslog rule configuration.

Series overview | Previous: Open-Source Network Automation: Ubuntu Server Setup | Next: Automate Cisco Switchports with Ansible and Jinja2