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

Hi% username%,

Today I would like to talk about a rather trivial task of collecting logs from decentralized Squid proxy servers and the pitfalls we encountered.

What do we have:

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

As you can see 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 it was possible to find this pearl scripts and the scheme , we actually take them as a basis:

1. We put the dependencies on the squid proxy server:
   yum install perl perl-Readonly* perl-URI perl-YAML perl-DBI perl-Carp perl-DBD-mysql
   
2. Then we arrange scripts and a config for places to connect to the database:
   
   cp log_mysql_daemon.pl /usr/libexec/squid/log_mysql_daemon.pl
   
   Give 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 for connecting the script to the database:
   
   vi /etc/squid/log_mysql_daemon.conf

host: ""
database: "squid_log"
table: "access_log"
user: "squid"
pass: ""
   
   
4. We create the database, import the scheme and create the user:
   
   mysql -p
create database squid_log;
CREATE USER 'squid'@'%' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON squid_log.* TO 'squid'@'';
GRANT ALL PRIVILEGES ON squid_log.* TO 'squid'@'';
GRANT ALL PRIVILEGES ON squid_log.* TO 'squid'@'';
GRANT ALL PRIVILEGES ON squid_log.* TO 'squid'@'';
GRANT ALL PRIVILEGES ON squid_log.* TO 'squid'@'';
exit

cat log_mysql_daemon-table.sql log_mysql_daemon-views.sql | mysql -p squid_log
   
   
5. 
   Go to the evil side squid proxy
   
   config Add the configuration for the daemon Parse: acl dontLog http_status 403 407 - an optional line, removes errors from the log going to the database associated with eror codes 403, 407. The database will grow in geometric progression, but will not be reported carry no value logformat squid_mysql% ts.% 03tu% 6tr%> a% Ss% 03Hs%
   
   vi /etc/squid/squid.conf
acl dontLog http_status 403 407
logformat squid_mysql %ts.%03tu %6tr %>a %Ss %03Hs % 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
   
   
   
   
   - set the format for the log, one of the important conditions for multiple squid servers is the last value with the name of the server from which the logs come. There is no functionality in the original scripts, so we twist this line and the script itself as follows:
   
   in /usr/libexec/squid/log_mysql_daemon.pl add squid-server access_log /var/log/squid/access.log squid to the column settings - Leave the local logs for debugging and cases of problems with the database, they have rotation turned on so access_log daemon will not be superfluous: /etc/squid/log_mysql_daemon.conf squid_mysql! dontLog - actually the line itself to the daemon configuration. Please note that ! DontLog cancels logging 403,407 only
   
   # 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
);
   
   
   
   for the database, so in case of debugging, you can easily use the local logs
   
   logfile_daemon /usr/libexec/squid/log_mysql_daemon.pl - the path to the pearl daemon
   
   
6. Reread the squid proxy configs and get the desired result: Available tables and views:
   
   squid reconfigure
squid -k reconfigure
   
   
   
   
   
   
   

Conclusion:

As you can see, the data is now located centrally in the database and is 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 to read the sequel to Squid Proxy?