Accesslogs in die Datenbank

Da ich mal unsere access-logs der letzten paar Tage auswerten wollte, brauchte ich eine Moeglichkeit die Daten in eine Datenbank zu packen. Dummerweise sind die Log-Dateien der Webserver nicht Datenbankgerecht und keiner hat ein Interface um Datensaetze gleich in eine Datenbank zu schreiben.

Deswegen hab ich mir eine Methode ueberlegt, mit der ich die Daten in die Datenbank bekomme, ohne dass es viel Aufwand bedarf.

Voraussetzungen

Da das hier eine Art kleines Tutorial wird hier eine Liste der Programme die ich benutzt habe:

* lighttpd
* PostgreSQL
* simples Shell (sh FreeBSD Version)
* cron
* logrotate (newsyslog in FreeBSD)

Logformat setzen

Als erstes brauche ich ein Logformat, welches sich auch gut verarbeiten laesst. Im Default sind alle Felder durch Whitespaces getrennt und es gibt Felder, welche nicht immer gefuellt sind, was sich nur sehr schwer parsen laesst.

Deswegen hatte ich mir ueberlegt, das Logformat in Richtung CSV-Datei auszurichten. Im lighttpd muss dazu das Module mod_accesslog eingebunden werden.

Danach steht die Option accesslog.format:http://redmine.lighttpd.net/wiki/1/Docs:ModAccesslog zur Verfuegung. Diese habe ich bei uns auf folgenden Wert gesetzt

# log format for csv
# h host ip # t timestamp of the end of the request
# m request method # s status code
# b bytes sent # U request URL
# f real filename # q query string
# T time used in secounds accesslog.format = “h|t|m|s|b|U|f|q|T|i|{Referer}i”

Das Zeichen | ist dabei mein CSV-Trennzeichen, damit ich die Werte auseinander halten kann.

Datenbankschema

Das Datenbankschema entspricht dem Schema der CSV-Datei.

source:sqlCREATE TABLE logs (
ip text,
datum text,
method text,
code integer,
bytes integer,
req_file text,
full_path text,
query_string text,
time_taken integer,
user_agent text,
referer text
);

optionaler User

Wenn man auf Nummer sicher gehen will, kann man noch einen zusaetzlichen User anlegen, welcher nur Insertrechte auf diese Tabelle hat. Damit kann man ausschliessen, dass irgendwas doofes passiert, wie zum Beispiel ein Drop Table oder aehnliches.

Das geht dann mit folgendem Befehl:

source:sqlcreate role wwwimport login password ’changeme![](‘;

h1. Logrotate

Lighttpd schreibt per default nach @/var/log/lighttpd/access.log@ bis kein Speicher mehr da ist. Wenn man allerdings die Datei allerdings verschiebt und eine neue hinstellt ohne den Server neu zu starten, so loggt dieser in die verschobene Datei. Wird sie geloescht, dann loggt der Server garnicht mehr. Da das fuer die Logverarbeitung ungeschickt ist, musste da irgendwie ein logrotate rein. Unter FreeBSD geht das mit @/etc/newsyslog.conf@ wo ich den Eintrag

/var/log/lighttpd/access.log www:www 664 7 * @T00 Z /var/run/lighttpd.pid

gesetzt habe. Das erstellt Archive im Format @access.log..gz@. Allerdings wird dabei auch am Anfang der neuen und am Ende der alten Datei eine Zeile eingesetzt, dass ein rotate stattgefunden hat.

h1. das Script als cron

Um die Daten in die Datenbank zu bekommen habe ich den Befehl “COPY”:http://www.postgresql.org/docs/9.0/static/sql-copy.html genommen, da er schneller als 9000 einzelne Selects arbeitet. Zusaetzlich hat der Timestamp den kleinen Makel, dass er von [ und ] umgeben ist, wodurch sich das Feld nicht einfach als Timestamp speichern laesst. Die Timestamps lassen sich allerdings recht einfach mit folgendem Befehl bereinigen

source:shsed -e “s/[][]//g”

Das Problem mit der zusaetzlichen Zeile loesen wir mit einem @grep@.

source:shgrep -v “logfile turned over”

geloest. Das gibt nur noch die Zeilen aus, welche nicht den String enthalten.

Insgesamt kommt dabei bei mir folgendes kleines Script raus.

source:ksh#)/usr/bin/env ksh

LOG_PATH=“/var/log/lighttpd”
LOG_FILE=“access.log”
TABLE=“logs”
DATABASE=“test”
DBUSER=“wwwimport”
DBPASS=“changeme![](”

WORKDIR=“/tmp/logimport”

create a workdir

if [ -d $WORKDIR ]; then echo ‘Job is running)’
exit 1
else
mkdir m 700 \$WORKDIR
fi
files=\$
workfile=“\${WORKDIR}/dump.sql”
echo “COPY \${TABLE} FROM STDIN WITH DELIMITER ‘|’;” >> \$workfile
for file in \$files; do
gzcat \$file | sede”s/[][]//g” | grep v “logfile turned over” >> \$workfile
rm \$file
done
PGPASSWORD=“\$DBPASS” psqlU \$DBUSER -f \$workfile \$DATABASE

1. delete the workdir
   rm \$workfile
   rmdir \$WORKDIR
   exit 0

Das habe ich dann noch irgendwo gespeichert und im cron mit

0 23 * * * sh logimport.sh

eingetragen. Jetzt importiert es munter meine Logs und ich kann anfangen R zu lernen.