Webserver-Logs mit Log Parser auswerten

Microsofts kostenloses Werkzeug Log Parser eignet sich hervorragend, um Protokolldateien (nahezu) jeder Art auszuwerten. Vor einiger Zeit haben wir hier eine Lösung vorgestellt, die Log Parser einsetzt, um Berichte über Windows-Eventlogs für Server zu erzeugen. Ursprünglich allerdings handelte es sich um ein Tool zur Auswertung von Webserver-Zugriffslogs – daher nutzen wir es seit einiger Zeit auch, um Statistiken über die Nutzung unseres Blogs zu erzeugen. Das ist sicher auch für andere interessant, daher hier ein paar Beispiele.

Bei uns läuft WordPress auf einem LAMP-System, also erhalten wir die Logs vom Apache-Server, die im NCSA-Logformat vorliegen. So kommen sie bei uns allerdings nicht an, sondern wir erhalten sie komprimiert im gz-Format. Zum Entpacken nutzen wir daher ein Batch-Kommando dieser Art (hier mit dem freien Dekomprimierer IZArc2Go, aber man kann auch 7zip oder sowas nehmen):

FOR %%I IN (C:\Statistik\Logs\*.gz) DO (
	echo Dekomprimiere: %%~nI
	C:\Tools\IZArc2Go.exe -eh C:\Statistik\Logs-Raw\ %%~fI
)

Diese Schleife nimmt sich alle gz-Dateien aus dem Ausgangsordner und entpackt sie in den Zielordner. Dort angekommen, sieht eine Logdatei in etwa so aus (hier natürlich anonymisiert):

123.45.67.89 - - [26/Dec/2010:00:00:29 +0100] "GET /2004/10/26/wieviel-ram-ist-sinnvoll-bei-terminal-servern/ HTTP/1.1" 200 10206 "-" "Mozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)"
23.45.6.78 - - [26/Dec/2010:00:00:34 +0100] "GET /2007/06/08/welcher-name-ist-der-beste-fuer-eine-ad-domaene/ HTTP/1.1" 200 10194 "http://www.administrator.de/index.php?content=100062" "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729)"
123.98.76.54 - - [26/Dec/2010:00:00:35 +0100] "GET /wp-content/themes/faqomatic/style.css HTTP/1.1" 200 9943 "http://www.faq-o-matic.net/2007/06/08/welcher-name-ist-der-beste-fuer-eine-ad-domaene/" "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729)"

Artikel-Aufrufe filtern

Aufgrund des URL-Schemas unserer Seite haben die Aufrufe von Artikeln immer ein ganz spezielles Format: Zuerst kommt das Datum, mit Schrägstrichen getrennt, dann der Titel des Artikels und am Ende des URLs steht wieder ein Schrägstrich. Da wir unter anderem wissen wollen, welche Artikel wie oft aufgerufen werden, lassen wir Log Parser die folgende Abfrage ausführen:

Das Feld DateTime findet Log Parser selbst. Um aus dem Request-String den URI herauszufiltern, nutzen wir die Funktion EXTRACT_TOKEN, die den Abschnitt bis zum ersten Leerzeichen extrahiert. Wir wollen aber nur Artikel-Abrufe, also filtern wir in der WHERE-Klausel auf den HTTP-Statuscode 200 und lassen uns nur solche URIs heben, bei denen am Ende ein Leerzeichen steht und die mit dem Muster Schrägstrich – vier Zeichen – Schrägstrich – zwei Zeichen – Schrägstrich – zwei Zeichen – Schrägstrich beginnen.

Zur INTO- und zur FROM-Klausel folgt weiter unten mehr.

Wo kommen die Leser her?

Jeder Webserver-Aufruf eines Browsers (naja, fast jeder) gibt an, welche Seite der Anwender vorher besucht hat. Das ist gar nicht uninteressant, denn so entsteht ein Bild, wie unser Blog in die Community eingebunden ist. Daher werten wir auch den so genannten “Referer” aus (natürlich anonym).

Das Feld Referer ist Log Parser wieder bekannt, aber daraus benötigen wir nur den Host, nicht den ganzen URI, also kommt wieder EXTRACT_TOKEN zum Einsatz. Dabei interessieren uns interne Verweise nicht, also filtern wir unseren eigenen Servernamen wieder aus.

Externe Suchausdrücke

Die meisten Suchmaschinen lassen die Suchausdrücke, die Anwender verwendet haben, in ihrem Referer-URL erkennen. Das machen wir uns zunutze und werten die Ausdrücke aus, mit denen Leser zu uns kommen. Das Prinzip ist einfach: Wir identifizieren in den Aufrufen die Suchmaschinen über die Referer und schneiden aus der Angabe die Suchstrings heraus. Das folgende Beispiel findet die Suchbegriffe für Google und Bing. Praktischerweise verwenden beide ein sehr ähnliches Parameter-Format.

Die gesuchten Ausdrücke finden sich nämlich bei Google und Bing im URI nach der Angabe ?q=. Die obige SELECT-Klausel ist zwar ganz schön unübersichtlich, weil sie gleich vier Log-Parser-Funktionen ineinander verschachtelt, aber vom Prinzip her einfach: Erst schneidet sie mit EXTRACT_TOKEN den Teil nach dem ?q= aus, dann wirft sie alles nach dem folgenden & weg (da kommen nämlich Angaben, die nicht mehr zu den Suchbegriffen gehören). Danach konvertiert sie die HTTP-Codes für Umlaute mit URLUNESCAPE in lesbare Zeichen und ersetzt am Ende mit REPLACE_STR alle + durch Leerzeichen.

Fehler finden

Schließlich interessieren uns auch die Fehler, die Leser auf unserer Seite erzeugen. Die meisten davon sind nicht richtig spannend, aber einige lassen Rückschlüsse auf Angriffe zu. So ist es manchmal beeindruckend zu sehen, wie Clients (wahrscheinlich durch Botnetze ferngesteuert) bekannte angreifbare Dateien auf unserem Server suchen oder wild irgendwelche SQL-Injection-Angriffe ausprobieren. Bislang glücklicherweise erfolglos.

Daneben dienen uns die Fehler aber auch zum Optimieren unserer Seite. So haben wir ja bereits mehrfach die Technik gewechselt, und dadurch haben sich viele Links geändert. Durch die Fehler-Auswertung haben wir Hinweise, für welche veralteten Links sich Umleitungs-Maßnahmen besonders lohnen würden.

Automatisch auswerten

Wie die meisten Webserver erzeugt auch unser Apache für jeden Tag eine Logdatei. Damit Log Parser all diese Dateien automatisch auswertet, lassen wir alle 14 Tage einen Batch über die angesammelten Dateien laufen, der für jede Datei einige Abfragen ausführt. Wir schreiben die Ergebnisse dann in eine kleine SQL-Datenbank (das macht Log Parser selbst) und löschen die Ausgangsdaten datenschutzkonform.

So sieht unser Batch prinzipiell aus:

@echo off
REM --- Ordner
SET LogsRawFolder="C:\Statistik\logs-raw"
SET LogParserSQLFolder="C:\Statistik"
REM --- SQL-Klausel für Log Parser
SET LogParserOutputClause=-o:SQL -server:.\SQLEXPRESS -database:fomStats -createTable:ON
REM --- Pfade zu den Programmen
SET LogParserCmd="C:\Program Files\Log Parser 2.2\LogParser.exe"

REM Logdateien auswerten
FOR %%I IN (%LogsRawFolder%\*.*) DO (
	ECHO Werte aus: %%~nI
	REM Artikelaufrufe
	%LogParserCmd% file:%LogParserSQLFolder%\acclog-Artikel.sql?InputLog="%%~fI"+OutputFile="Artikel" -i:NCSA %LogParserOutputClause% -q
	REM Referer-Domains
	%LogParserCmd% file:%LogParserSQLFolder%\acclog-RefererDomains.sql?InputLog="%%~fI"+OutputFile="RefererDomains" -i:NCSA %LogParserOutputClause% -q
	REM Externe Suche
	%LogParserCmd% file:%LogParserSQLFolder%\acclog-ExterneSuche.sql?InputLog="%%~fI"+OutputFile="ExterneSuche" -i:NCSA %LogParserOutputClause% -q
	REM Angriffe und Fehler
	%LogParserCmd% file:%LogParserSQLFolder%\acclog-Errors.sql?InputLog="%%~fI"+OutputFile="Errors" -i:NCSA %LogParserOutputClause% -q
)

ECHO Log-Verarbeitung beendet.

Der eigentliche Witz liegt in den Log-Parser-Aufrufen. Jede der oben gezeigten SQL-Abfragen liegt als Datei vor. Diese übergeben wir an Log Parser (mit dem Parameter -file). Da in jeder der SQL-Dateien Platzhalter für die FROM– und INTO-Klauseln stehen (schließlich ändern sich ja die Dateinamen der Logdateien bei jedem Aufruf), übergeben wir in jedem Durchlauf der Batch-Schleife den Namen der aktuellen Logdatei sowie den Namen der SQL-Tabelle, in der die erhaltenen Daten landen sollen. Dafür zuständig ist die Angabe ?InputLog="%%~fI"+OutputFile="Artikel" in dem Aufruf: Das Fragezeichen leitet die Parameter-Übergabe ein, die dem Parameter InputLog den Namen der gerade bearbeiteten Datei (aus der FOR-Schleife im Batch) sowie dem Parameter OutputFile den Namen der SQL-Tabelle Artikel übergibt.

Hinter dieser Angabe folgen dann noch die Identifikation des Eingabe-Logformats (-i:NCSA), damit Log Parser auch weiß, was für Daten er denn da bekommt, sowie die nötigen Angaben zur SQL-Datenbankverbindung, die wir der Einfachheit halber am Anfang der Batchdatei in der Variablen %LogParserOutputClause% hinterlegt haben.

Log Parser kann’s auch einfacher

Die hier gezeigten Beispiele sind schon starker Tobak und wenden sich eher an Fortgeschrittene. Es ist aber wohl sichtbar, wie flexibel Log Parser ist. Natürlich kann er auch einige einfachere Fragen beantworten – aber auch noch viel komplexere.