Rsyslog : データベースにログを出力する2015/06/18

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

[root@dlp ~]#

yum -y install rsyslog-mysql

[root@dlp ~]#

cat /usr/share/doc/rsyslog-mysql-*/createDB.sql | mysql -u root -p

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 7291
Server version: 5.5.41-MariaDB MariaDB Server

Copyright (c) 2000, 2014, 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

# 22行目あたりに追記

$ModLoad 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 7299
Server version: 5.5.41-MariaDB MariaDB Server

Copyright (c) 2000, 2014, 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.00 sec)

MariaDB [Syslog]>

select ReceivedAt,Facility,Priority,FromHost,Message from SystemEvents;

+---------------------+----------+----------+----------+--------------------------------------------------------+
| ReceivedAt          | Facility | Priority | FromHost | Message                                                |
+---------------------+----------+----------+----------+--------------------------------------------------------+
| 2015-06-17 19:40:33 |       10 |        6 | dlp      | pam_unix(login:session): session closed for user root  |
| 2015-06-17 19:40:39 |       10 |        6 | dlp      | pam_unix(login:session): session opened for user root  |
| 2015-06-17 19:40:39 |       10 |        6 | dlp      | DIALUP AT ttyS0 BY root                                |
| 2015-06-17 19:40:39 |       10 |        5 | dlp      | ROOT LOGIN ON ttyS0                                    |
| 2015-06-17 19:40:58 |       10 |        6 | node01   |  Accepted password for cent from 10.0.0.30 port 60492  |
| 2015-06-17 19:40:58 |       10 |        6 | node01   |  pam_unix(sshd:session): session opened for user cent  |
| 2015-06-17 19:40:58 |       10 |        6 | node01   |  Received disconnect from 10.0.0.30: 11: disconnected  |
| 2015-06-17 19:40:58 |       10 |        6 | node01   |  pam_unix(sshd:session): session closed for user cent  |
| 2015-06-17 19:41:13 |       10 |        6 | node01   |  pam_unix(su-l:session): session opened for user cent  |
| 2015-06-17 19:41:23 |       10 |        6 | dlp      | Invalid user cent from 10.0.0.51                       |
| 2015-06-17 19:41:23 |       10 |        6 | dlp      | input_userauth_request: invalid user cent [preauth]    |
| 2015-06-17 19:41:27 |       10 |        4 | dlp      | pam_unix(sshd:auth): check pass; user unknown          |
| 2015-06-17 19:41:27 |       10 |        5 | dlp      | pam_unix(sshd:auth): authentication failure; logname=  |
| 2015-06-17 19:41:28 |       10 |        6 | dlp      | Failed password for invalid user cent from 10.0.0.51 p |
| 2015-06-17 19:41:29 |       10 |        6 | dlp      | Connection closed by 10.0.0.51 [preauth]               |
| 2015-06-17 19:41:40 |       10 |        6 | dlp      | Accepted password for root from 10.0.0.51 port 58750 s |
| 2015-06-17 19:41:40 |       10 |        6 | dlp      | pam_unix(sshd:session): session opened for user root b |
| 2015-06-17 19:41:42 |       10 |        6 | dlp      | Received disconnect from 10.0.0.51: 11: disconnected b |
| 2015-06-17 19:41:42 |       10 |        6 | dlp      | pam_unix(sshd:session): session closed for user root   |
+---------------------+----------+----------+----------+--------------------------------------------------------+
19 rows in set (0.00 sec)