Syslogging using PostgreSQL

I am running a small network at home with approximately 5 pcs running linux. To monitor the health of the network, it is important to keep an eye on the syslogs, but as I also have another job and a family to use some time on, I prefer to do that in an as efficient as possible manner. I have been using the postgresql database in a lot of projects, so a natural idea was to somehow put all the syslog information into a database and then afterwards extract what was interesting from that.

When searching the net for information on this issue, the closest I came to some kind of guide, was at https://lists.balabit.hu/pipermail/syslog-ng/2002-April/003249.html, the syslog-ng mailing list, so I have been using that description as the foundation for my work.

The logging system is summerized as followed:

• All the client are sending their syslog information to a central server
• The server is merging all the data into a script for psql (the command-line postgresql interface)
• A process at the server picks up those script at some interval and inserts the information into postgresql.
• A cron job on the server restart the 'pick-up-script' if it goes down
• A perl programme under apache reads selected information from the data base and formats for presentation

All the neccecary files are in syslog-002.tar.gz There is still no automatic installation, so these steps have to be followed:

1. If needed, install postgresql on the server
2. create the database syslog and poplulate it using the script syslog.sql
3. Install syslog-ng on all machines that are to participate in the logging network, preferably using your distribution''s distribution
4. Install the file syslog-ng.conf.server as syslog-ng.conf on the server (in /etc/syslog-ng on debian, probably somewhere similiar on other distributions) Remember to keep the original file as a backup
5. Install the file syslog-ng.conf.client as syslog-ng.conf on the clients. The line "destination net_log { udp("192.168.1.2" port(514));};" has to be modified with the actual ip of your server, again, remember to backup the original file
6. My server is a debian system, so only a few directories had to be created, (if you are using another distribution, either create the 'debian-tree' or modify the shell scripts to point to the correct location) up to /var/lib/postgres/syslog/datafiles/
7. Put the file run-syslog2pgsql-insert.sh into /var/lib/postgres/syslog and make it runnable for the user owning the postgresql database (postgres on debian)
8. Create for the user postgres the cron job:

   */15 * * * * /var/lib/postgres/syslog/run-syslog2pgsql-insert.sh >> /var/lib/postgres/syslog/syslog2pgsql-insert.log 2>&1

   (This should be on one line) That cronjob tries each 15 minutes to restart the run-syslog2pgsql-insert.sh job. The script checks if it is already running and will not be running multiple processes
9. Restart syslog-ng. Check the database if anything is accumulating, if not check all logs for errors
10. Add your hosts manually to the table hostname in the database, or wait for a while (for all host to send at least one message) and run
    

    insert into hostname(name) select distinct hostname from message

    in psql
11. Add the relevant entries manually to the table facility in the database, or wait for a while and run
    

    insert into facility(name) select distinct facility from message

    in psql
12. Put the .css file somewhere it can be reached and adjust the header in the syslog.pl accordingly
13. Install syslog.pl somewhere it will be treated correctly by apache (preferably handeled by mod_perl, but it should also work as a perl cgi-script. Some values at the top have to be customized. (at last the name of the postgresql server). Presently, there is no authentification in the syslog programme. Since the syslogs may contain sensitive information, the web server's authentification should be used if not on a 100% trusted network

Remember, the command

logger [-is] [-f file] [-p pri] [-t tag] [-u socket] [ message ... ]

can be used to send a message to the logger, as in

 logger "This is a test"

The message shoud immideately show up in the logs with priority notice unless another priority is set.

Open syslog.pl in a webbrowser, you should get a window like this, but with your own hostnames (I am using latin flower names for the workstations and Russian numbers for the servers..)

Select a host or --all hosts--, a minimum priority and facilities to watch, and a number of items to have listed if 200 are too many or too few, and press [submit]. The selected log items are color coded using:

Green

Debug and info

Yellow

Notice and warning

Orange

Err and Crit

Red

Emerg and alert

Todos

• Implement taint checks
• Rewrite to a module and make tests
• Make an adminstrative page to make it easier to add facilites and host names
• Make a link back to the original page
• Make scripts for installation
• Make a function to 'flip through' the items i.e. 'Next 200', 'Previous 200'
• Search for all items after a specified timestamp
• Make a more or less automatic system for moving old data out of the data base cf logrotate
• Maybe: use numeric values for host and facility in the message table, the problem with this is that new hosts and facilities have to be added to the respective tables before they are used in logging, or the data will be lost

For further information, contact Morten Sickel: syslog@sickel.net