Php-Syslog-ng

cf http://code.google.com/p/php-syslog-ng/
This installation is done based on the proposed setup in Syslog.

Install (old notes for version 2.8)

In Apache node

Get http://www.phpwizardry.com/php-syslog-ng/phpsyslogng-2.8.tar.gz
Untar it in /usr/local/share/phpsyslogng
Manage the config file the Debian way: move config/config.php to /etc/phpsyslogng/config.php and make a symlink
In config.php, change at least those lines and assign your passwords:

define('DBUSERPW', 'aaaa');
define('DBADMINPW', 'cccc');

And in our case as the sql is running on a separate node:

define('DBHOST', 'sql'); 

Create /etc/apache2/sites-available/phpsyslogng with:

Alias /syslog /usr/local/share/phpsyslogng
<Directory /usr/local/share/phpsyslogng>
 Options FollowSymLinks
</Directory>

Reload apache2 and go to http:// ... /syslog you will get a help page for the installation process.

Another step is to install the scripts/logrotate.php script.
First adapt the $APP_ROOT to your install, here /usr/local/share/phpsyslogng, then

ln -s /usr/local/share/phpsyslogng/scripts/logrotate.php /etc/cron.daily/phpsyslogng-logrotate

In Mysql node

Take scripts/dbsetup.sql from php-syslog-ng
But adapt some lines to our situation:

# create users
INSERT INTO user (Host, User, Password) VALUES ('private','sysloguser', password('aaaa'));
INSERT INTO db (Host, Db, User) VALUES ('private','syslog','sysloguser');

INSERT INTO user (Host, User, Password) VALUES ('mail','syslogfeeder', password('bbbb'));
INSERT INTO db (Host, Db, User) VALUES ('mail','syslog','syslogfeeder');

INSERT INTO user (Host, User, Password) VALUES ('private','syslogadmin',password('cccc'));
INSERT INTO db (Host, Db, User) VALUES ('private','syslog','syslogadmin');
COMMIT;
FLUSH PRIVILEGES;

# grant rights to user syslogadmin for backup purpose
GRANT USAGE ON syslog.* TO syslogadmin@private;
GRANT ALL ON syslog.* TO syslogadmin@private;
GRANT RELOAD ON *.* TO syslogadmin@private;

REVOKE ALL PRIVILEGES ON syslog.* FROM sysloguser@private;
GRANT USAGE ON syslog.* TO sysloguser@private;
GRANT SELECT ON syslog.* TO sysloguser@private;
GRANT UPDATE ON syslog.users TO sysloguser@private;

REVOKE ALL PRIVILEGES ON syslog.* FROM syslogfeeder@mail;
GRANT USAGE ON syslog.* TO syslogfeeder@mail;
GRANT INSERT ON syslog.* TO syslogfeeder@mail;

GRANT ALL ON syslog.search_cache TO sysloguser@private;
GRANT SELECT ON syslog.user_access TO sysloguser@private;
GRANT ALL ON syslog.user_access TO syslogadmin@private;
GRANT SELECT ON syslog.actions TO sysloguser@private;
GRANT ALL ON syslog.actions TO syslogadmin@private;

Then use it:

mysql -uroot -p < dbsetup.sql

Now you should be able to login to your http:// ... /syslog with account admin/admin
But you will face an error since there is no entry yet in your db.

In Syslog node

Add to /etc/syslog-ng/syslog-ng.conf the example given in scripts/syslog.conf:

destination d_mysql {
   pipe("/var/log/mysql.pipe"
      template("INSERT INTO logs
      (host, facility, priority, level, tag, datetime, program, msg)
      VALUES ( '$HOST', '$FACILITY', '$PRIORITY', '$LEVEL', '$TAG', '$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC',
      '$PROGRAM', '$MSG' );\n") template-escape(yes));
}; 

And the log section but with our additional source:

log {
   source(s_all);
   source(net);
   destination(d_mysql);
};

Get inspired by scripts/syslog2mysql.sh to make an init.d script:

• add option "-h sql.vlan2" to mysql client to connect to the remote host
• save the password in a secured file instead of leaving it on the cmd line of mysql, e.g. /etc/syslog-ng/my.cnf with strich access rights (600 root.root)
• script is based on /etc/init.d/skeleton but much more tricky to track PIDs of processes as mysql client is not a daemon!

[client]
password="bbbb"

The init.d script itself, to be stored in /etc/init.d/syslog2mysql

#! /bin/sh
# Author: Philippe Teuwen

# Do NOT "set -e"

PATH=/usr/sbin:/usr/bin:/sbin:/bin
DESC="Fetch queries from syslog-ng to mysql db"
NAME=syslog2mysql
PIDFILE=/var/run/$NAME.pid
SCRIPTNAME=/etc/init.d/$NAME

# Exit if mysql client is not installed
[ -x "/usr/bin/mysql" ] || exit 0

# Load the VERBOSE setting and other rcS variables
[ -f /etc/default/rcS ] && . /etc/default/rcS

# Define LSB log_* functions.
# Depend on lsb-base (>= 3.0-6) to ensure that this file is present.
. /lib/lsb/init-functions

#
# Function that starts the daemon/service
#
do_start()
{
        [ ! -e /var/log/mysql.pipe ] && echo " (Creating $NAME pipe)." && mkfifo /var/log/mysql.pipe
        if [ -e $PIDFILE ]; then
            if ps -p $(cat $PIDFILE) >/dev/null; then
                echo -n -e "\nError: $NAME seems to be already running!"
                return 1
            else
                rm -f $PIDFILE
            fi
        fi
        {
            while [ -e /var/log/mysql.pipe ]
            do
                mysql --defaults-file=/etc/syslog-ng/my.cnf -u syslogfeeder -h sql.vlan2 syslog < /var/log/mysql.pipe >/dev/null
                sleep 1
            done
        } &
        echo $! > $PIDFILE
}

#
# Function that stops the daemon/service
#
do_stop()
{
        if [ -e $PIDFILE ]; then
            PID=$(cat $PIDFILE)
            if ps -p $PID >/dev/null; then
                # get PID of child
                CPID=$(pgrep -P $PID)
                # kill script
                kill $PID
                # kill child
                kill $CPID
                rm -f $PIDFILE
                return 0
            else
                echo -e "\nWarning: $NAME was not running."
                echo -n -e "\nCleaning PID file"
                rm -f $PIDFILE
                return 1
            fi
        else
            echo -n -e "\nWarning: $NAME was not running"
            return 1
        fi
}

case "$1" in
  start)
        [ "$VERBOSE" != no ] && log_daemon_msg "Starting $DESC" "$NAME"
        do_start
        case "$?" in
                0|1) [ "$VERBOSE" != no ] && log_end_msg 0 ;;
                2) [ "$VERBOSE" != no ] && log_end_msg 1 ;;
        esac
        ;;
  stop)
        [ "$VERBOSE" != no ] && log_daemon_msg "Stopping $DESC" "$NAME"
        do_stop
        case "$?" in
                0|1) [ "$VERBOSE" != no ] && log_end_msg 0 ;;
                2) [ "$VERBOSE" != no ] && log_end_msg 1 ;;
        esac
        ;;
  restart|force-reload)
        #
        # If the "reload" option is implemented then remove the
        # 'force-reload' alias
        #
        log_daemon_msg "Restarting $DESC" "$NAME"
        do_stop
        case "$?" in
          0|1)
                do_start
                case "$?" in
                        0) log_end_msg 0 ;;
                        1) log_end_msg 1 ;; # Old process is still running
                        *) log_end_msg 1 ;; # Failed to start
                esac
                ;;
          *)
                # Failed to stop
                log_end_msg 1
                ;;
        esac
        ;;
  *)
        echo "Usage: $SCRIPTNAME {start|stop|restart|force-reload}" >&2
        exit 3
        ;;
esac

:

And create the links to run the script at startup:

update-rc.d syslog2mysql defaults

Configure

Once everything is settled, you can login to your http:// ... /syslog with user admin / password admin.

First step is of course to go to the config page and assign a new password.

Then you can create users but by default they will be users as powerful as admin.
I though ok just change the default settings but but... wait!!
Actually there is no yet ACLs associated to the users so if you change the defaults you kick the admin out! I had to fix manually the DB to solve it :-(
So, first go to " set user access", select admin, normally everything is allowed, and *save*!!
Then you can reduce the default rights for the new users.

Bug

Once I could not access the interface anymore once I was logged, with an error such as

Unable to open underlying table

The error is a SQL error when trying to access the all_logs table, I get the same error under phpmysql.
I got the solution in this post: modify logrotate.php and run it once solved the problem.
I copy here the hack just to get a trace of it:

--- logrotate.php       2005-07-07 02:07:31.000000000 +0200
+++ logrotate2.php      2007-07-11 15:25:26.000000000 +0200
@@ -70,6 +70,13 @@
        }
 }
 
+// Re-create the merge table again just in case one table is deleted by LOGROTATERETENTION
+//// if(defined('MERGELOGTABLE') || defined('LOGROTATERETENTION') || $isDELETED) {
+if(defined('MERGELOGTABLE') || defined('LOGROTATERETENTION')) {
+        echo "Getting list of log tables.\n";
+        $logTableArray = get_logtables($dbLink);
+}
+
 if(defined('MERGELOGTABLE') && MERGELOGTABLE) {
        echo "Creating merge table.\n";
        $query = "SHOW CREATE TABLE ".DEFAULTLOGTABLE;