Displaying logs in database through log analyzer

Environmental preparation

Three hosts
    A log server, implemented by the previous case, IP: 192.168.39.7,
    A database server, using the last case, IP: 192.168.39.27,
    An httpd+php server, and log analyzer is installed to display web graphics, IP: 192.168.39.77
    
    Log server tool loganalyzer-4.1.8.tar.gz

Prepare server:

# log server
[root@centos7 ~]$hostname rsyslog
[root@centos7 ~]$exit
[root@rsyslog ~]$
# database server
[root@centos7 ~]$hostname mysql
[root@centos7 ~]$exit
[root@mysql ~]$
# websrv server
[root@centos ~]# hostname websrv
[root@centos ~]# exit
[root@websrv ~]$

Log server:

1. Install the package related to connecting mysql module on rsyslog server

[root@rsyslog ~]$yum install rsyslog-mysql -y

1. Find sql script to send to database

# Download auxiliary software search
[root@rsyslog ~]$yum install mlocate
[root@rsyslog ~]$updatedb   # Update database information
[root@rsyslog ~]$locate mysql-createDB.sql   # Using locatedb to find the path where the script file is stored
/usr/share/doc/rsyslog-8.24.0/mysql-createDB.sql

[root@rsyslog ~]$cat /usr/share/doc/rsyslog-8.24.0/mysql-createDB.sql   # Script file content
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
);

[root@rsyslog ~]$scp /usr/share/doc/rsyslog-8.24.0/mysql-createDB.sql 192.168.39.27:/root  # Send to database server
The authenticity of host '192.168.39.27 (192.168.39.27)' can't be established.
ECDSA key fingerprint is SHA256:XVNFzEbN3eaCzTwYrlQg2SzHZXHbd0dS0YKLuIOXVr0.
ECDSA key fingerprint is MD5:df:4d:86:ba:0c:e6:c1:a2:6c:45:71:e9:ac:ea:1d:a5.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.39.27' (ECDSA) to the list of known hosts.
root@192.168.39.27's password: 
mysql-createDB.sql                            100% 1046   588.1KB/s   00:00    

1. Modify the configuration file to start the service module and write it as database information (used to send log information to the database server)

# The imjournal module bellow is now used as a message source instead of imuxsock.
$ModLoad ommysql   # Add this line

# Don't log private authentication messages!
*.info;mail.none;authpriv.none;cron.none                /var/log/messages
*.info;mail.none;authpriv.none;cron.none                :ommysql:192.168.39.27,Syslog,syslog,taotaobao

#Configure rsyslog to save logs to mysql
[root@centos8 ~]#vim /etc/rsyslog.conf
####MODULES####
#Under MODULES, if it's CentOS 8, add the following lines
module(load="ommysql")
#Under MODULES, if it's CentOS 7, add the following lines
$ModLoad ommysql

Database server:

1. mount this database

[root@mysql ~]$yum install mariadb-server -y

[root@mysql ~]$systemctl start mariadb.service 

1. Execute the test script

[root@mysql ~]$mysql <
anaconda-ks.cfg     .cshrc              .tcshrc
.bash_history       ifcfg-eth0          .viminfo
.bash_logout        init_env_191113.sh  .vimrc
.bash_profile       mysql-createDB.sql  
.bashrc             .pki/               
[root@mysql ~]$mysql < mysql-createDB.sql   # Import database directly

1. Create the account used by Syslog Library

[root@mysql ~]$mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant all on Syslog.* to syslog@'192.168.39.%' identified by 'taotaobao';  # Note that the database name here corresponds to the database name in the executed sql script.
Query OK, 0 rows affected (0.00 sec)

Test whether the log server and database are connected:

• The Syslog database is empty before the service is started

MariaDB [Syslog]> show tables;
+------------------------+
| Tables_in_Syslog       |
+------------------------+
| SystemEvents           |
| SystemEventsProperties |
+------------------------+
2 rows in set (0.00 sec)

MariaDB [Syslog]> 

MariaDB [Syslog]> SELECT COUNT(*) FROM SystemEvents;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

• Start rsyslog service

[root@rsyslog ~]$systemctl restart rsyslog.service

[root@rsyslog ~]$logger "this is a test log" # Log server trigger log (logger trigger log command)

• Check whether the database has data (you can test it several times)

MariaDB [Syslog]> SELECT COUNT(*) FROM SystemEvents;
+----------+
| COUNT(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

websrv server:

1. Decompress tool

[root@websrv ~]# ll
-rw-r--r--  1 root root 2943754 Oct 10 13:04 loganalyzer-4.1.8.tar.gz # web interface log service Toolkit
[root@websrv ~]# tar xvf loganalyzer-4.1.8.tar.gz

1. Install the required services to implement LAMP architecture

# Note that the php version here must be the same or higher than the 5.6 version or this software does not support
[root@websrv ~]# yum install httpd php56-php-fpm.x86_64 php56-php-mysqlnd.x86_64 -y

1. Cut the directory required by loganalyzer to the website directory

[root@websrv loganalyzer-4.1.8]# ll
total 100
drwxrwxr-x 13 root root 4096 Sep 26 23:41 src
-rw-rw-r-- 1 root root 48571 Sep 26 23:41 ChangeLog
drwxrwxr-x 2 root root    43 Sep 26 23:41 contrib
-rw-rw-r-- 1 root root 35497 Sep 26 23:41 COPYING
drwxrwxr-x 3 root root   258 Sep 26 23:41 doc
-rw-rw-r-- 1 root root  8449 Sep 26 23:41 INSTALL

[root@websrv loganalyzer-4.1.8]# mv src/ /var/www/html/log  # Switch to website directory

[root@websrv loganalyzer-4.1.8]# ll /var/www/html/
total 4
drwxrwxr-x 13 root root 4096 Sep 26 23:41 log

# Modify owner to apache
[root@websrv loganalyzer-4.1.8]# cd /var/www/html/
[root@websrv html]# chown -R apache.apache log/

1. Modify httpd configuration file to support PHP FPM

# Add index.php
<IfModule dir_module>
    DirectoryIndex index.php index.html
</IfModule>

# Find the module location and add the middle three lines.
<IfModule mime_module>
    AddType application/x-httpd-php .php
    AddType application/x-httpd-php-source .phps
    ProxyRequests Off
</IfModule>

1. Create call module file (or understand as reverse proxy)

[root@websrv html]# vim /etc/httpd/conf.d/fcgi.conf
Directoryindex index.php
Proxyrequests off
ProxyPassMatch ^/(.*\.php)$ fcgi://127.0.0.1:9000/var/www/html/$1

The fourth and fifth configurations are added to centos7. CentOS 8 is configured by default without modification.

1. web interface test
   

• Carry out the test
  
• If it's wrong, it may show that there are no files for writing
  
• Solution (due to the lack of this file)

[root@websrv html]# cd log/
[root@websrv log]# pwd
/var/www/html/log
[root@websrv log]# touch config.php  # Create this file
[root@websrv log]# chmod 666 config.php # Give the file owner read and write permission (change according to work requirements)

• What is the format of the last displayed log
  
• Fill in database information
  
• View added table names

[root@centos7 ~]$mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use Syslog;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [Syslog]> show tables;
+------------------------+
| Tables_in_Syslog       |
+------------------------+
| SystemEvents           |   # This table is used to store log information
| SystemEventsProperties |
+------------------------+
2 rows in set (0.00 sec)

• Success
  
  
• View report interface (but no pie chart is not clear)
  

[root@websrv html]# yum install php56-php-gd.x86_64 -y  # Packages that support pie drawing
[root@websrv html]# systemctl restart httpd.service php56-php-fpm.service  # Restart service

• Refresh the web interface (this will produce a clearer pie chart)