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|%{User-Agent}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.
CREATE 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:
create role wwwimport login password 'changeme!';
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.<counter>.gz. Allerdings wird dabei auch am Anfang der neuen und am Ende der alten Datei eine Zeile eingesetzt, dass ein rotate stattgefunden hat.
das Script als cron
Um die Daten in die Datenbank zu bekommen habe ich den Befehl COPY 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
sed -e "s/[][]//g"
Das Problem mit der zusaetzlichen Zeile loesen wir mit einem grep.
grep -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.
#!/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=$(ls $LOG_PATH/$LOG_FILE*gz)
workfile="${WORKDIR}/dump.sql"
echo "COPY ${TABLE} FROM STDIN WITH DELIMITER '|';" >> $workfile
for file in $files; do
gzcat $file | sed -e "s/[][]//g" | grep -v "logfile turned over" >> $workfile
rm $file
done
PGPASSWORD="$DBPASS" psql -U $DBUSER -f $workfile $DATABASE
# delete the workdir
rm $workfile
rmdir $WORKDIR
exit 0Das 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.
Die zwei Wollknäule von 
Ich hatte mich extra in die hinterste Ecke verkrochen, aber trotzdem hab ich das Gefühl, dass ich die meist fotographierte Person auf dem Wollefest war. So viele kamen dann doch vorbei, fanden mein Spinnrad total süß und wollten wissen, was das für eins ist. Dabei war ich bei weitem nicht die einzige Spinnerin auf dem Fest.
