HOWTO GET SYSLOGS INTO POSTGRESQL SQL DATABASE

25 Apr 2002

      HOWTO 

HOW TO GET SYSLOGS INTO POSTGRESQL SQL DATABASE
	originally by Ben Russo 04/25/2002

		AUDIENCE

This document is intended to be read by a UNIX System Administrator
with at least a year or two of experience.  This document is heavily
biased towards RedHat LINUX because the author likes RedHat Linux.  

If you want to do this on another UNIX platform, more power to you, 
but I won't be writing a document about how to do it.

Feel free to use this document however you want. (My friends suggest
that it comes in handy in the toilet and next to the fireplace).
You can modify it and redistribute it however you want to, but 
please (just a suggestion) leave my name as the *original*
author.

There are no guarantees or warrantees about the advice given here.
It is full of mispeellings and grammar mistakes for which I will
never make no apologiez.

		GOAL

The goal of this document is to provide a procedure for creating
a centralized SYSLOG sink that imports syslog messages into a 
postgreSQL database and provides ODBC access to that database.
So that you can use PgAdmin or MS Access to generate Syslog Reports.

You could theoretically set up triggers in Postgres (based on inserts)
that would cause your system to kick off other jobs (like paging the
sysadmin).
But that won't be covered here.

		CREDITS & THANKS

I would like to thank Open Source developers everywhere,
I hesitate to mention names because there are so many of them
and they all deserve great recognition.  However I will mention
a few that are relevant to this document, there are many more people
as well but you are going to skip this section anyway so why bother.

	 Balázs Scheidler  (author & maintainer of syslog-ng)
		Thanks for all your hard work!

	 Bruce Momjian (author of the PostgreSQL Book from which
			     I learned much about databases, and 
				a major contributor to the PostgreSQL
				 database project)

I would dedicate this document to open source developers, but
it sucks so much I don't want to embarress them.

	HOWTO....

Set up a PC with the latest version of Linux and all the patches.
	(You could use any UNIX, but I like Linux, and I wrote this.)
You can set it up however you like best but make sure that you at least
install the following:

	ssh/scp
	bind
	cacheing-nameserver config
	ntp
	postgreSQL
	syslog-ng

After installing your box, make sure that it is on the network.
If I were you I would disable everything else on the box that is 
network accessible.

Set up bind to be started at boot time and set forwarders to your
default name servers and set up forward only.  
Set the /etc/resolv.conf to point at 127.0.0.1

This setup is designed to create a forwarding only caching DNS server
that will increase the performance of the syslog-ng server when doing
NS-LOOKUPS

Set up and configure ntp to be started at boot time.
Make sure that the systems default time zone is set for UCT
(Universal Coordinated Time).  This is important for this 
box because all our syslogs will be coming from different zones.
Even the Neutral Zone.

Get the latest tarball for libol and syslog-ng from the balabit
website (where syslog-ng is from). use "rpm -tb " on the tarballs 
(you will need all the development tools and compilers and devel libs 
and rpm-build installed, as well as the kernel headers)  
     (****USE RPMS THEY ARE GOOD FOR YOU****)
The rpm -tb command will create i386.rpm binary install packages
for libol and syslog-ng and put them in /usr/src/redhat/RPMS/i386

use "rpm -Uvh" on the newly created rpms to install them.

Then "cd /usr/etc" and 
"mkdir /etc/syslog-ng; ln -s ../../etc/syslog-ng syslog-ng"

in the /etc/syslog-ng directory place the following 
into syslog-ng.conf

*******************************BEGIN FILE /etc/syslog-ng/syslog-ng.conf

# syslog-ng configuration file for RedHat 7.2
#
# See syslog-ng(8) and syslog-ng.conf(5) for more information.
#
# Originall:  20000925 gb@sysfive.com
# Modified:  20020424  ben@pleasenospam.tnsi.com
#

options { sync (0);
          time_reopen (10);
          log_fifo_size (100);
          long_hostnames (on);
          use_dns (yes);
          use_fqdn (yes);
          create_dirs (yes);
          keep_hostname (yes);
          time_reap(12);
          gc_busy_threshold(768);
        };

############################################################################
###

source s_sys { unix-stream ("/dev/log"); internal(); };
source s_udp { udp(); };

############################################################################
###

# destination d_files { file("/var/log/syslog-ng/$HOST/$FACILITY.$LEVEL"
#                      template("$ISODATE $HOST $FACILITY $PRIORITY $PROCESS
$MSG\n"));
#                    };

destination d_msgs { file("/var/log/messages"
                      template("$FULLDATE $HOST $FACILITY.$PRIORITY
$MSG\n"));
                    };

destination d_mail { file("/var/log/maillog"
                      template("$FULLDATE $HOST mail.$PRIORITY $MSG\n"));
                    };

destination d_secure { file("/var/log/secure"
                      template("$FULLDATE $HOST authpriv.$PRIORITY
$MSG\n"));
                    };

destination d_cron { file("/var/log/cron"
                      template("$FULLDATE $HOST cron.$PRIORITY $MSG\n"));
                    };

destination d_bootlog { file("/var/log/boot.log"
                      template("$FULLDATE $HOST local7.$PRIORITY $MSG\n"));
                    };

destination d_tty12 { pipe("/dev/tty12"
                      template("$FULLDATE $FACILITY.$PRIORITY $MSG\n"));
                    };

destination d_postgres {
file("/var/lib/pgsql/syslog/datafiles/fulllog.$YEAR.$MONTH.$DAY.$HOUR.$MIN.$
SEC"
       template("INSERT INTO msg_table VALUES \( '$R_ISODATE', '$S_ISODATE',
'$HOST', '$FACILITY', '$PRIORITY', '$MSG'\)\;\n")
       template_escape(yes)
       owner(postgres));
                   };

############################################################################
####
############################################################################
####

filter f_msgs { host("syslog")
                and level(warning,err,crit,alert,emerg)
                and not facility(mail,authpriv,cron);
              };

filter f_mail { host("syslog")
                and facility(mail);
              };

filter f_cron { host("syslog")
                and facility(cron);
              };

filter f_secure { host("syslog")
                and facility(authpriv);
              };

filter f_bootlog { host("syslog")
                and facility(local7);
                 };

filter f_tty12 { host("syslog")
                and level(crit,alert,emerg);
               };

############################################################################
######
############################################################################
######

# log { source(s_sys); source(s_udp); destination(d_files); };
log { source(s_sys); source(s_udp); filter(f_msgs); destination(d_msgs); };
log { source(s_sys); source(s_udp); filter(f_mail); destination(d_mail); };
log { source(s_sys); source(s_udp); filter(f_secure); destination(d_secure);
};
log { source(s_sys); source(s_udp); filter(f_cron); destination(d_cron); };
log { source(s_sys); source(s_udp); filter(f_bootlog);
destination(d_bootlog); };
log { source(s_sys); source(s_udp); filter(f_tty12); destination(d_tty12);
};
        # This allows you to hit "ALT-F12" on a Linux console and get
        # all the important log messages scrolling up your screen.
log { source(s_sys); source(s_udp); destination(d_postgres); };

**************************END OF FILE /etc/syslog-ng/syslog-ng.conf

then set up your syslog-ng init script in /etc/rc.d/init.d
like so:

**************************BEGIN FILE /etc/rc.d/init.d/syslog-ng
#!/bin/bash
#
# syslog-ng       Starts syslog-ng/klogd.
#
#
# chkconfig: 2345 12 88
# description: Syslog-ng is the facility by which many daemons use to log \
# messages to various system log files.  It is a good idea to always \
# run syslog-ng.

# Source function library.
. /etc/init.d/functions
:

############################################################################
####
# configuration
#
INIT_PROG="/sbin/syslog-ng"    # Full path to daemon
INIT_OPTS=""                      # options passed to daemon

PATH=/bin:/sbin:/usr/bin:/usr/sbin

INIT_NAME=`basename "$INIT_PROG"`

# Uncomment this if you are on Redhat and think this is useful
#
# . /etc/sysconfig/network
#
# if [ ${NETWORKING} = "no" ]
# then
#       exit 0
# fi

RETVAL=0

umask 077
ulimit -c 0

# See how we were called.
case "$1" in
  start)
        export TZ=UCT
        echo -n "Starting $INIT_NAME: "
        daemon --check $INIT_PROG "$INIT_PROG $INIT_OPTS"
        RETVAL=$?
        echo -n "Starting Kernel Logger: "
        [ -x "/sbin/klogd" ] && daemon klogd
        echo
        [ $RETVAL -eq 0 ] && touch "/var/lock/subsys/${INIT_NAME}"
        ;;
  stop)
        echo -n "Stopping $INIT_NAME: "
        killproc $INIT_PROG
        RETVAL=$?
        echo -n "Stopping Kernel Logger: "
        [ -x "/sbin/klogd" ] && killproc klogd
        echo
        [ $RETVAL -eq 0 ] && rm -f "/var/lock/subsys/${INIT_NAME}"
        ;;
  status)
        status $INIT_PROG
        RETVAL=$?
        ;;
  restart|reload)
        $0 stop
        $0 start
        RETVAL=$?
        ;;
  *)
        echo "Usage: $0 {start|stop|status|restart|reload}"
        exit 1
esac

exit $RETVAL
********************************END OF FILE /etc/rc.d/init.d/syslog-ng

Disable the default syslog daemon installed by RedHat.
use chkconfig to get rid of it's sysVinit links
start up syslog-ng!

use chkconfig to make sure that the proper SysVinit links are created and
that syslog-ng is configured to be started when the box is.

Also use chkconfig to make sure that postgresql is configured to start
when the system is.

Copy /var/lib/pgsql/data/pg_hba.conf to /var/lib/pgsql/data/pg_hba.conf.orig
Edit /var/lib/pgsql/data/pg_hba.conf so that it contains the following
lines:

**************BEGIN**************
local   syslog-ng    trust admins
local   all     md5
host    all     172.16.0.0 255.240.0.0 md5
host    all     127.0.0.1 255.255.255.255 md5
*************END******************

echo "postgres" > /var/lib/pgsql/data/admins

Make sure that the following lines 
are uncommented in /var/lib/pgsql/data/postgresql.conf

	port = 5432
	tcpip_socket = true
	fsync = false
	shared_buffers=256

If you are not going to monitor the box to make sure that the log files
under /var/log and /var/lib/pgsql/syslog
don't grow too big then you should write some scripts to clean them up
automatically.

I plan on doing this eventually, but at this stage in the development of 
the system I want to watch it manually until I figure out what is going on.

Now lets reboot the box and make sure of a few things.
	So far when the box boots only 
		sshd
		ntpd
		bind
		postgresql (postmaster is the network listener)
		syslog-ng
	should be listening on the network

NTP should sync up with the time servers.
Bind should be forward only and the local system should resolv from itself.
SSH & SCP should be accessible from the network.
cron should be running.
postmaster should be accepting connections from other psql boxes.

Now, run "su - postgres" and run the command: "createdb syslog-ng"
Then run "psql -U postgres -d syslog-ng"
You should now be at an SQL terminal prompt,
and you need to run the following commands:

CREATE sequence msg_seq_num;
CREATE TABLE msg_table (
msg_rcv_time timestamp,
msg_sent_time timestamp,
hostname varchar(256),
msg_facility varchar(16),
msg_priority varchar(16),
msg_text text,
msg_id integer default nextval('msg_seq_num')
);

ALTER USER postgres with encrypted password 'enter password here'

\q

Now create the file called /var/lib/pgsql/syslog/run-syslog2pgsql-insert.sh
with the content:

**************************************BEGIN FILE
*****************************
#!/bin/bash
#
#       /var/lib/pgsql/run-syslog2pgsql-insert.sh
#       23-April-2002  by Ben Russo
#
#       This script is intended to be called by cron.
#       This script should never exit, it runs in a loop.
#       The first thing it should do is check to see that
#       if there is another instance already running.
#
#       running this script every minute should work pretty good.
#
#
LOGFILE=/var/lib/pgsql/syslog/syslog2pgsql-insert.log
export TZ=UCT
if [ -f /var/lib/pgsql/syslog/.insert.lockfile ]
  then
     OLDPID=`cat /var/lib/pgsql/syslog/.insert.lockfile`
     NUMPROCS=`ps -e | grep $OLDPID | grep run-syslog2pg | wc -l`
     if [ $NUMPROCS -eq 1 ]
        then
         exit 0
     fi
  fi

echo $$ > /var/lib/pgsql/syslog/.insert.lockfile

while true
  do
   FILELIST=`find $DATADIR -name
"fulllog.2[0-9][0-9][0-9].[0-1][0-9].[0-3][0-9].[0-2][0-9].[0-5][0-9].[0-6][
0-9]"`
   usleep 999997
   for i in $FILELIST
    do
     cat $i | psql -U postgres -d syslog-ng >> $LOGFILE 2>&1
     DATE=`date`
     echo "========= $DATE finished $i" >> $LOGFILE
     rm -f $i
    done
   SLPID=`pidof syslog-ng`
   done
***************************************END
FILE*********************************

Make sure that everything under /var/lib/pgsql/ is owned by postgres.

Add the following line to the postgres users crontab:

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

Next, install the PsqlODBC driver on your windows desktop PC
and install the "pgadmin" utility.

Open your control panel
Open the ADMINISTRATIVE TOOLS sub-panel
Double click on the "DATA SOURCES (ODBC)"
	Icon
Select the System DSN tab from the window that appears.
Click on the "ADD" button.
Select PostgreSQL 
Enter "syslog-ng" (or whatever the name of the database was
that you created)
Enter the host name of your database server
The port should be 5432

Your User name can be "postgres" if you are the admin
or "guest" (we will create that user soon enough).
and the database password for the account name you picked.
The password for "guest" should be "guest"

Click OK, click OK again, close your control panel windows.

Now you can run PGADMIN from the windows start menu ICON.
In this utility you can add users and groups and set 
permissions for the tables and columns.
You can use the query wizard to generate and save queries
and direct their output to Excell, HTML, TXT or Screen.

Create the "guest" "guest" user after logging in as the
postgres user.  Also create the guest account.

Right click on your msg_table and select 
properties, and on the Security tab give the guest group.

Give the guest group the ability to "SELECT" only.

Now open up MS Access,  open a blank database.
...
From the FILE menu select GET EXTERNAL DATA, LINK TABLES
and in the filebrowser window that pops up you need to 
scroll down in the "FILES OF TYPE" window to ODBC Databases()
Then in the "Select Data Sources" window that appears go
to the "Machine Data Source" window and scroll down 
to PostgreSQL
Then select the msg_table and click "OK"
Then select the "oid" field (or the "msg_id" field)
as your Unique record Identifier and click OK.

Now you can use MS-Access to do queries and searches
and reports.

Russo, Ben

tags

participants (1)