Configure Rsyslog Server TCP Port 514

[root@server ~]$ vi /etc/rsyslog.conf
# Line 17, 18: uncomment 2 lines
$ModLoad imtcp
$InputTCPServerRun 514

# Line 19: add senders you permit to access
$AllowedSender TCP, 127.0.0.1, 192.168.1.0/24, *.flemingcollege.ca

[root@server ~]$ service rsyslog restart
Shutting down system logger: [ OK ]
Starting system logger: [ OK ]

[root@server ~]$ iptables -I INPUT 5 -m state --state NEW -m tcp -p tcp --dport 514 -j ACCEPT
[root@server ~]$ service iptables save
[root@server ~]$ service iptables restart
[root@server ~]$ semanage port -a -t syslogd_port_t -p tcp 514

[root@client ~]$ vi /etc/rsyslog.conf
# Line 72: uncomment all
$WorkDirectory /var/lib/rsyslog # where to place spool files
$ActionQueueFileName fwdRule1   # unique name prefix for spool files
$ActionQueueMaxDiskSpace 1g     # 1gb space limit (use as much as possible)
$ActionQueueSaveOnShutdown on   # save messages to disk on shutdown
$ActionQueueType LinkedList     # run asynchronously
$ActionResumeRetryCount -1      # infinite retries if host is down
# Add to the end of the file to send only authentication messages to server
authpriv.*    @@server.flemingcollege.ca:514
# Or
authpriv.*    @@192.168.1.100:514
# We can send more if we need, all the options are in the config file
kern.*     # Log all kernel messages                                                
mail.*     # Log all mail messages                                                
cron.*     # Log cron stuff
*.emerg    # Log emergency messages
*.info;mail.none;authpriv.none
# Log anything (except mail and authentication) of level info or higher.
local7.*   # Log boot messages


[root@client ~]$ service rsyslog restart
Shutting down system logger: [ OK ]
Starting system logger: [ OK ]

[root@server ~]$ tail -10 /var/log/secure
Nov  8 07:17:26 localhost unix_chkpwd[2838]: password check failed for user (root)
Nov  8 07:17:29 localhost sshd[2836]: Failed password for root from 192.168.1.125 port 22116 ssh2
Nov  8 07:17:49 localhost sshd[2837]: Connection closed by 192.168.1.125
Nov 10 07:19:49 client sshd[2534]: Invalid user 3 from 192.168.1.125
Nov 10 07:19:49 client sshd[2535]: input_userauth_request: invalid user 3
Nov 10 07:19:50 client sshd[2534]: pam_unix(sshd:auth): check pass; user unknown
Nov 10 07:19:50 client sshd[2534]: pam_succeed_if(sshd:auth): error retrieving information about user 3
Nov 10 07:19:52 client sshd[2534]: Failed password for invalid user 3 from 192.168.1.125 port 22119 ssh2

[root@server ~]$ vi /etc/rsyslog.conf
# Add to define log file's names and directory
$template Secure_log,"/var/log/secure.d/%fromhost%_%$year%%$month%%$day%.secure"

# Add to save authentication messages to log files
authpriv.*     -?Secure_log

[root@server ~]$ service rsyslog restart
Shutting down system logger: [ OK ]
Starting system logger: [ OK ]

[root@server ~]$ ll /var/log/secure.d
total 12
-rw-------. 1 root root 4181 Nov  8 07:58 client_20171108.secure
-rw-------. 1 root root 1330 Nov  8 07:17 localhost_20171108.secure

[root@server ~]$ yum -y install rsyslog-mysql

# Create database for rsyslog
[root@server ~]$ cat /usr/share/doc/rsyslog-mysql-*/createDB.sql | mysql -u root -p
Enter password: # Enter password for root

[root@server ~]$ mysql -u root -p
Enter password: # Enter password for root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

# Create a user named "rsyslog" and grant all privileges to him to access Syslog database (change your own 'p@ssw0rd')
mysql> grant all privileges on Syslog.* to rsyslog@'localhost' identified by 'p@ssw0rd';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

[root@server ~]$ vi /etc/rsyslog.conf
# Near line 20: add to save logs to database
$ModLoad ommysql

# Add to save authentication messages to log files
# How to write :ommysql:Hostname,Database,MysqlUser,MysqlPassword
authpriv.*     :ommysql:localhost,Syslog,rsyslog,password

[root@server ~]$ service rsyslog restart
Shutting down system logger: [ OK ]
Starting system logger: [ OK ]

[root@server ~]$ mysql -u rsyslog -p Syslog
Enter password: # Enter password for rsyslog
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 MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show tables;
+------------------------+
| Tables_in_Syslog       |
+------------------------+
| SystemEvents           |
| SystemEventsProperties |
+------------------------+
2 rows in set (0.00 sec)

mysql> select ReceivedAt,Facility,Priority,FromHost,Message from SystemEvents;
+---------------------+----------+----------+-----------+------------------------------------------------------------------------+
| ReceivedAt          | Facility | Priority | FromHost  | Message                                                                |
+---------------------+----------+----------+-----------+------------------------------------------------------------------------+
| 2017-11-08 07:17:26 |       10 |        5 | localhost |  password check failed for user (root)                                 |
| 2017-11-08 07:17:29 |       10 |        6 | localhost |  Failed password for root from 192.168.1.125 port 22116 ssh2           |
| 2017-11-08 07:17:49 |       10 |        6 | localhost |  Connection closed by 192.168.1.125                                    |
| 2017-11-08 07:19:41 |       10 |        6 | client    |  Invalid user 3 from 192.168.1.125                                     |
| 2017-11-08 07:19:41 |       10 |        6 | client    |  input_userauth_request: invalid user 3                                |
| 2017-11-08 07:19:42 |       10 |        4 | client    |  pam_unix(sshd:auth): check pass; user unknown                         |
| 2017-11-08 07:19:42 |       10 |        2 | client    |  pam_succeed_if(sshd:auth): error retrieving information about user 3  |
| 2017-11-08 07:19:44 |       10 |        6 | client    |  Failed password for invalid user 3 from 192.168.1.125 port 22119 ssh2 |
+---------------------+----------+----------+-----------+------------------------------------------------------------------------+
10 rows in set (0.00 sec)