Tutorial Centralized logging on macOS

[toaruScar]

I want to store logs from all my ASUS routers in one place, so I recently tried to set up a server for centralized logging on a LAN-side macOS host, and it turned out to be really easy.

---

Install Homebrew
First install Homebrew.

---

Setting up MySQL for rsyslog
Install mysql:

brew install mysql

And follow the Caveats to secure root account by running

mysql_secure_installation

Then start mysql:

brew services start mysql

Then download the sql script, and use the password you just created to create a database to be used by rsyslog.

mysql -u root -p <[path to sql script]

After this there'll be a database called Syslog.

Then create users so rsyslog can write to the database. You'll first need to connect to mysql server first by running mysql -u root -p.

CREATE USER 'Syslog-Writer'@'localhost' IDENTIFIED BY 'password';
GRANT insert on Syslog.* to 'Rsyslog-Writer'@localhost ;

---

Setting up rsyslog
Modify the formula for rsyslog to enable storing logs to a mysql dabase:

brew edit rsyslog

Then add the highlighted line to the file (line 22 and line 44)

Then build rsyslog from source by running

brew install --build-from-source rsyslog

Then modify the config file for rsyslog at /usr/local/etc/rsyslog.conf:
A barebone example should have the following:

module(load="imudp")
input(type="imudp" port="514")

module(load="ommysql")
action(type="ommysql" socket="/tmp/mysql.sock" server="localhost" db="Syslog" uid="Syslog-Writer" pwd="password")

This file tells rsyslog to listen at port 514 and save the logs to a database.

At last you can run rsyslog:

/usr/local/opt/rsyslog/sbin/rsyslogd -n -f /usr/local/etc/rsyslog.conf -i /usr/local/var/run/rsyslogd.pid

This will will launch rsyslog in the foreground. Rsyslog will let you know in the output if anything goes wrong.

---

Configure ASUS router
First, assign a static IP address to the host running rsyslog.
Then go to "System Log", and fill in "Remote Log Server" with the static IP address from the previous step, and fill in "Remote Log Server Port" with "514".
Hit "Apply". This will have all AiMesh nodes send their logs to the logging server.

---

Check if everything works
First, we need to do something that will generate a log entry on any of the nodes, for example, SSH'ing into one of the nodes.
Then you can connect to your sql database ( mysql -u root -p) and check if there's anything in the "SystemEvents" table:

use Syslog;
SELECT t.* FROM Syslog.SystemEvents t ;

And you should see a row of data in the query result there.

---

Finishing up
You should then exit that syslog process, and use brew services start rsyslog to run it as a daemon.
You can create a view in the database to show only the columns of interest:

create definer = `Syslog-Admin`@localhost view conciseevents as
select `syslog`.`systemevents`.`ReceivedAt`                                                                        AS `Time`,
       `syslog`.`systemevents`.`FromHost`                                                                          AS `Host`,
       trim(trailing ':' from `syslog`.`systemevents`.`SysLogTag`)                                                 AS `Process`,
       trim(leading ' ' from `syslog`.`systemevents`.`Message`)                                                    AS `Message`
from `syslog`.`systemevents`
order by `syslog`.`systemevents`.`ReceivedAt` desc
limit 501;

You can also create a user to read the logs, so you don't have to log in as root everytime.