CentOS 8
Sponsored Link

Rsyslog : Output Logs to Databases2020/02/10

 
Configure Rsyslog to output logs to Databases.
[1]
It's possible to select a database from some mainly used products, this example shows to configure with MariaDB, so Install and start MariaDB server, refer to here.
[2] Create a user and Database for Rsyslog.
[root@dlp ~]#
dnf -y install rsyslog-mysql
[root@dlp ~]#
mysql -u root -p < /usr/share/doc/rsyslog/mysql-createDB.sql

Enter password:
[root@dlp ~]#
mysql -u root -p

Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

# create [rsyslog] user and grant privileges to Syslog DB (set any password for 'password' section)
MariaDB [(none)]> grant all privileges on Syslog.* to rsyslog@'localhost' identified by 'password'; 
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges; 
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit
Bye
[3] Configure Rsyslog to output logs to database.
[root@dlp ~]#
vi /etc/rsyslog.conf
# line 39: add

module(load="ommysql")
# for example, output logs for [authpriv.*]

# how to wite ⇒ :ommysql:Host,DB,DBUser,DBPassword

authpriv.*    
:ommysql:localhost,Syslog,rsyslog,password
[root@dlp ~]#
systemctl restart rsyslog
[4] After configuration of above, some logs of kinds of authentication are recorded in Database like follows.
[root@dlp ~]#
mysql -u rsyslog -p Syslog

Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 25
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [Syslog]> show tables; 
+------------------------+
| Tables_in_Syslog       |
+------------------------+
| SystemEvents           |
| SystemEventsProperties |
+------------------------+
2 rows in set (0.000 sec)

MariaDB [Syslog]> select ReceivedAt,Facility,Priority,FromHost,Message from SystemEvents; 
+---------------------+----------+----------+----------+-------------------------------------------------------------------------+
| ReceivedAt          | Facility | Priority | FromHost | Message                                                                 |
+---------------------+----------+----------+----------+-------------------------------------------------------------------------+
| 2020-02-05 19:12:59 |       10 |        6 | dlp      |  Received signal 15; terminating.                                       |
| 2020-02-05 19:12:59 |       10 |        6 | dlp      |  Server listening on 0.0.0.0 port 22.                                   |
| 2020-02-05 19:12:59 |       10 |        6 | dlp      |  Server listening on :: port 22.                                        |
| 2020-02-05 19:14:26 |       10 |        6 | node01   |  Accepted password for cent from 10.0.0.51 port 48960 ssh2              |
| 2020-02-05 19:14:26 |       10 |        6 | node01   |  pam_unix(systemd-user:session): session opened for user cent by (uid=0)|
| 2020-02-05 19:14:26 |       10 |        6 | node01   |  pam_unix(sshd:session): session opened for user cent by (uid=0)        |
| 2020-02-05 19:14:26 |       10 |        6 | node01   |  Received disconnect from 10.0.0.51 port 48960:11: disconnected by user |
| 2020-02-05 19:14:26 |       10 |        6 | node01   |  Disconnected from user cent 10.0.0.51 port 48960                       |
| 2020-02-05 19:14:26 |       10 |        6 | node01   |  pam_unix(sshd:session): session closed for user cent                   |
+---------------------+----------+----------+----------+-------------------------------------------------------------------------+
30 rows in set (0.000 sec)
Matched Content