Thursday, April 9, 2009

rsyslog and php-syslog-ng for gnu/linux and AIX logging

php-syslog-ng is a free tool to display reports from
a centralized log server. i'm evaluating it now to see
how it fits our rhel and aix systems.

currently, a windows based application is being used
by our infra team to handle centralized log reporting.

install rsyslog and mysql

yum install rsyslog rsyslog-mysql php-mysql mysql-server mysql

NOTE: a web server is also needed. httpd and php have been previously
installed on my machine.

download php-syslog-ng from:

http://code.google.com/p/php-syslog-ng/

for the directory location, i used the ff:

php-syslog-ng:
/apps/isaplog


mysql database:
/database/


mysql server

by default, RHEL5 mysql creates DBs in /var/lib/mysql. i
stopped the mysql daemons first and then moved the /var/lib/mysql
files to /database/mysql.
take note of the selinux settings here.


# /etc/my.cnf
[mysqld]
datadir=/database/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/database

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


# set password
/usr/bin/mysqladmin -u root password 'root_db_pass'
/usr/bin/mysqladmin -u root -h lx0050.domain password 'root_db_pass'


# create database
mysql -u root -p
create DATABASE ISAPLog;
grant all privileges on ISAPLog.* to isaplog@"localhost" identified by 'admin_pass';
grant select on ISAPLog.* to sysloguser@"%" identified by 'ro_passwd';
Query OK, 0 rows affected (0.00 sec)


php-syslog-ng

installation of php-syslog-ng is straight forward. you can
follow the steps in the guide .

just watch out for the following:

1. ownership of the files
- i set apache to own the contents of /apps/isaplog
2. permissions
- html/jpcache and html/config needs to be writable
during installation
chcon -t public_content_rw_t html/jpcache
chcon -R -t public_content_rw_t html/config
(a blank config.php is present, apache needs to write
to it during configuration).

after installation, make sure to remove the write permissions
in config/ and its contents. install directory also needs to be
removed.

for info:
man 8 httpd_selinux

my webserver configuration for my php-syslog-ng site is as follows:

#/etc/httpd/conf.d/phpsyslog.conf
Alias /isaplog /apps/isaplog/html

<Directory "/apps/isaplog/html">
Options -Indexes FollowSymLinks MultiViews
AllowOverride All
Order allow,deny
Allow from all
</Directory>
# added after configuration is done
<Directory "/apps/isaplog/scripts">
Deny from all
</Directory>
<Directory "/apps/isaplog/includes">
Deny from all
</Directory>
<Directory "/apps/isaplog/config">
Deny from all
</Directory>
# end

my site, is then accessible as:

http://lx0050/isaplog/

after installation, /apps/isaplog looks like this:

drwxr-xr-x apache root root:object_r:public_content_t html
drwxr-xr-x apache root root:object_r:public_content_t scripts
drwxr-xr-x apache root root:object_r:public_content_t upgrades

html/config:

drwxr-xr-x apache root root:object_r:public_content_t config


my initial rsyslog.conf:
$ModLoad ommysql.so
$template isapDBLog,"insert into logs(host, facility, priority, level, tag, datetime, program, msg) values ('%HOSTNAME%', '%syslogfacility-text%', '%syslogseverity-text%', '%syslogpriority-text%', '%syslogtag%', '%timereported:::date-mysql%', '%programname%', '%msg%')",sql
*.* :ommysql:127.0.0.1,ISAPLog,isaplog,admin_pass;isapDBLog

isapDBLog - is just the template name
ISAPLog - is the DB created to hold logs
isaplog - the DB user with admin privileges on ISAPLog DB
admin_pass - isaplog password

for the AIX Audit logs, entries in AIX are like this:

UXI0025 AIXAudit 2 14398 Wed Apr 08 16:21:42 2009 PROC_Execute grep iriXXXXX user00 979110 6758
74 OK euid: 6206 egid: 6200 epriv: 0:0 name grep 14024

UX0025 AIXAudit 2 14399 Wed Apr 08 16:21:42 2009 PROC_Execute grep iriXXXXX user00 1364214 6758
74 OK euid: 6206 egid: 6200 epriv: 0:0 name grep PRINCIPAL

UX0025 AIXAudit 2 14400 Wed Apr 08 16:21:42 2009 PROC_Execute awk iriXXXXX user00 675874 4671
68 OK euid: 6206 egid: 6200 epriv: 0:0 name awk -F | { print $5}

UX0025 AIXAudit 2 14401 Wed Apr 08 16:21:42 2009 PROC_Execute cat iriXXXXX user00 1085528 6758
76 OK euid: 6206 egid: 6200 epriv: 0:0 name cat /xMount/adsfasf/ISIXXXXX/IRIS/IRIS_C/extraction/out/Variables.dat

UX0025 AIXAudit 2 14402 Wed Apr 08 16:21:42 2009 PROC_Execute grep iriXXXXX user00 1364216 6758
76 OK euid: 6206 egid: 6200 epriv: 0:0 name grep 14024

UX0025 AIXAudit 2 14403 Wed Apr 08 16:21:42 2009 PROC_Execute grep iriXXXXX user00 1085530 6758
76 OK euid: 6206 egid: 6200 epriv: 0:0 name grep PRINCIPAL


However, using the current rsyslog configuration above to log to the mysql database,
the AIX Audit log entries are badly messed up. what i did, for a logging that is just *OK* with me:

- process the AIX Audit logs separetely (the AIX boxes still logs using traditional syslog message
- log them to DB with the raw,messed up AIX audit messages intact.

i then have the ff rsyslog.conf entries:

# start of rsyslog.conf
$ModLoad ommysql.so
# for other systems using syslog
$template isapDBLog,"insert into logs(host, facility, priority, level, tag, datetime, program, msg) values ('%HOSTNAME%', '%syslogfacility-text%', '%syslogseverity-text%', '%syslogpriority-text%', '%syslogtag%', '%timereported:::date-mysql%', '%programname%', '%msg%')",sql

# for AIX Audit
$template AIXDBLog,"insert into logs(host, facility, priority, level, tag, datetime, program, msg) values ('%FROMHOST%', 'syslog', '%syslogseverity-text%', '%syslogpriority-text%', '%syslogtag%', '%timereported:::date-mysql%', 'AIXAudit', '%msg:::drop-last-lf%')",sql

# for specific AIX Audit messages - log to sql
:msg,contains,"AIXAudit" :ommysql:127.0.0.1,ISAPLog,isaplog,admin_pass;AIXDBLog
& ~

#:msg, startswith, "START: nrpe " ~
if $msg contains 'START: nrpe' then ~
# log the rest to sql
*.* :ommysql:127.0.0.1,ISAPLog,isaplog,admin_pass;isapDBLog
# end of rsyslog.conf

from php-syslog-ng, an AIX audit entry appears like this:


one problem i'm still trying to fix - discarding unwanted log entries (like the
status msgs from nagios).



the line in rsyslog.conf:

if $msg contains 'START: nrpe' then ~

was supposed to fix it. and i think i'm doing something wrong. :D

more readings:
php-syslog-ng
rsyslog

update: i've scheduled some log rotation and cache reloading (scripts are part of php-syslog-ng):

#a file in /etc/cron.d has this:
*/5 * * * * root php /apps/isaplog/scripts/reloadcache.php >> /var/log/reloadcache.log

and had the following problem (msg was sent to my email):

PHP Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 19731248 bytes) in /apps/isaplog/html/includes/common_funcs.php on line 203

i've set before the memory allocation to 128M then to 256M - and it's been working for a few days. now it is complaining again. from a google issue post, i've set the following:

in line 201 of html/includes/common_funcs.php, i added this line:

if (!in_array($row['program'],$cacheProgramValues))

and that fixed the problem.

update:

i've upgraded the rsyslog package using the rpm file i created and discarding of nrpe entries is now working. but the AIX Audit entries now has some different entries:


under Program column, instead of the "AIXAudit" entry, it now has the delimiter (?) included (the #011 etc).
AND from this very helpful post, i added $EscapeControlCharactersOnReceive off in my rsyslog.conf.

my new rsyslog.conf now has the following:
# start rsyslog.conf
$ActionFileDefaultTemplate RSYSLOG_TraditionalFileFormat
$EscapeControlCharactersOnReceive off
$ModLoad ommysql.so
$ModLoad imklog
$ModLoad immark
$ModLoad imuxsock
$ModLoad imudp
# receive messages from network
$UDPServerAddress 10.x.y.z
$UDPServerRun 514

$template isapDBLog,"insert into logs(host, facility, priority, level, tag, datetime, program, msg) values ('%HOSTNAME%', '%syslogfacility-text%', '%syslogseverity-text%', '%syslogpriority-text%', '%syslogtag%', '%timereported:::date-mysql%', '%programname%', '%msg%')",sql

if $msg contains 'START: nrpe' then ~
*.* :ommysql:127.0.0.1,ISAPLog,isaplog,admin_pass;isapDBLog
# end rsyslog.conf

notice that the AIX template is no longer used. isapDBLog now handles everything (for RHEL and AIX messages). and entries now look neat and clean:


will update how it goes.

4 comments:

  1. High, I am the author of rsyslog. Good article! I suggest that you also have a look at phpLogcon, another web interface. Actually, I would be very interested in your thoughts on which one is better and why. If you don't want to go through all the hassle of setting it up, you can have a peek at the demo system on demo.phplogcon.org.

    On the discard action: I'll try to look into it, it looks right. It would be great, though, if you could post a question at the rsyslog forums.

    ReplyDelete
  2. hi rainer,

    thanks for the comment. i already did take a look a phplogcon before. and it does have a lot of features compared to php-syslog-ng. i'm not the main user for this project. we have a QA team that needs to view certain log entries for servers. and phplogcon seems to be a bit "too advanced" for their needs. i'm considering it though for our sys admin group.
    and for the discard problem, i'll check out the forums.

    thanks!

    ReplyDelete
  3. discard problems have been resolved with 3.20.5. i've updated my blog. everything looks clean right now.

    ReplyDelete
  4. well, encountered a bug today, rsyslog stopped logging to the DB (redirecting to file on disk
    is ok).

    running rsyslog in debug mode:

    7390.960465000:main thread: initialization completed, transitioning to regular run mode
    7390.963496000:main queue:Reg/w0: Called LogError, msg: db error (1062): Duplicate entry '8388607' fo
    7390.963524000:main queue:Reg/w0: logmsg: flags 5, from 'lxisap0050', msg db error (1062): Duplicate
    7390.963559000:main queue:Reg/w0: Message has legacy syslog format.


    the phplogrotate utility from php-syslog-ng messed up something in the DB.
    from google:
    http://groups.google.com/group/php-syslog-ng-support/browse_thread/thread/17e71a706f7f4e35

    php-syslog-ng is still under development. so don't complain. :D

    moving to phplogcon...

    ReplyDelete