When you operate a server, you're faced with a deluge of logs. These logs are essential assets for understanding system health, tracing the cause of problems, and detecting security threats. However, in a default setup, logs are scattered as text files in the /var/log directory, making it difficult to search for specific information, generate statistics, or derive meaningful insights. To solve this, the concept of a "centralized logging system" was born.
Today, we will delve into how to use rsyslog, the powerful log processing system that comes pre-installed on Ubuntu, to go beyond simple file storage. We will learn how to selectively filter logs and systematically store them in a relational database (MySQL/MariaDB). Through this process, you will take the first step in transforming your scattered logs into a powerful data asset.
By the time you finish this article, you will be able to:
- Understand rsyslog's modular system and install the database integration module.
- Set up a dedicated database and user account for log storage.
- Use rsyslog's basic and advanced filtering rules (RainerScript) to precisely select the logs you need.
- Configure rsyslog to insert filtered logs into a database in real-time.
- Verify that your configuration is working correctly and troubleshoot common issues.
This guide isn't just about the technical steps of putting logs into a DB; it's about providing insight into how you can efficiently manage logs from large-scale systems and build a foundation for analysis. Now, let's breathe new life into the logs sleeping in your text files.
Prerequisites: What You'll Need
Before we dive in, let's ensure you have everything you need for a smooth process.
- An Ubuntu Server: You'll need a server running Ubuntu 18.04 LTS, 20.04 LTS, 22.04 LTS, or a newer version. This guide can also be adapted for most Debian-based Linux distributions.
- Sudo Privileges: You will need an account with
sudo access to install packages and modify system configuration files.
- A Database of Choice: This guide will use MariaDB as the example, as it's a widely used open-source database. The process is nearly identical for MySQL. If you prefer PostgreSQL, you'll just need to change the relevant package name (e.g., to
rsyslog-pgsql).
- Basic Linux Command-Line Knowledge: We'll assume you're comfortable with basic commands like
apt, systemctl, and using a text editor such as nano or vim.
If you're all set, let's begin with our first step: installing the database and the rsyslog module.
Step 1: Install the Database and rsyslog Module
For rsyslog to send logs to a database, it needs a "translator" module that allows it to "speak" with the database. For MariaDB/MySQL, a package named rsyslog-mysql fills this role. We also need to install the database server itself to store the logs.
1.1. Install MariaDB Server
If you already have a database server running, you can skip this step. If you're starting fresh, install the MariaDB server by entering the following commands in your terminal:
sudo apt update
sudo apt install mariadb-server -y
Once the installation is complete, the MariaDB service will start automatically. You can confirm it's running correctly with this command:
sudo systemctl status mariadb
If the output includes a line like active (running), the installation and startup were successful.
1.2. Install the rsyslog MySQL Module
Now, let's install the rsyslog-mysql package so rsyslog can communicate with MariaDB. This package provides the ommysql output module.
sudo apt install rsyslog-mysql -y
The installation is quick and straightforward. This single small package is the key that extends rsyslog's capabilities beyond the filesystem.
Step 2: Set Up the Database for Log Storage
Next, we need to create a "warehouse" for our logs. For security purposes, it's a best practice to create a dedicated database and user for rsyslog. This prevents the rsyslog user from affecting other databases on the server.
2.1. Connect to MariaDB and Secure It
First, log in to MariaDB as the root user.
sudo mysql -u root
If this is a new installation, it's highly recommended to run the security script. The mysql_secure_installation script will guide you through setting a root password, removing anonymous users, and more.
sudo mysql_secure_installation
2.2. Create the Database and User
From the MariaDB prompt (MariaDB [(none)]>), execute the following SQL queries to create a database and a user for rsyslog.
1. Create the database: We'll create a database named `Syslog` to store the logs.
CREATE DATABASE Syslog CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
2. Create a user and grant privileges: We'll create a user named `rsyslog_user` and give it full permissions on the `Syslog` database only. Be sure to replace `'your-strong-password'` with a real, strong password.
CREATE USER 'rsyslog_user'@'localhost' IDENTIFIED BY 'your-strong-password';
GRANT ALL PRIVILEGES ON Syslog.* TO 'rsyslog_user'@'localhost';
3. Apply changes: Flush the privileges to apply the changes immediately.
FLUSH PRIVILEGES;
4. Exit: Leave the MariaDB prompt.
EXIT;
2.3. Create the Log Table Schema
rsyslog expects a specific table structure to store logs. Fortunately, the rsyslog-mysql package includes a SQL script to create this predefined schema. All we have to do is execute this script on the `Syslog` database we just created.
The script file is typically located in the /usr/share/doc/rsyslog-mysql/ directory. Use the following command to apply it to the `Syslog` database.
sudo mysql -u rsyslog_user -p Syslog < /usr/share/doc/rsyslog-mysql/createDB.sql
You will be prompted for the password for the `rsyslog_user` you set earlier. Enter it correctly. The command should complete without any output, which is normal.
To verify, you can check which tables were created in the `Syslog` database.
sudo mysql -u rsyslog_user -p -e "USE Syslog; SHOW TABLES;"
If the output shows two tables, SystemEvents and SystemEventsProperties, your database setup is complete. The SystemEvents table is where all your logs will be stored.
Step 3: Configure rsyslog - Filtering and DB Integration
This is the most critical step. We will modify rsyslog's configuration to filter logs based on specific criteria and send the matching ones to our MariaDB database. rsyslog's configuration is managed through /etc/rsyslog.conf and files ending in .conf within the /etc/rsyslog.d/ directory. To keep the main system configuration clean and make maintenance easier, we'll create a new configuration file in the /etc/rsyslog.d/ directory.
Let's create a new file named 60-mysql.conf.
sudo nano /etc/rsyslog.d/60-mysql.conf
Inside this file, we will write instructions telling rsyslog what to send, how to send it, and where to send it.
3.1. Core Concept: RainerScript
Modern versions of rsyslog use an advanced, script-based configuration syntax called RainerScript. It offers far more flexibility and power for filtering and control than the older facility.priority format. We will use RainerScript to create our filtering rules.
Filtering in RainerScript generally follows an if ... then ... structure.
if <condition> then {
<action to perform>
}
The 'condition' is built based on various properties of a log message (e.g., program name, hostname, message content), and the 'action' defines what to do with that log, such as saving it to a file, forwarding it to another server, or, in our case, inserting it into a database.
3.2. Configuration: Sending All Logs to the DB (Basic)
First, let's start with the simplest configuration: sending all logs to the database without any filtering. This will help us confirm that the database connection is working correctly. Enter the following content into your 60-mysql.conf file.
# #####################################################################
# ## Configuration to send logs to MySQL/MariaDB ##
# #####################################################################
# 1. Load the ommysql module.
# This line tells rsyslog how to communicate with a MySQL database.
module(load="ommysql")
# 2. Define an action to send all logs (*) to the database.
# Format: *.* action(type="ommysql" server="server_address" db="database_name"
# uid="username" pwd="password")
#
# IMPORTANT: Replace 'your-strong-password' below with the actual DB password you set in Step 2.
action(
type="ommysql"
server="127.0.0.1"
db="Syslog"
uid="rsyslog_user"
pwd="your-strong-password"
)
This configuration is quite intuitive:
module(load="ommysql"): Activates the MySQL module.action(...): Instructs rsyslog to perform the specified action for all logs (implied since there's no filter).
type="ommysql": Specifies that the action is to write to a MySQL DB.server, db, uid, pwd: You must enter the exact database connection details you configured in Step 2.
3.3. Configuration: Applying Filters (The Core Task)
Now, let's implement the core topic of this guide: filtering. Storing every single log in the database generates a massive amount of data, wastes storage, and makes it harder to find important information. We will add rules to store only the logs that meet specific criteria.
For example, let's say our requirement is: "I want to store only SSH (sshd) logs and kernel messages with a severity of 'warning' or higher in the database."
Modify or replace the content of your 60-mysql.conf file with the following:
# #####################################################################
# ## Configuration to filter logs and send them to MySQL/MariaDB ##
# #####################################################################
# 1. Load the ommysql module
module(load="ommysql")
# 2. Define filtering rules and the DB storage action
# We use the RainerScript if-then syntax.
if ( \
# Condition 1: If the program name is 'sshd'
$programname == 'sshd' \
or \
# Condition 2: If the program name is 'kernel' AND
# the log severity (syslogseverity) is 4 ('warning') or less
# (Severity is numeric, lower numbers are more severe: 0=emerg, 1=alert, 2=crit, 3=err, 4=warning)
($programname == 'kernel' and $syslogseverity <= 4) \
) then {
# The action below will only be executed for logs that match the above conditions.
action(
type="ommysql"
server="127.0.0.1"
db="Syslog"
uid="rsyslog_user"
pwd="your-strong-password"
)
# The 'stop' command prevents this log from being processed by any subsequent rules.
# This can be useful to prevent duplicate logging (e.g., to both DB and /var/log/syslog).
# We'll keep it commented out to ensure logs are still written to default files.
# stop
}
The core of this configuration is the if (...) then { ... } block:
$programname: An internal rsyslog variable (property) that holds the name of the process/program that generated the log.$syslogseverity: A variable representing the log's severity as a number (0: Emergency, 1: Alert, ..., 6: Informational, 7: Debug).==, or, and, <=: You can use familiar comparison and logical operators, just like in a programming language, to build complex conditions.action(...): This action is now conditional and will only apply to logs that pass the if statement.
More Filtering Examples:
- Store only logs containing a specific message (e.g., 'Failed password'):
if $msg contains 'Failed password' then { ... }
- Store only logs from a specific host:
if $hostname == 'web-server-01' then { ... }
- Store everything except CRON job logs:
if not ($programname == 'CRON') then { ... }
As you can see, RainerScript allows you to implement almost any log filtering scenario imaginable. Feel free to modify and combine conditions to fit your system's environment and monitoring goals.
Step 4: Apply and Verify the Configuration
Once you've finished writing the configuration file, it's time to make rsyslog read the new settings and verify that everything is working as expected.
4.1. Check Configuration Syntax
Before restarting the service, it's a good practice to check your configuration file for syntax errors. Restarting with a broken config could cause rsyslog to fail. Run the following command to perform a syntax check:
sudo rsyslogd -N1
If you see a message like "rsyslogd: version ..., config validation run (level 1), master config /etc/rsyslog.conf OK." and no errors, your syntax is correct. If there are errors, the message will point to the file and line number that needs fixing.
4.2. Restart the rsyslog Service
With the syntax check passed, restart the rsyslog service to apply the new configuration.
sudo systemctl restart rsyslog
After restarting, check the service's status to ensure it's running correctly.
sudo systemctl status rsyslog
Look for the active (running) state and carefully check for any error messages in the output.
4.3. Check the Database
The most definitive way to verify your setup is to check if logs are actually appearing in the database.
Try to generate some logs that match your filter rules. For instance, attempt an SSH login (either successful or failed) or reboot the system to generate kernel messages. After waiting a moment, connect to MariaDB and query the SystemEvents table.
sudo mysql -u rsyslog_user -p
Once connected to the DB, run the following query:
USE Syslog;
SELECT ID, ReceivedAt, FromHost, SysLogTag, Message FROM SystemEvents ORDER BY ID DESC LIMIT 10;
This query displays the 10 most recently stored logs. If you see logs related to SSH (sshd) or the kernel in the table, your configuration is working successfully! If you don't see any data, refer to the troubleshooting section below.
Troubleshooting
If logs aren't appearing in the database after configuration, check the following:
- Check rsyslog Status and Logs: Run
sudo systemctl status rsyslog or sudo journalctl -u rsyslog to check for error messages from rsyslog itself. Look for messages about DB connection failures, like "cannot connect to mysql server."
- Verify DB Connection Info: Double-check that the database name, username, password, and server address in your
60-mysql.conf file are perfectly correct. A typo in the password is a very common mistake.
- Check Firewall: If rsyslog and the database are on different servers, ensure that the firewall (e.g.,
ufw, iptables) is allowing connections on the database port (default 3306).
- Check Filter Conditions: Make sure your filter conditions are not too strict, which might result in no logs currently matching them. For testing, you can temporarily remove the filter condition and use a simple all-logs (
*.*) configuration to first confirm if the DB connection itself is the issue.
- SELinux/AppArmor: In rare cases, security modules like SELinux or AppArmor might be blocking rsyslog's network connections. Check the relevant logs (
/var/log/audit/audit.log or /var/log/syslog) for permission denied messages.
Conclusion and Next Steps
Congratulations! You have successfully built a system to filter logs in real-time on your Ubuntu server and store them in a database. You've transformed what was once a mere list of text files into structured data that can be queried, sorted, and aggregated using SQL. This is a critical foundation for elevating your system monitoring, security analysis, and incident response capabilities to the next level.
But don't stop here. You can take this even further:
- Log Visualization: Connect dashboard tools like Grafana or Metabase to your database to visually analyze your log data. You can create charts for error trends over time, maps of login attempt IPs, and more.
- Use Advanced Templates: rsyslog's templating feature allows you to completely customize the format of logs stored in the database. This enables advanced use cases, like extracting specific information into separate columns.
- Expand to Centralized Logging: Configure multiple servers to forward their logs to a central rsyslog server. This central server can then handle the filtering and database insertion, creating an enterprise-wide log management system.
The filtering and DB integration features of rsyslog you've learned today are just the beginning. rsyslog is an incredibly flexible and powerful tool. I encourage you to explore the official documentation and build even more sophisticated log management pipelines tailored to your specific environment.
