Centralized log storage for Squid Proxy or how we wrapped the logs in the database

Hi% username%,

Today I would like to tell you about the rather trivial task of collecting logs from decentralized Squid proxy servers and the pitfalls we faced.

What we have:
')

1. Squid-hq
2. Squid-br1
3. Squid-br2
4. Squid-br3
5. Squid-br4
6. Squid-db

As can be seen from the list, there are 5 squid proxy servers in different remote offices, and 1 database for collecting logs. All OS CentOS 7.3, squid proxy from 3.3.8 to 3.5.26, Squid-db - with installed mariadb 5.6

From the fact that we managed to find this pearl barley scripts and schema , we actually take them as a basis:

1. Install dependencies on squid proxy servers:
   yum install perl perl-Readonly* perl-URI perl-YAML perl-DBI perl-Carp perl-DBD-mysql
   
2. Then we put in place the scripts and the config for connecting to the database:
   
   cp log_mysql_daemon.pl /usr/libexec/squid/log_mysql_daemon.pl
   
   Giving rights:
   
   chmod +x /usr/libexec/squid/log_mysql_daemon.pl
chown squid:squid /usr/libexec/squid/log_mysql_daemon.pl
   
   
3. Next, create a config file to connect the script to the database:
   
   vi /etc/squid/log_mysql_daemon.conf

host: "<database-ip>"
database: "squid_log"
table: "access_log"
user: "squid"
pass: "<squid-passwd>"
   
   
4. Create a database, import the schema and create a user:
   
   mysql -p
create database squid_log;
CREATE USER 'squid'@'%' IDENTIFIED BY '<squid-passwd>';
GRANT ALL PRIVILEGES ON squid_log.* TO 'squid'@'<Squid-hq-ip>';
GRANT ALL PRIVILEGES ON squid_log.* TO 'squid'@'<Squid-br1-ip>';
GRANT ALL PRIVILEGES ON squid_log.* TO 'squid'@'<Squid-br2-ip>';
GRANT ALL PRIVILEGES ON squid_log.* TO 'squid'@'<Squid-br3-ip>';
GRANT ALL PRIVILEGES ON squid_log.* TO 'squid'@'<Squid-br4-ip>';
exit

cat log_mysql_daemon-table.sql log_mysql_daemon-views.sql | mysql -p squid_log
   
   
5. 
   Moving to the evil side squid proxy config
   
   Add configuration for daemon
   
   vi /etc/squid/squid.conf
acl dontLog http_status 403 407
logformat squid_mysql %ts.%03tu %6tr %>a %Ss %03Hs %<st %rm %ru %un %Sh %<A %mt squid-hq
access_log /var/log/squid/access.log squid
access_log daemon:/etc/squid/log_mysql_daemon.conf squid_mysql !dontLog
logfile_daemon /usr/libexec/squid/log_mysql_daemon.pl
   
   Parse:
   acl dontLog http_status 403 407 is an optional string, removes errors from the log going to the database associated with the eror codes 403, 407. The base will grow in geometric progression and will not carry any value for reporting
   
   logformat squid_mysql% ts.% 03tu% 6tr%> a% Ss% 03Hs% <st% rm% ru% un% Sh% <A% mt squid-hq - set the format to the log, one of the important conditions for multiple squid servers is the last value with the name of the server from which logs come. In the original scripts, there is no functionality, so we wind up this line and the script itself as follows:
   
   in /usr/libexec/squid/log_mysql_daemon.pl we add squid-server to the column setup
   
   # fields that we should have in the database table
# this list depends on the log format configuration
my @required_fields = qw(
id
time_since_epoch
response_time
client_src_ip_addr
squid_request_status
http_status_code
reply_size
request_method
request_url
username
squid_hier_status
server_ip_addr
mime_type
squid_server
);
   
   access_log /var/log/squid/access.log squid - We leave local logs for debugs and cases of problems with the database, they have rotations enabled, so they won’t be superfluous
   
   access_log daemon: /etc/squid/log_mysql_daemon.conf squid_mysql! dontLog - the actual line itself to the configuration of the daemon. Please note that ! DontLog cancels logging 403,407 only for the database, so in the case of debugging you can easily use local logs
   
   logfile_daemon /usr/libexec/squid/log_mysql_daemon.pl - path to the pearl-barley daemon
   
   
6. Rereading squid proxy configs
   
   squid reconfigure
squid -k reconfigure
   
   and we get the desired result:
   
   
   Available tables and views:
   
   

Conclusion:

As you can see, the data are now centrally located in the database and easily accessible for processing. Next, we plan to write a frontend for filtering and exporting data (reporting). In general, the article was written and compiled from various sources, unfortunately I could not find everything together anywhere, so I consider it advisable to leave it here.

Would you be interested in reading the continuation of a series of articles about the Squid Proxy?