Rsyslog : データベースにログを出力する2020/02/07

	データベースにログを出力する場合は以下のように設定します。
[1]	データベースはいくつかの主要なものから選択可能ですが、当例では MariaDB を利用します。よって、こちらを参考に MariaDB サーバーをインストールして起動しておきます。
[2]	MariaDB に 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.

# rsyslog ユーザーを作成し、Syslog DB に対して権限を付与 (password には任意のパスワードを設定)
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]	ログをデータベースへ出力するように Rsyslog を設定します。

[root@dlp ~]#

vi /etc/rsyslog.conf

# 39行目あたりに追記

module(load="ommysql")

# 例として authpriv のログを DB へ出力

# 書式 ⇒ :ommysql:ホスト名,DB,DBユーザー,DBパスワード

authpriv.*

:ommysql:localhost,Syslog,rsyslog,password

[root@dlp ~]#

systemctl restart rsyslog

[4]	DB を見てみると、以下のようにログが記録されていることが分かります。

[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)