Today, we’re excited to share a powerful network automation solution using Ansible and Rsyslog. Customization possibilities are endless, limited only by your creativity.

Configuring Ubuntu

For this solution, we used Ubuntu Server 24.04.3 LTS. Below is a script to secure the server based on our setup, with notes on parts you may need to change for your environment. Currently, We run this on Nutanix AHV but have also tested it on VMware and Hyper-V without problems.

Hardening Server

Below is the provided file. Please review and modify it as needed before execution. We will supply the necessary commands to run the script. This setup is intended solely for testing purposes and is not recommended for production environments without implementing proper security measures.

Before running the script please review the first 30 or so lines to ensure you are configuring the system as you would like. There are some items that need customization such as SSH sources for UFW and IPv6 status.

sudo chmod +x ubuntuHarden.sh
sudo ./ubuntuHarden.sh

Configure UFW – Ubuntu Firewall to secure the server.

#Wipe UFW config if there is one
Sudo ufw reset

#Configure default incoming deny all and outgoing allow all
sudo ufw default deny incoming
sudo ufw default allow outgoing
#Allow SSH from client network - my pc
sudo ufw allow from 10.24.0.0/16 to any port 22 proto tcp
sudo ufw allow from 192.168.204.0/24 to any port 22 proto tcp
#Allow Syslog from switch network
sudo ufw allow from 10.24.224.0/19 to any port 514 proto udp
#Turn on UFW
sudo ufw enable

Check UFW status to verify configuration

sudo ufw status

Required Modules/Plugins

#Install Inotify
sudo apt install inotify-tools -y
#Install MySQL
sudo apt install mysql-server -y
#Install Ansible
sudo apt install ansible -y
sudo apt install python3-paramiko -y
#Install rsyslog
sudo apt install rsyslog -y
sudo apt install rsyslog-mysql -y
#when asked to do you want a database created say no

Configure MySQL

We will start with hardening the mysql installation. First we need to adjust some settings in the mysqld.cnf file.

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

If you only want the internal applications on the server to connect to the SQL server change the bind address to 127.0.0.1.

Second, we will disable symbolic links to protect against filesystem exploits and disable local infile, as it is unnecessary and helps prevent file read attacks. If these options are missing from the configuration file, add them at the end. * Note: Press “CTRL + S” to save and “CTRL + X” to exit nano.

symbolic-links=0
local_infile=0

After completing the above steps, save and exit nano. Next, run the MySQL secure installation command. Most of the prompts are straightforward. If you need help, feel free to ask in the comments. My responses were: Y, 2, Y, Y, Y, Y.

mysql_secure_installation

Next, we need to set the MySQL root password, which is blank by default. Afterward, we will create the database, set up the table, and configure a local user with access. Copy the following into Notepad and replace “My Super Strong Password” with your chosen password for both the root user and the rsyslog_user. Be sure to enclose the new password in quotes, as shown below.

sudo mysql

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'My super strong password';
FLUSH PRIVILEGES;

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

# Create the new user (replace % with localhost if only local access needed)
CREATE USER IF NOT EXISTS 'rsyslog_user'@'localhost' IDENTIFIED BY 'MY Super strong password 2';

# Grant privileges on the rsyslog database
GRANT ALL PRIVILEGES ON rsyslog.* TO 'rsyslog_user'@'%';

# 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;

Configure Ansible

First we will make a directory for our Ansible config file and playbooks.

sudo mkdir /etc/ansible
sudo mkdir /etc/ansible/playbooks
sudo touch /etc/ansible/host
sudo touch /etc/ansible/ansible.cfg
sudo touch /etc/ansible/DeviceMacs.txt
sudo touch /etc/ansible/OUIs.txt
sudo touch /etc/ansible/Wireless.txt
sudo mkdir /var/log/rsyslog

Create the ansible config file. To start we just need to add these two lines to /etc/ansible/ansible.cfg. * Note: Use “CTRL S” to save and “CTRL X” to exit nano.

sudo nano /etc/ansible/ansible.cfg

[defaults]
host_key_checking = False

Once this phase is complete, we will create playbooks to automate actions triggered by specific syslog messages received by the system. The initial playbook will manage switch port configurations based on MAC addresses. To facilitate this, we maintain three regularly updated files: one with full MAC addresses, including descriptions and assigned VLANs; a second containing OUI addresses alongside their descriptions and VLAN assignments; and a third exclusively for our wireless access point MAC addresses. Because our wireless access points require trunk ports, their MAC addresses are kept separate from those of other devices.

switch_port_config.yml

Notes: Update ansible_username and ansible_password to reflect the credentials needed to access your switches. To receive an email notification upon task completion, uncomment the relevant lines and provide the necessary details at the end of the file.

sudo nano /etc/ansible/playbooks/switch_port_config.yml

# Purpose: Ansible playbook to apply port configuration template to switch port indicated in syslog msg.
#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
#
#

#Populates variables from bash script for us in template
- name: Switchport Configuration Playbook
  hosts: localhost
  gather_facts: false
  vars:
    ip_address: ""
    macaddress: ""
    interface: ""
  tasks:
    - name: Print extracted details
      debug:
        msg: "IP Address: {{ ip_address }}, MAC Address: {{ macaddress }}, Interface: {{ interface }}"
    - name: Add Host to memory
      add_host:
        name: "{{ ip_address }}"
#Assigns which host to run below tasks on
- hosts: "{{ ip_address }}"
  gather_facts: false
  vars:
    ansible_connection: ansible.netcommon.network_cli
    ansible_network_os: cisco.ios.ios
    ansible_user: my_username
    ansible_password: my_password
  gather_facts: false
  tasks:
#Imports files for comparisons in template
    - name: Import File
      shell: cat /etc/ansible/DeviceMacs.txt
      register: deviceMacs
      delegate_to: localhost
    - name: Import File
      shell: cat /etc/ansible/OUIs.txt
      register: ouiMacs
      delegate_to: localhost
    - name: Import File
      shell: cat /etc/ansible/Wireless.txt
      register: wirelessMacs
      delegate_to: localhost
#Configures port based on template and variables.
    - name: Configure Port
      cli_command:
        command: "{{ lookup('template','./switch_port_config.j2') }}"
      register: portConfigResults
    - name: debug
      debug:
        var: "{{ lookup('template','./switch_port_config.j2') }}"
    - name: debug
      debug:
        var: portConfigResults.stdout_lines
#Email notification of changes
#    - name: Email each change
#      community.general.mail:
#        host: email server hostname
#        sender: AutoIT@domain.name
#        to:
#          - test@domain.name
#          - tech@domain.name
#        subject: "Ansible Configured a Switch port"
#        body:
#          - "Switch IP Address: {{ ip_address }}, Device MAC Address: {{ macaddress }}, Switch Interface: {{ interface }}"
#        subtype: html
#      delegate_to: localhost

To generate the precise commands to be entered on the switch, we create a Jinja2 template named switch_port_config.j2

sudo nano /etc/ansible/playbooks/switch_port_config.j2

## Purpose: Configures port template based on provided mac address and returns template to ansible playbook to be executed on given port.
##Created by Matthew
##Last modified by Anthony
##
##Change Log
##7/30/2024 00:00 - Created file - Matthew
##7/31/2024 11:42 - Changed file names to make more sense - Anthony
##
##

## compares full mac address to DeviceMacs.txt and templates as necessary
{% if macaddress in deviceMacs.stdout %}
    {% set matched_entry = deviceMacs.stdout_lines | select('search', '^' + macaddress + ',') | list | first %}
    {% set deviceMacVlan = matched_entry.split(',')[1] %}
    {% set deviceMacDescription = matched_entry.split(',')[2] %}
    config t
    default interface {{ interface }}
    interface {{ interface }}
    shutdown
    description {{ deviceMacDescription }}
    switchport access vlan {{ deviceMacVlan }}
    switchport mode access
    switchport port-security mac-address sticky
    switchport port-security
    storm-control broadcast level 5.00
    storm-control multicast level 5.00
    storm-control action shutdown
    spanning-tree portfast
    spanning-tree bpduguard enable
    spanning-tree guard root
    no shutdown
    end
    wri
## 
{% elif '1c7d.22' in macaddress %}
    config t
    default interface {{ interface }}
    interface {{ interface }}
    shutdown
    description Ansible_Xerox
    switchport access vlan 20
    switchport mode access
    switchport port-security mac-address sticky
    switchport port-security maximum 2
    switchport port-security
    storm-control broadcast level 5.00
    storm-control multicast level 5.00
    storm-control action shutdown
    spanning-tree portfast
    spanning-tree bpduguard enable
    spanning-tree guard root
    no shutdown
    end
    wri
## compares oui of mac address to ouis.txt and templates as necessary
{% elif macaddress[:7] in ouiMacs.stdout %}
    {% set matched_entry2 = ouiMacs.stdout_lines | select('search', '^' + macaddress[:7] + ',') | list | first %}
    {% set ouiMacVlan = matched_entry2.split(',')[1] %}
    {% set ouiMacDescription = matched_entry2.split(',')[2] %}
    config t
    default interface {{ interface }}
    interface {{ interface }}
    shutdown
    description {{ ouiMacDescription }}
    switchport access vlan {{ ouiMacVlan }}
    switchport mode access
    switchport port-security mac-address sticky
    switchport port-security
    storm-control broadcast level 5.00
    storm-control multicast level 5.00
    storm-control action shutdown
    spanning-tree portfast
    spanning-tree bpduguard enable
    spanning-tree guard root
    no shutdown
    end
    wri
##configures port for Wireless AP
{% elif macaddress[:7] in wirelessMacs.stdout %}
    config t
    default interface {{ interface }}
    interface {{ interface }}
    shutdown
    description Ansible_AP
    switchport trunk native vlan 420
    switchport trunk allowed vlan 20,420,520,1234
    switchport mode trunk
    power inline four-pair forced
    storm-control broadcast level 5.00
    storm-control multicast level 5.00
    storm-control action shutdown
    spanning-tree bpduguard enable
    spanning-tree guard root
    spanning-tree portfast trunk
    no shutdown
    end
    wri
{% else %}
    skip_mac_address_not_found
{% endif %}

The playbook above configures port security on all specified ports except wireless access points. To resolve issues caused by ports being disabled due to port security, we created a playbook that resets the port to 802.1x and applies security settings.

sudo nano /etc/ansible/playbooks/resetdot1x.yml

# Purpose: Playbook resets interface for 802.1x operations after an err-disable syslog message from switch
#Created by: Anthony
#Last modified by:
#Logging to: /var/log/ansible_playbook_output.log
#
#Change Log
#7/30/2024 00:00 - Created file
#
#
#
---
#Configures the variables to be used and logs them to the screen/logfile refer to /var/log/ansible_playbook_output.log
- name: Playbook for rsyslog integration
  hosts: localhost
  vars:
    ip_address: ""
    macaddress: ""
    interface: ""
  tasks:
    - name: Print extracted details
      debug:
        msg: "IP Address: {{ ip_address }}, MAC Address: {{ macaddress }}, Interface: {{ interface }}"
    - name: Add Host to memory
      add_host:
        name: "{{ ip_address }}"
# sets the target machine for play to be executed on
- hosts: "{{ ip_address }}"
  vars:
    ansible_connection: ansible.netcommon.network_cli
    ansible_network_os: cisco.ios.ios
    ansible_user: switch_username
    ansible_password: switch_password
  gather_facts: false
  tasks:
# Uses the provided variables and apply a configuration template to the port.
    - name: Configure Port
      cli_command:
        command: "{{ lookup('template','./resetdot1x.j2') }}"
    - name: debug
      debug:
        msg: "{{ lookup('template','./resetdot1x.j2') }}"

This is the jinja2 template to apply the port configurations for the reset playbook.

sudo nano /etc/ansible/playbooks/resetdot1x.j2

## Purpose: Configure switchport for 802.1x operations
##Created by: Anthony
##Last modified by:
##
##Change Log
##7/30/2024 00:00 - Created file
##
##
##


config t
default interface {{ interface }}
interface {{ interface }}
shutdown
switchport mode access
switchport voice vlan 120
load-interval 60
authentication event server dead action authorize vlan 999
authentication event no-response action authorize vlan 999
authentication event server alive action reinitialize
authentication order dot1x
authentication priority dot1x
authentication port-control auto
authentication periodic
authentication violation replace
no snmp trap link-status
dot1x pae authenticator
dot1x timeout quiet-period 2
dot1x timeout tx-period 3
storm-control broadcast level 5.00
storm-control multicast level 5.00
storm-control action shutdown
spanning-tree portfast
spanning-tree bpduguard enable
spanning-tree guard root
no shutdown
end
wri

The final playbook we developed is designed to notify us immediately whenever a switch port is disabled by BPDU Guard. It automatically disables the port permanently and sends an email alert to keep us informed of the action.

sudo nano /etc/ansible/playbooks/bpduguardDisable.yml

---
# Purpose: Ansible playbook to disable ports that are err-diable due to bpduguard indicated in syslog msg.
#Created by Anthony
#Last modified by Anthony
#
#Change Log
#11/20/2024 7:34AM - Created file - Anthony
#
#
#

#Populates variables from bash script for us in template
- name: Playbook for BPDUGuard port disable and report
  hosts: localhost
  gather_facts: false
  vars:
    ip_address: ""
    macaddress: ""
    interface: ""
  tasks:
    - name: Print extracted details
      debug:
        msg: "IP Address: {{ ip_address }}, MAC Address: {{ macaddress }}, Interface: {{ interface }}"
    - name: Add Host to memory
      add_host:
        name: "{{ ip_address }}"
#Assigns which host to run below tasks on
- hosts: "{{ ip_address }}"
  gather_facts: false
  vars:
    ansible_connection: ansible.netcommon.network_cli
    ansible_network_os: cisco.ios.ios
    ansible_user: switch_username
    ansible_password: switch_password
  gather_facts: false
  tasks:
#Configures port based on template and variables.
    - name: Configure Port
      cli_command:
        command: "{{ lookup('template','./bpduguardDisable.j2') }}"
#Email notification of changes
    - name: Email on disabling of port
      community.general.mail:
        host: mail_server
        sender: AutoIT@domain.local
        to:
          - test@domain.local
          - tech@domain.local
        subject: "Ansible DISABLED a Switch port"
        body:
          - "IP Address: {{ ip_address }}, Interface: {{ interface }} has been disabled due to bpduguard"
        subtype: html
      delegate_to: localhost

Following is the jinja2 template for the commands.

sudo nano /etc/ansible/playbooks/bpduguardDisable.j2

## Purpose: Disables port due to bpduguard and alerts via email.
##Created by Anthony
##
##
##Change Log
##11/20/24 07:38AM - Created file - Anthony
##
##
##

## Commands to disable port
config t
interface {{ interface }}
shutdown
description "Port Disabled due to BPDUGuard"
end
wri

Feel free to use the above templates to create your own playbooks for activation via syslog messages from your switches. Next, we will proceed to develop the next component of the application.

Rsyslog Configuration

The following file defines the syslog messages that are critical and must be retained for playbook execution. Please update the file with the accurate MySQL table name, username, and password.

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

Add the below line just above “module(load=”imuxsock………….”

module(load="omprog")
# loads the mysql module
module(load="ommysql") # Load the MySQL module

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

Inotify Configuration

The next step is to set up a service that continuously scans the MySQL table to identify log messages requiring processing. This service queries the table every 5 seconds, retrieves all entries needing action, and processes them sequentially. If a message does not meet any processing criteria, the service deletes it from the table and moves on to the next. To keep these messages instead of deleting them, simply comment out the deletion line in the script.

sudo nano /usr/local/bin/monitor_logs.sh

Paste the following into Notepad and update the MYSQL_PASS to the password you set for the rsyslog_user.

#!/bin/bash

# MySQL connection details
MYSQL_USER="rsyslog_user"
MYSQL_PASS="My Super Strong Password"
MYSQL_DB="rsyslog"
MYSQL_HOST="127.0.0.1" # or 'localhost'
LOG_FILE="/var/log/inotify.log"
Device_Log_File_Path="/var/log/rsyslog/"

# MySQL query to get rows where status is blank (NULL or empty)
QUERY="SELECT CONCAT(id,',',FromHost,',',Message) AS formatted_row FROM SystemEvents WHERE status IS NULL OR status = '';"

# Loop to continuously check the database
while true; do
    # Query MySQL for rows where status is blank
    rows=$(mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" -D "$MYSQL_DB" -N -e "$QUERY")

    if [ ! -z "$rows" ]; then
        echo "Found rows with blank status. Executing Ansible playbook for each row..." >>"$LOG_FILE"
        # Mac Address Lists
        macOuiList="/etc/ansible/OUIs.txt"
        macDeviceList="/etc/ansible/DeviceMacs.txt"
        macUnknownList="/var/logs/rsyslog/unknowndevices.log"
        wirelessList="/etc/ansible/Wireless.txt"
        # Loop through each row and execute the playbook
        while read -r row; do
            echo "$row" >>"$LOG_FILE"
            # Extract the row details (id and msg)
            #id=$(echo "$row" | awk '{print $1}')
            #msg=$(echo "$row" | awk '{print $2}')
            IFS=',' read -r id ip_address msg <<<"$row"
            # Print the current row being processed
            echo "Processing row ID: $id with message: $msg from $ip_address" >>"$LOG_FILE"
            # Extract IP address, MAC address, and interface using regex
            macaddress=$(echo "$msg" | grep -oP '(\w{4}\.\w{4}\.\w{4})')
            interface=$(echo "$msg" | grep -oE '\b(FiveGigabitEthernet|TenGigabitEthernet|GigabitEthernet|Gi|Fi|Te)[0-9]+/[0-9]+/[0-9]{1,2}+\b' | head -n 1)
            dt=$(date '+%m/%d/%Y %H:%M:%S')
            echo "current data is: $ip_address,$macaddress,$interface" >>"$LOG_FILE"
            # Verify device is known and port should be configured. If not remove sql entry.
            if [[ "$msg" == *"psecure-violation"* ]] || [[ "$msg" == *"Security violation occurred"* ]]; then
                echo "$dt: Port: $interface disabled on $ip_address running dot1xreset script" >>"$LOG_FILE"
                # Trigger the Ansible playbook (modify as needed)
                ansible-playbook /etc/ansible/playbooks/resetdot1x.yml -e "ip_address=$ip_address macaddress=$macaddress interface=$interface sql_id=$id" >>"$Device_Log_File_Path/portsecure.log" 2>&1
                # After execution, update the row's status to 'processed'
                mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" -h "$MYSQL_HOST" -D "$MYSQL_DB" -e "UPDATE SystemEvents SET status='started-reset-dot1x-playbook' WHERE id=$id;"
                echo "Row ID: $id processed successfully!" >>"$LOG_FILE"
            elif [[ "$msg" == *"BLOCK_BPDUGUARD"* ]]; then
                echo "$dt: Port: $interface will be disabled" >>"$LOG_FILE"
                #Trigger the Ansible playbook
                ansible-playbook /etc/ansible/playbooks/bpduguardDisable.yml -e "ip_address=$ip_address interface=$interface $sql_id=$id" >>"$Device_Log_File_Path/bpduportdisable.log" 2>&1
                mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" -h "$MYSQL_HOST" -D "$MYSQL_DB" -e "UPDATE SystemEvents SET status='started-port-disable-script-bpdu' WHERE id=$id;"
                echo "Row ID: $id processed successfully!" >>"$LOG_FILE"
            elif grep -qi "${macaddress:0:7}" "$macOuiList" || grep -qi "$macaddress" "$macDeviceList"; then
                # Execute the Ansible playbook for the current row (you can pass extra vars if needed)
                if [[ "$msg" == *"DOT1X-5-FAIL"* ]]; then
                    echo "$dt: Port: $interface in guest vlan on $ip_address running port_config script" >>"$LOG_FILE"
                    # Trigger the Ansible playbook (modify as needed)
                    ansible-playbook /etc/ansible/playbooks/switch_port_config.yml -vvvv -e "ip_address=$ip_address macaddress=$macaddress interface=$interface sql_id=$id" >>"$Device_Log_File_Path/$macaddress.log" 2>&1
                    # After execution, update the row's status to 'processed'
                    mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" -h "$MYSQL_HOST" -D "$MYSQL_DB" -e "UPDATE SystemEvents SET status='started-switchport-config-playbook' WHERE id=$id;"
                    echo "Row ID: $id processed successfully!" >>"$LOG_FILE"
                else
                    echo "dt: Port: $interface on $ip_address did not match a rule with message: $msg" >>"$LOG_FILE"
                    mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" -h "$MYSQL_HOST" -D "$MYSQL_DB" -e "UPDATE SystemEvents SET status='Did not meet any playbook requirements.' WHERE id=$id;"
                fi

            elif grep -qi "${macaddress:0:7}" "$wirelessList"; then
                # Execute the Ansible playbook for the current row (you can pass extra vars if needed)
                if [[ "$msg" == *"DOT1X-5-FAIL"* ]]; then
                    echo "$dt: Port: $interface in guest vlan on $ip_address running port_config script" >>"$LOG_FILE"
                    # Trigger the Ansible playbook (modify as needed)
                    ansible-playbook /etc/ansible/playbooks/switch_port_config.yml -vvvv -e "ip_address=$ip_address macaddress=$macaddress interface=$interface sql_id=$id" >>"$Device_Log_File_Path/$macaddress.log" 2>&1
                    # After execution, update the row's status to 'processed'
                    mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" -h "$MYSQL_HOST" -D "$MYSQL_DB" -e "UPDATE SystemEvents SET status='started-switchport-config-playbook' WHERE id=$id;"
                    echo "Row ID: $id processed successfully!" >>"$LOG_FILE"

                else
                    echo "dt: Port: $interface on $ip_address did not match a rule with message: $msg" >>"$LOG_FILE"
                    mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" -h "$MYSQL_HOST" -D "$MYSQL_DB" -e "UPDATE SystemEvents SET status='Did not meet any playbook requirements.' WHERE id=$id;"
                fi
            else
                if grep -qi "$macaddress" "$macUnknownList"; then
                        echo "done"
                else
                        echo "$macaddress does not exist in OUIs.txt or DeviceMacs.txt" >> "$macUnknownList"
                fi
                mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" -h "$MYSQL_HOST" -D "$MYSQL_DB" -e "Delete FROM SystemEvents WHERE id=$id;"
            fi
        done <<<"$rows"
    else
        echo "No rows with blank status found. Waiting for updates..."
    fi

    # Wait for 60 seconds before checking again (can be adjusted)
    sleep 5
done

We need to update the file permissions to make it executable.

sudo chmod +x /usr/local/bin/monitor_logs.sh

After the script is finalized, we will transform it into a service that automatically restarts each time the server reboots.

sudo nano /etc/systemd/system/monitor_logs.service

[Unit]
Description=Monitor logs for Ansible trigger
After=network.target

[Service]
ExecStart=/usr/local/bin/monitor_logs.sh
Restart=always

[Install]
WantedBy=multi-user.target

Now we will start the service and check it’s status. If there are any errors a quick Google or ChatGBT session will usually help.

sudo systemctl start monitor_logs.service
sudo systemctl status monitor_logs.service

Once we know there are no errors lets set this service to start on boot

sudo systemctl daemon-reload
sudo systemctl enable monitor_logs.service

Testing

Now, you need to populate the three files with MAC address data. Below are examples demonstrating the correct format for each file to ensure proper processing. Be sure to format the MAC addresses according to your switch vendor’s specifications. The example shown follows Cisco’s standard format.

/etc/ansible/Wireless.txt
mac address only
d04f.58

/etc/ansible/OUIs.txt
mac address oui, vlan, description
0002.c1,620,Ansible_IED

/etc/ansible/DeviceMacs.txt
mac address, vlan, description
744d.bd7e.9f88,20,Ansible_HPPrint

Configure your switches to enable 802.1x authentication on the ports. Set the logging host to your Ubuntu Server and adjust the logging level to informational. When a device fails 802.1x authentication, it will send a syslog message to the Ubuntu server, which will process it using the provided MAC address lists. When device is moved, the port will be disabled due to port security. The syslog message will notify the Ubuntu server, which will reset the original port to 802.1x and configure the new port for the device.

Conclusion

We understand this may seem extensive, but this solution has saved us a tremendous amount of time—especially during a complete switch refresh. It enabled us to simply plug in devices without the need to configure each port individually. We hope you find this helpful. This is just the beginning of the project; we plan to add a user interface that can generate playbooks based on user input and provide easier log viewing.