Linux: Sending Squid Logs to a MySQL Database Using syslog-ng

What I am going to write below is how I configured squid and syslog-ng to send squid logs to mysql database, so that i can further analyze them.

SQUID CONFIGURATION

First I changed log format of squid access logs file by editing squid’s configuration file usually located at /etc/squid/squid.conf

vi /etc/squid/squid.conf

Then search for “logformat” I configured my squid server’s log format to something  looks like this:

logformat custom %{%Y-%m-%d %H:%M:%S}tl,%tr,%>a,%>A,%rm,%ru,%{Content-Length}h,%{User-Agent}>h

Where, custom is a name for this format. This format will provide me following information (separated by comma):

date,response time(millisec),user ip,user host,request method,request url,file size,downloaded size,squid status,referer,user-agent

Note:You can change log format to whatever you want. After changing log format, now search for “access_log” and modify logformat name to what you have given above. In my case, its “custom” so:

access_log /var/log/squid/access.log custom

Where,

access_log is a syntax to configure squid access logs.

/var/log/squid/access.log is the path for squid access logs file.

custom is the name for logformat we’ve just created. Squid configuration is now complete.

 

 

SYSLOG-NG CONFIGURATION

I am not covering how to install syslog-ng, so, we are moving now to syslog-ng configuration.

NOTE: Install libdbi-mysql package along with syslog-ng, or else, syslog-ng wont send logs to mysql server.

Open sysnlog-ng configuration file, usually located at /etc/syslog-ng/syslog-ng.conf

Syslog-ng configuration is divided into three parts, i.e. SOURCE, DESTINATION, and PARSER.

First, we will create data source for syslog-ng, which is squid.

 

Write this (syslog-ng.conf):

source all { file("/var/log/squid/access.log"); };

Where,

source is the syntax to define a data source.

all is the name for this data source we are creating. You can name it anything.

file(“/var/log/squid/access.log”) is the source file where access logs are located. Now we will configure destination.

NOTE:Before configuring destination, what we need to understand is how syslog-ng interpret data and assign them into variables? Syslog-ng have its own default variables it assigned by automatically inspecting data source, but we are going to define our own variables. Write this (in syslog-ng.conf):

destination d_mysql {  
sql(type(mysql) 
host("db.example.org") 
username("db_username") 
password("db_password") 
database("syslog") 
table("squid") 
columns("date", "responsetime", "userip", "userhost", "requestmethod", 
"requesturl",  "filesize", "downloaded", "squidstatus", "referer", "useragent") 
values("$YEAR-$MONTH-$DAY $SQDATE", "$SQRESPONSETIME", "$SQUSERIP", "$SQUSERHOST", 
"$SQREQUESTMETHOD",  "$SQREQUESTURL", "$SQFILESIZE", "$SQDOWNLOAD") };

Where,

destination is a syntax to define data destination.

d_mysql is the name for this destination

sql type is a type of database

columns are table’s fields

values are the variables syslog-ng will assign data in.

 

Now we are going to define parser so that syslog-ng can understand squid log format and assigned them in respective variables.

Note:This is where we will create our own variables.

 

Write this (in syslog-ng.conf):

parser p_squid {  
csv-parser(columns( "SQDATE", "SQRESPONSETIME", "SQUSERIP", "SQUSERHOST", 
"SQREQUESTMETHOD",  "SQREQUESTURL", "SQFILESIZE", "SQDOWNLOADED", 
"SQSQUIDSTATUS", "SQREFERER", "SQUSERAGENT") 
flags(escape-none) 
delimiters(",") 
quote-pairs('""[]') 
); };

 

Where,

parser is the syntax for defining parser.

p_squid is the name for parser.

csv-parser(columns( are the variables we want syslog-ng to grab data in.

delimitersare simply column delimiter, which is in our case comma.

 

Finally, we will join these 3 to make this thing work. Write this (in syslog-ng.conf):

log { source(all); parser(p_squid); destination(d_mysql); };

where,

all is the name of our data source

p_squid is the name of parser

d_mysql is the name of destination

 

 

MYSQL DATABASE CONFIGURATION

Now, as our final step, we will create database for syslog-ng with a table.

 

Login to mysql and run these commands in this sequence:

create database syslog;
CREATE TABLE IF NOT EXISTS `squid` (
 `serial` int(11) NOT NULL AUTO_INCREMENT,
 `date` text NOT NULL,
 `responsetime` text NOT NULL,
 `userip` text NOT NULL,
 `userhost` text NOT NULL,
 `requestmethod` text NOT NULL,
 `requesturl` text NOT NULL,
 `filesize` text NOT NULL,
 `downloaded` text NOT NULL,
 `squidstatus` text NOT NULL,
 `referer` text NOT NULL,
 `useragent` text NOT NULL,
 PRIMARY KEY (`serial`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=36 ;

This is it. Now restart squid, and then syslog-ng service, and check syslog-ng logs for possible errors by typing this command:

tail -f /var/log/syslog

That’s all :) Please give your feed back in comments, Thank you.

Leave a Reply

Name (required)

Mail (will not be published) (required)

Website

Notify me of follow-up comments by email.

Notify me of new posts by email.