The present version needs some fixing of field names to work properly!
Beware, if your postgres server for some reason goes down e.g. full disk, you will loose all your logging…
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 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 http://sickel.net/syslog/syslog-002.tar.gz There is still no automatic installation, so these steps have to be followed:
- If needed, install postgresql on the server
- create the user httpd and the database syslog and poplulate it using the script syslog.sql
- Install syslog-ng on all machines that are to participate in the logging network, preferably using your distribution”s distribution
- 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
- 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
- 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/ the two latter levels must be readable for the postgres user
- 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)
- 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 - Restart syslog-ng. Check the database if anything is accumulating, if not check all logs for errors
- 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 - 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 messagein psql
- Put the .css file somewhere it can be reached and adjust the header in the syslog.pl accordingly
- 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 least 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 should immideatly 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
- Check if the server is running, do not delete log items if they are not received
- 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 at syslog@sickel.net