Remote Syslog with MySQL and PHP

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 软件下载:http://www.sfr-fresh.com/unix/privat/msyslog-1.
软件下载:
文章作者:Duane Dunston
文章备注:如果觉得这种格式不爽 邪恶八进制给您做了一个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. 8-)
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.  8-)
);



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 &#39; { print $4 } &#39; /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 &#39;/tmp/hosts.tmp&#39;
     INTO TABLE sysloghosts LINES TERMINATED BY &#39;n&#39;;
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 &#39;dahbadahba&#39;;
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 &#39; dahbadahba &#39;;) 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&#39;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&#39;s syslog port, as well. No one should be connecting to the client&#39;s syslog port.

Only the system adminstrators should have access to julie. One reason is that root passwords and other user&#39;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&#39;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[&#39;host&#39;])));
-$message = trim(addslashes(htmlspecialchars($_GET[&#39;message&#39;])));
-$date = trim(addslashes(htmlspecialchars($_GET[&#39;date&#39;])));
-$numresults = $_GET[&#39;numresults&#39;];
-$start = $_GET[&#39;start&#39;];

+$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&#39;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&#39;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&#39;s eatery, Anntony&#39;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".
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
7月前
|
关系型数据库 MySQL PHP
PHP 原生操作 Mysql
PHP 原生操作 Mysql
81 0
|
7月前
|
关系型数据库 MySQL 数据库连接
PHP 原生连接 Mysql
PHP 原生连接 Mysql
107 0
|
7月前
|
关系型数据库 MySQL Unix
PHP MySql 安装与连接
PHP MySql 安装与连接
127 0
|
3月前
|
关系型数据库 MySQL PHP
|
4天前
|
PHP
web简易开发——通过php与HTML+css+mysql实现用户的登录,注册
web简易开发——通过php与HTML+css+mysql实现用户的登录,注册
|
7月前
|
关系型数据库 MySQL 数据库连接
PHP 原生操作 Mysql 增删改查案例
PHP 原生操作 Mysql 增删改查案例
87 0
|
2月前
|
监控 关系型数据库 MySQL
PHP与MySQL的结合:实现局域网上网行为监控软件的数据库管理
在当今信息化时代,网络安全日益成为重要的话题。为了有效监控和管理局域网上网行为,开发一个基于PHP和MySQL的数据库管理系统是一个理想的选择。本文将介绍如何结合PHP和MySQL,开发一款简单而高效的局域网上网行为监控软件,并重点关注数据库管理方面的实现。
184 0
|
4月前
|
NoSQL 关系型数据库 应用服务中间件
Linux安装 OpenResty、Nginx、PHP、Mysql、Redis、Lua、Node、Golang、MongoDB、Kafka等
Linux安装 OpenResty、Nginx、PHP、Mysql、Redis、Lua、Node、Golang、MongoDB、Kafka等
88 0
|
6月前
|
关系型数据库 MySQL PHP
PHP环境搭建(安装MySQL)
PHP环境搭建(安装MySQL)
42 0
|
7月前
|
关系型数据库 MySQL PHP
PHP 原生操作 Mysql 分页数据案例
PHP 原生操作 Mysql 分页数据案例
96 1