RemoteSyslogwithMySQLandPHP
文章備註：如果覺得這種格式不爽 邪惡八進位制給您做了一個pdf格式的 文章末尾有下載
Msyslog has the ability to log syslog messages to a database. This allows for easier monitoring of multiple servers and the ability to be display and search for syslog messages using PHP or any other programming language that can communicate with the database.
“Since the beginning, life has relied upon the transmission of messages. For the self-aware organic unit, these messages can relay many different things. The messages may signal danger, the presence of food or the other necessities of life, and many other things. In many cases, these messages are informative to other units and require no acknowledgement. As people interacted and created processes, this same principle was applied to societal communications. As an example, severe weather warnings may be delivered through any number of channels – a siren blowing, warnings delivered over television and radio stations, and even through the use of flags on ships. The expectation is that people hearing or seeing these warnings would realize their significance and take appropriate action. In most cases, no responding acknowledgement of receipt of the warning is required or even desired.”
I never would have guessed that this message came from the Introduction of RFC 3164 The BSD Syslog Protocol.
Reviewing and maintaining the system logs on dozens of servers is a daunting task. Logging into each one and running grep or awk on each one can be very tedious and time-consuming. Luckily, there are programs like Logwatch and Logdog that can parse syslog files (and other files) and filter out keywords and send email or pager alerts. Fortunately, Syslog has a feature that allows for remote logging to a central server or servers. This feature allows virtually any unix syslog daemon to send syslog messages to a remote server that is configured to accept syslog messages. On a Linux system, for example, the syslogd daemon can be started with the “-r” option which tells the daemon to listen for incoming syslog messages. The port it listens on is 514 and the protocol it accepts is UDP.
# /usr/sbin/syslogd -r -m 0
“-m 0” disables the timestamp mark in the syslog file, /var/log/messages on Linux systems.
The configuration below is the only client configuration needed. The rest of the article pertains only to the central syslog server.
Each client's syslog.conf file is then configured to send alerts to the central syslog server, by adding the line:
*.* @julie
*.* means to send all syslog messages to the remote syslog server, in this article named julie.
Then refresh the syslog daemon:
# /sbin/service syslogd restart
End client configuration.
Now on julie if you run tail -20 /var/log/messages (show the last 20 lines), you should now see the alerts sent with the hostname of the client's that have been configured to send alerts to julie.
NOTE: http://ntsyslog.sourceforge.net has a program, called NTSyslog, that enables Windows Event Logs to be sent to a Unix syslog server.
The process of reviewing multiple servers can be a lot easier using grep, awk, or perl, now that you have a central location where all the messages are sent. To take this one stop further, it can be incorporated with MySQL and PHP.
The first thing we need to do is to get the syslog messages to the MySQL server. This is where Msyslog comes into play. Msyslog is a replacement for the standard syslog daemon that comes installed with most unix systems. Msyslog also has a nice feature of cryptographically signing syslog messages to let an admin know if their syslog files have been altered. Using the cryptographic features will be discussed in the next article. For now, the focus is on sending syslog messages to a remote server, logging to a database, and viewing the logs over a web interface using PHP.
Finally, you will need to compile Apache, MySQL, and PHP support. Depending on your OS you may have a package manager that will do this work for you. Oh! You know me…I am not going to leave you hanging, my fellow readers. Here is a tutorial that explains how to setup Apache, PHP, MYSQL, and SSL. Just get the latest versions.
Configuring Msyslog
This was setup by downloading and installing the rpm from the msyslog website at: http://sourceforge.net/projects/msyslog/
The tarball install compiled cleanly on Red Hat 7.0-7.3.
# cd msyslog-x.xxx
# ./configure
# make
# make install
rpm install:
# rpm -ivh msyslog-xxx.rpm
The rpm install added a startup script named “msyslogd” to the /etc/rc.d/init.d/ directory. If you installed from source, here is a startup script you can add to your OS's startup directory.
The line:
# daemon msyslogd $CONFIG $DEBUG $MARK
$IM_BSD $IM_DOORS $IM_LINUX $IM_STREAMS $IM_TCP
$IM_UDP $IM_UNIX
in the “msyslog” startup script was changed by adding the switches “-i udp -p 514 -i om_mysql”
# daemon msyslogd $CONFIG $DEBUG $MARK $IM_BSD $IM_DOORS
$IM_LINUX $IM_STREAMS $IM_TCP $IM_UDP
$IM_UNIX -i udp -p 514 -i om_mysql
-i udp -p 514 – Listen on the standard port 514 for incoming syslog messages via udp
-i om_mysql – load the mysql support module for logging to a mysql database
This was done before the existing syslog daemon is shutdown so that when it is stopped, the settings above will immediately take affect and remote logging will continue.
The normal syslog daemon was shutdown and myslogd started up immediately:
# /sbin/service syslogd stop ; /sbin/service msyslogd start
To ensure everything is still working run “tail -f” on the /var/log/messages file to see if logs from remote servers were being received:
# /usr/bin/tail -f /var/log/messages ^C
“tail -f” allows data to be viewed while a file is being appended.
The logging to mysql was setup by first creating a database called “logd”:
# /usr/bin/mysqladmin -p -u root create logd
Then the script supplied in the man page for the om_mysql module was loaded into the database.
# /usr/bin/mysql -p -u root logd < syslog.sql
The syslog.sql file contained this, I modified the supplied sql file to index the host, date, and message fields.:
mysql> CREATE TABLE syslogTB (
facility char(10), # OPTIONAL field for facility
priority char(10), # OPTIONAL field for priority
date date, # date of this log message
time time, # time of this message
host varchar(128), # host logging, If you have a host with
# 128 characters you probably
# have other issues to worry about than
#someone being l33t. ?
message text,
INDEX host_index (host),
INDEX date_index (date),
INDEX message_index (message (50)) , #Index the first 50 characters
seq int unsigned auto_increment primary key # optional sequencenumber
);
#Table to import host names
mysql> CREATE TABLE sysloghosts (
hostname varchar(128) # host logging, Same principles as
# above for a 128-character hostname. ?
);
The “sysloghosts” table is used as a dropdown list on the PHP search form. This is only run if new hosts are configured to log to julie. I retrieved the list from the /var/log/messages file with this command:
# /bin/awk ' { print $4 } ' /var/log/messages | sort
| uniq > /tmp/hosts.tmp
# /bin/chown mysql:mysql /tmp/hosts.tmp
The mysqld owner must hsve permissions to import the file into the database.
Log into the mysql logd database as a root user (not system root), delete the current hosts, and add new hosts file:
# /usr/bin/mysql -p -u root logd
Enter Password:
mysql> DELETE FROM sysloghosts;
mysql> LOAD DATA INFILE '/tmp/hosts.tmp'
INTO TABLE sysloghosts LINES TERMINATED BY 'n';
mysql> exit
Delete the temporary file:
# rm -f /tmp/hosts.tmp
The user “mysql” is used to insert the syslog data into the database. Also, the mysql user will be used to select data from the database using PHP.
Log into the database as the admin user and grant the user “mysql” rights to edit and update the “logd” database.
# /usr/bin/mysql -u root -p logd
Enter Password:
mysql> GRANT SELECT, INSERT on logd.* TO mysql@localhost
IDENTIFIED BY 'dahbadahba';
mysql> FLUSH PRIVILEGES;
the mysql user is allowed to select and insert data for the logd database (GRANT SELECT,INSERT on logd.*) from the localhost (TO mysql@localhost) with the password “dahbadahba” (IDENTIFIED BY ' dahbadahba ';) and then the privileges are enabled (Flush privileges;).
Syslog configuration file
In order for this to work the password for the database has to be kept in the syslog.conf file. A few changes were made to prevent normal users from viewing the syslog.conf file; thus, revealing the database password. (NOTE: Never use the system's root password for a database password)
First, the default permissions, on some unix systems, for /etc/syslog.conf are readable-writeable by root and readable by the group “root” and by the world (644). This was changed to 600:
# /bin/chmod 600 /etc/syslog.conf
Now it is only readable and writeable by root. Test it by trying to “cat” the file as a normal user:
# /bin/cat /etc/syslog.conf
Hopefully, the following message will be displayed:
# cat: /etc/syslog.conf: Permission denied
The options for logging to the mysql database can be added to the bottom of the /etc/syslog.conf file:
*.* %mysql -s localhost -u mysql -p dahbadahba -d logd -t syslogTB -D
*.* — log all syslog messages to the mysql database
-s – hostname
-u – user to log into the database as
-p – the database password
-d – the database name
-t – the database table name
-D – delay logging to the database (prevents overloading the mysql daemon if large numbers of syslog messages are received).
Restart the myslogd daemon:
# /sbin/service msyslogd restart
Watch the directory where your mysql databases are located and see if the file grows.
Restricting access to julie:
By default, the syslog daemon will accept syslog messages from any server. Be sure to use firewall rules to only allow syslog messages from the servers that should be logging to it. If your firewall supports it, use a threshold for logging to prevent a Denial-of-Service (DoS) attack. Also, the clients will listen on port 514 when sending log messages to the syslog server so be sure to firewall incoming requests to the client's syslog port, as well. No one should be connecting to the client's syslog port.
Only the system adminstrators should have access to julie. One reason is that root passwords and other user's passwords could be echoed into the syslog files because of those with fast fingers may type the password in the “username” or “Login:” field and hit “Enter”. Yes I am guilty.. That's the only time I have stopped the syslog daemon, opened /var/log/messages and mnaully deleted an entry. (NOTE: For sanity be sure your syslog files are chmod 600 and owned by root.)
The following rules restrict access to particular hosts: (NOTE: the policy is to DENY ALL)
# This restricts access to the entire web directory on julie
# You can configure as you like
<Directory “/var/www/html”>
Options Includes FollowSymLinks
AllowOverride None
Order deny,allow
deny from all # >8-)
# allow from (space delimited list of allowed hosts or networks)
allow from 192.168.0.2 192.168.0.3 john.server.com clint.server.com
</Directory>
The logs should also be reviewed via the web on julie over a secure connection. Your firewall can block or redirect incoming port 80 requests so access to the server is granted only over a secure connection.
Viewing syslog messages
Logs are viewed on julie using php to extract the data from the mysql database.
Create a directory under your root directory called “web-syslog”
# /bin/mkdir /var/www/html/web-syslog
Place these .php files there: (syslog-index.txt and syslog-search.txt). Create an include outside of your web directory:
# touch /var/www/gsyslog.php
Be sure this file is owned by the owner of the httpd daemon and readable-writeable by only that user. If you put this file somewhere else on your filesystem, be sure the owner of the httpd daemon has read access to the directory where it is located. This file will contain the password for the logd database. Add the following:
<?php
// if you change these variable names
// be sure the change the variables in
// the syslog-search.php file
$hostname = “localhost”;
$username = “mysql”;
$password = “dahbadahba”;
$databasename = “logd”;
?>
You can also find this script at http://www.sukkha.info/tap/gsyslog.txt.
A side note (special thanks to Steve Reed): For those of you running older versions of php, you might need to make the following modifications to syslog-search.php:
-$host = trim(addslashes(htmlspecialchars($_GET['host'])));
-$message = trim(addslashes(htmlspecialchars($_GET['message'])));
-$date = trim(addslashes(htmlspecialchars($_GET['date'])));
-$numresults = $_GET['numresults'];
-$start = $_GET['start'];
+$host = trim(addslashes(htmlspecialchars($host)));
+$message = trim(addslashes(htmlspecialchars($message)));
+$date = trim(addslashes(htmlspecialchars($date)));
+$numresults = $numresults;
+$start = $start;
Point your browser to: https://julie.domain.com/web-syslog/syslog-index.php. Hopefully you should see a page where you can select the hosts to retreive records for and an optional date and message field.
Other Notes:
Disable the standard syslog daemon from starting during bootup. You'll have to check your OS documentation for starting a stopping services during bootup. On Red Hat you can use the “chkconfig” program or “ntsysv”:
# /sbin/chkconfig –level 345 syslog off
or
# /usr/sbin/ntsysv
Uncheck the syslog option. If you upgrade your system then you will have to be sure that the msyslogd daemon starts up and not the standard syslog daemon on julie.
Conclusion:
Centralized logging and viewing of logs is very valuable, especially when you have dozens of servers to monitor. It can also serve as a place to look for errors if a server has crashed and can't be broke back on line. Take care to ensure that only necessary people are allowed to view the logs and if at all possible view the logs only over a secure connection. A database backend whether mysql, postgres, or whatever database gives you more power and control of how to display and manipulate the data that is being stored.
——————————————————————————–
Duane Dunston is a Computer Security Analyst at STG Inc. for the National Climatic Data Center in Asheville, NC. He received his B.A. and M.S. degrees from Pfeiffer University and he has his G SEC certification from SANS. He hangs out at Old Europe Cafe, Early Girl's eatery, Anntony's, and any place with good tea and hot choc olate.
Duane has been working in security for 5 years and wishes he had the funding for a “Basic Security Tour” so he could provide the wo rld with hands-on training on how to implement the security recommendations from the Sans Top 20 List of the most common vulnerabiliti es. He knows that applying these recommendations to any network can minimize the most common types of attacks. Not only does he enjoy his work in computer security, he also likes to get involved in its ever-growing technologies. Duane says, “Security is one of those jobs where you have to stay abreast of new technologies and new ways that attackers are compromising computer systems. Security keeps evolving and the industry has to keep up with it, that is why we need well-trained, evolving security professionals supportive manag ers to help us with this ongoing process”.