通过loganalyzer展示数据库中的日志

通过loganalyzer展示数据库中的日志

环境准备

源码包：loganalyzer-4.1.10.tar.gz

服务器	IP	描述
MariaDB-10.4.22	10.0.0.7	数据库服务器
rsyslog	10.0.0.17	日志服务器
httpd+php	10.0.0.27	httpd+php 服务器，安装loganalyzer展示web图形

1、在rsyslog服务器上安装连接mysql模块相关的程序包

[root@centos7 ~]#yum install -y rsyslog-mysql
[root@centos7 ~]#rpm -ql rsyslog-mysql
/usr/lib64/rsyslog/ommysql.so
/usr/share/doc/rsyslog-8.24.0/mysql-createDB.sql

#查看sql脚本文件内容
[root@centos7 ~]#cat /usr/share/doc/rsyslog-8.24.0/mysql-createDB.sql
CREATE DATABASE Syslog;
USE Syslog;
CREATE TABLE SystemEvents
(
        ID int unsigned not null auto_increment primary key,
        CustomerID bigint,
        ReceivedAt datetime NULL,
        DeviceReportedTime datetime NULL,
        Facility smallint NULL,
        Priority smallint NULL,
        FromHost varchar(60) NULL,
        Message text,
        NTSeverity int NULL,
        Importance int NULL,
        EventSource varchar(60),
        EventUser varchar(60) NULL,
        EventCategory int NULL,
        EventID int NULL,
        EventBinaryData text NULL,
        MaxAvailable int NULL,
        CurrUsage int NULL,
        MinUsage int NULL,
        MaxUsage int NULL,
        InfoUnitID int NULL ,
        SysLogTag varchar(60),
        EventLogType varchar(60),
        GenericFileName VarChar(60),
        SystemID int NULL
);

CREATE TABLE SystemEventsProperties
(
        ID int unsigned not null auto_increment primary key,
        SystemEventID int NULL ,
        ParamName varchar(255) NULL ,
        ParamValue text NULL
);

#将sql脚本复制到数据库服务器上
[root@centos7 ~]#scp /usr/share/doc/rsyslog-8.24.0/mysql-createDB.sql 10.0.0.7:/data

2、准备MySQL Server

#在mariadb数据库服务器上创建相关数据库和表，并授权rsyslog能连接至当前服务器
MariaDB [mysql]> source /data/mysql-createDB.sql
MariaDB [Syslog]> show databases;
+--------------------+
| Database           |
+--------------------+
| Syslog             |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
MariaDB [Syslog]> show tables;
+------------------------+
| Tables_in_Syslog       |
+------------------------+
| SystemEvents           |
| SystemEventsProperties |
+------------------------+

MariaDB [Syslog]> GRANT ALL PRIVILEGES ON Syslog.* TO 'rsyslog'@'10.0.0.%' IDENTIFIED BY 'rsyslog';
MariaDB [Syslog]> FLUSH PRIVILEGES;

3、配置日志服务器将日志发送至指定数据库

#配置rsyslog将日志保存到mysql中
[root@centos7 ~]#vim /etc/rsyslog.conf
#### MODULES ####
#在 MODULES 语言下面，如果是 CentOS 7加下面行
$ModLoad ommysql

#在RULES语句块加下面行的格式
#facility.priority :ommysql:DBHOST,DBNAME,DBUSER, PASSWORD
*.info;mail.none;authpriv.none;cron.none     :ommysql:10.0.0.7,Syslog,rsyslog,rsyslog

[root@centos7 ~]#systemctl restart rsyslog.service

4、测试

#在rsyslog日志服务器上生成日志
[root@centos7 ~]#logger "This is a test log from 10.0.0.17"

#在数据库服务器上查询到上面的测试日志
MariaDB [Syslog]> select * from SystemEvents\G
*************************** 1. row ***************************
                ID: 1
        CustomerID: NULL
        ReceivedAt: 2022-03-13 23:47:41
DeviceReportedTime: 2022-03-13 23:47:41
          Facility: 3
          Priority: 6
          FromHost: centos7
           Message: Stopping System Logging Service...
        NTSeverity: NULL
        Importance: NULL
       EventSource: NULL
         EventUser: NULL
     EventCategory: NULL
           EventID: NULL
   EventBinaryData: NULL
      MaxAvailable: NULL
         CurrUsage: NULL
          MinUsage: NULL
          MaxUsage: NULL
        InfoUnitID: 1
         SysLogTag: systemd:
      EventLogType: NULL
   GenericFileName: NULL
          SystemID: NULL
*************************** 2. row ***************************
                ID: 2
        CustomerID: NULL
        ReceivedAt: 2022-03-13 23:47:41
DeviceReportedTime: 2022-03-13 23:47:41
          Facility: 5
          Priority: 6
          FromHost: centos7
           Message:  [origin software="rsyslogd" swVersion="8.24.0-55.el7" x-pid="1292" x-info="http://www.rsyslog.com"] exiting on signal 15.
        NTSeverity: NULL
        Importance: NULL
       EventSource: NULL
         EventUser: NULL
     EventCategory: NULL
           EventID: NULL
   EventBinaryData: NULL
      MaxAvailable: NULL
         CurrUsage: NULL
          MinUsage: NULL
          MaxUsage: NULL
        InfoUnitID: 1
         SysLogTag: rsyslogd:
      EventLogType: NULL
   GenericFileName: NULL
          SystemID: NULL
*************************** 3. row ***************************
                ID: 3
        CustomerID: NULL
        ReceivedAt: 2022-03-13 23:47:41
DeviceReportedTime: 2022-03-13 23:47:41
          Facility: 3
          Priority: 6
          FromHost: centos7
           Message: Stopped System Logging Service.
        NTSeverity: NULL
        Importance: NULL
       EventSource: NULL
         EventUser: NULL
     EventCategory: NULL
           EventID: NULL
   EventBinaryData: NULL
      MaxAvailable: NULL
         CurrUsage: NULL
          MinUsage: NULL
          MaxUsage: NULL
        InfoUnitID: 1
         SysLogTag: systemd:
      EventLogType: NULL
   GenericFileName: NULL
          SystemID: NULL
*************************** 4. row ***************************
                ID: 4
        CustomerID: NULL
        ReceivedAt: 2022-03-13 23:47:41
DeviceReportedTime: 2022-03-13 23:47:41
          Facility: 3
          Priority: 6
          FromHost: centos7
           Message: Starting System Logging Service...
        NTSeverity: NULL
        Importance: NULL
       EventSource: NULL
         EventUser: NULL
     EventCategory: NULL
           EventID: NULL
   EventBinaryData: NULL
      MaxAvailable: NULL
         CurrUsage: NULL
          MinUsage: NULL
          MaxUsage: NULL
        InfoUnitID: 1
         SysLogTag: systemd:
      EventLogType: NULL
   GenericFileName: NULL
          SystemID: NULL
*************************** 5. row ***************************
                ID: 5
        CustomerID: NULL
        ReceivedAt: 2022-03-13 23:47:41
DeviceReportedTime: 2022-03-13 23:47:41
          Facility: 5
          Priority: 6
          FromHost: centos7
           Message:  [origin software="rsyslogd" swVersion="8.24.0-55.el7" x-pid="1905" x-info="http://www.rsyslog.com"] start
        NTSeverity: NULL
        Importance: NULL
       EventSource: NULL
         EventUser: NULL
     EventCategory: NULL
           EventID: NULL
   EventBinaryData: NULL
      MaxAvailable: NULL
         CurrUsage: NULL
          MinUsage: NULL
          MaxUsage: NULL
        InfoUnitID: 1
         SysLogTag: rsyslogd:
      EventLogType: NULL
   GenericFileName: NULL
          SystemID: NULL
*************************** 6. row ***************************
                ID: 6
        CustomerID: NULL
        ReceivedAt: 2022-03-13 23:47:41
DeviceReportedTime: 2022-03-13 23:47:41
          Facility: 3
          Priority: 6
          FromHost: centos7
           Message: Started System Logging Service.
        NTSeverity: NULL
        Importance: NULL
       EventSource: NULL
         EventUser: NULL
     EventCategory: NULL
           EventID: NULL
   EventBinaryData: NULL
      MaxAvailable: NULL
         CurrUsage: NULL
          MinUsage: NULL
          MaxUsage: NULL
        InfoUnitID: 1
         SysLogTag: systemd:
      EventLogType: NULL
   GenericFileName: NULL
          SystemID: NULL
*************************** 7. row ***************************
                ID: 7
        CustomerID: NULL
        ReceivedAt: 2022-03-13 23:56:19
DeviceReportedTime: 2022-03-13 23:56:19
          Facility: 1
          Priority: 5
          FromHost: centos7
           Message: This is a test log from 10.0.0.17
        NTSeverity: NULL
        Importance: NULL
       EventSource: NULL
         EventUser: NULL
     EventCategory: NULL
           EventID: NULL
   EventBinaryData: NULL
      MaxAvailable: NULL
         CurrUsage: NULL
          MinUsage: NULL
          MaxUsage: NULL
        InfoUnitID: 1
         SysLogTag: root:
      EventLogType: NULL
   GenericFileName: NULL
          SystemID: NULL
7 rows in set (0.002 sec)

#在rsyslog日志服务器上查询到上面的测试日志
[root@centos7 ~]#tail -f /var/log/messages
Mar 13 22:48:54 centos7 yum[1739]: Installed: rsyslog-mysql-8.24.0-55.el7.x86_64
Mar 13 23:01:01 centos7 systemd: Started Session 3 of user root.
Mar 13 23:47:41 centos7 systemd: Stopping System Logging Service...
Mar 13 23:47:41 centos7 rsyslogd: [origin software="rsyslogd" swVersion="8.24.0-55.el7" x-pid="1292" x-info="http://www.rsyslog.com"] exiting on signal 15.
Mar 13 23:47:41 centos7 systemd: Stopped System Logging Service.
Mar 13 23:47:41 centos7 systemd: Starting System Logging Service...
Mar 13 23:47:41 centos7 rsyslogd: [origin software="rsyslogd" swVersion="8.24.0-55.el7" x-pid="1905" x-info="http://www.rsyslog.com"] start
Mar 13 23:47:41 centos7 systemd: Started System Logging Service.
Mar 13 23:56:19 centos7 root: This is a test log from 10.0.0.17
Mar 14 00:01:02 centos7 systemd: Started Session 4 of user root.

5、安装 php和相关软件包

#在10.0.0.27主机上安装httpd, php和相关软件包
[root@centos7 ~]#yum -y install httpd php-fpm php-mysql php-gd
[root@centos7 ~]#systemctl enable --now httpd php-fpm

6、安装 LogAnalyzer

在10.0.0.27主机上安装LogAnalyzer

#在10.0.0.27主机上安装LogAnalyzer
#从http://loganalyzer.adiscon.com/downloads/ 下载loganalyzer-4.1.10.tar.gz
[root@centos7 ~]#tar xvf loganalyzer-4.1.10.tar.gz
[root@centos7 ~]#cd loganalyzer-4.1.10/
[root@centos7 loganalyzer-4.1.10]#ls
ChangeLog  contrib  COPYING  doc  INSTALL  src
[root@centos7 loganalyzer-4.1.10]#mv src/ /var/www/html/log
[root@centos7 loganalyzer-4.1.10]#touch /var/www/html/log/config.php
[root@centos7 loganalyzer-4.1.10]#chmod 666 /var/www/html/log/config.php

7、安全加固

[root@centos7 loganalyzer-4.1.10]#chmod 644 /var/www/html/log/config.php