Ok so about a year ago I was fretting over the idea that MySQL was now owned buy Sun and I actually said, “…Worse of all is if Oracle had purchased them, we would never see another MySQL product again same with IBM.” Uhm yeah, well the news comes out today that Oracle is buying Sun and there by owns MySQL now too.
Interesting.
These instruction are for setting up rsyslog and phpLogCon on CentOS 5.3. It is assumed you have a working Apache and MySQL setup. Configuration of these two packages are outside the realm of this document.
On the Linux server I wanted to host the rsyslog database I ran yum install -y rsyslog-mysql on my CentOS 5.3 machine. Once installed went to the path of cd /usr/share/doc/rsyslog-mysql-2.0.6/. In this location you will see a file called createDB.sql which is the scheme needed to make using phpLogCon easy to use. To create this database type mysql -u MySQLusername -p < createDB.sql You will be prompted for a password for the account you entered and then the database will be created.
Edit the /etc/rsyslog.conf file to add in the mysql connection information at the top of the config file. I am using FROMHOST instead of HOSTNAME as I wasn't getting any hostnames with the former.
$ModLoad ommysql.so
$template dbFormat,”insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values (‘%msg%’, %syslogfacility%, ‘%FROMHOST%’, %syslogpriority%, ‘%timereported:::date-mysql%’, ‘%timegenerated:::date-mysql%’, %iut%, ‘%syslogtag%’)”,sql
*.* : ommysql:192.168.1.100,Syslog,MySQLusername,MySQLpassword;dbFormat
Stop the normal syslog daemon
service syslog stop
And set it to never come on
chkconfig syslog off
Set rsyslog to start at boot
chkconfig rsyslog on
And then start the service
service rsyslog start
You should now have messages showing in your MySQL database. If you do not check the /var/log/messages to see if there is an error in your rsyslog.conf file.
Once you are sure you are getting messages in your database you can use the phpLogCon application to have a nice web interface over the top of the database. Download from http://www.phplogcon.org/, I am using version 2.6.2 for this tutorial. Once downloaded unpack it and move the src directory to the web server path mv src /var/www/html/phplogcon Out of the contrib directory copy both the configure.sh and secure.sh to /var/www/html/phplogcon. Change to the web directory cd /var/www/html/phplogcon and then run the configure script by typing ./configure.sh Open a web browser and enter the path to this site http://servername/phplogcon to start the configuration of the site. Click NEXT until you get to Step 7 and make sure the source type is MySQL, the Database Name is Syslog and the table name is SystemEvents and then enter your MySQL username and password. You now have a function phpLogCon page.
On the Linux clients make sure you install the rsyslog-mysql module the same way you did on the server by typing yum install -y rsyslog-mysql
Edit the /etc/rsyslog.conf file to add in the mysql connection information at the top of the config file.
$ModLoad ommysql.so
$template dbFormat,"insert into SystemEvents (Message, Facility, FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values ('%msg%', %syslogfacility%, '%FROMHOST%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag%')",sql
*.* : ommysql:192.168.1.100,Syslog,MySQLusername,MySQLpassword;dbFormat
Stop the normal syslog daemon
service syslog stop
And set it to never come on
chkconfig syslog off
Set rsyslog to start at boot
chkconfig rsyslog on
And then start the service
service rsyslog start
Refresh your phpLogCon web page and you will now see two entries from the Linux client, one is a kernel message the other is a syslog message.
Anything that has a syslog capability can now point to the rsyslog server, such as HP JetDirect cards and Cisco network gear. All the messages are available in one location and you can filter and search for issues on your network.