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;