ISPConfig – Apache-Logfiles and syslog 1

By default ISPConfig is using vlogger for writing apache-logs as file and to store some data to the database.

I don´t like this way as it stores in a multi-server setup the logs only on each server. So if you´re already running a centralisied log-server, you will never get all logs forward to your log-server. On the other hand you can´t create full statistics with spplitted logs. To make sure, you´re stats contains all access-log-entries, you must first fetch them into one file.

So i replaced vlogger with syslog-ng. First you need to change vlogger to logger in “CustomLog”. The LogFormat should not be changed:

LogFormat "%v %h %l %u %t \"%r\" %>s %B \"%{Referer}i\" \"%{User-Agent}i\"" combined_ispconfig


Change /usr/local/ispconfig/server/conf/apache_ispconfig.conf.master:

CustomLog "| /bin/logger -t apache2" combined_ispconfig


and also

/etc/apache2/sites-available/ispconfig.vhost:

CustomLog "| /bin/logger -t apache2" combined_ispconfig


By now all logs were sent via logger to syslog-ng (or any other syslog-daemon).

I write an access-log for each Domain. So i split the incoming message. For this i use the pattern-db. You can also use a csv-parser, but the pattern-db is much faster. As long as you´re running only a few low-traffic-domains, you can easily setup the csv-parser; there`s no need for the more complicated patttern-db. In the following i you how to setup it up using a csv-parser (i won´t do this with > 200 log-entries/minute).

On the end of this post you can easily download the config-files and also the needed pattern. cut & paste isn´t really needed. 😉

The destinations for syslog:

destination d_apache-logs {
file("/var/log/ispconfig/httpd/${.apache.domain}/${YEAR}${MONTH}${DAY}-access.log"
template("${.apache.client_ip} ${.apache.ident_name} ${.apache.user_name} ${.apache.timestamp} ${.apache.timestamp2} \"${.apache.request_url}\" ${.apache.request_status} ${.apache.content_length} \"${.apache.referer}\" \"${.apache.user_agent}\"\n")
template_escape(yes)
perm(0644));
};


All logs were written to /var/log/ispconfig/httpd/DOMAIN.

Granted, the template splits the log into many parts that are not absolutely necessary. It doesn´t harm to have any part of the log in your own variables. i other posts in this blog you may need them.

Again the syslog-ng.conf:

destination d_ispconfig_apache-stat {
program("/root/scripts/syslog/sql-log.sh"
template("INSERT INTO web_traffic VALUES ('${.apache.domain}',curdate(),${.apache.content_length}) ON duplicate KEY UPDATE traffic_bytes=traffic_bytes+${.apache.content_length};\n"));
};


Instead of “program” you can also call mysql with the matching parameters.

This will look like:

destination d_ispconfig_apache-stat {
program("/usr/bin/mysql -uispconfig -pPASSWORD dbispconfig"
template("INSERT INTO web_traffic VALUES ('${.apache.domain}',curdate(),${.apache.content_length}) ON duplicate KEY UPDATE traffic_bytes=traffic_bytes+${.apache.content_length};\n"));
};


Update 02.01.2013
fundamental this should also work with the mysql-destination from syslog-ng . By now i´ve no idea, how to realisize a”ON DUPLICATE KEY UPDATE” definieren kann.

Syslog-ng´s sql-driver currently doesn´t support “ON DUPLICATE KEY UPDATE”. At this moment you must use the program-parameters.
/root/scripts/syslog/sql-log.sh:

#!/bin/bash
#
# apache-stats to mysql
while read MSG; do
mysql -uispconfig -pPWD dbispconfig -e "$MSG"
done
exit 0


Get your PWD from /usr/local/ispconfig/server/lib/config.inc.php.

Now we define what syslog-ng should do with the logs:

log {
source(src);
parser(p_apache-access);
filter(f_apache2);
destination (d_apache-logs);
destination (d_ispconfig_apache-stat);
};


As you can see we need a filter and a parser.

Filter:

filter f_apache2 {
program('apache2')
};


Parser:

parser p_apache-access {
csv-parser(columns(
".apache.domain",
".apache.client_ip",
".apache.ident_name",
".apache.user_name",
".apache.timestamp",
".apache.timestamp2",
".apache.request_url",
".apache.request_status",
".apache.content_length",
".apache.referer",
".apache.user_agent")
flags(escape-double-char,strip-whitespace)
delimiters(" ")
quote-pairs('""\[\]') );
};

Done. 😉

Download:

syslog-ng.conf

syslog-ng.conf

Parser oder XML-File

Leave a comment

Your email address will not be published. Required fields are marked *

One thought on “ISPConfig – Apache-Logfiles and syslog”