Das Webalizer-Dienstprogramm und das Google Analytics-Tool haben mir seit vielen Jahren geholfen, eine Vorstellung davon zu bekommen, was auf Websites geschieht. Jetzt verstehe ich, dass sie sehr wenig nützliche Informationen liefern. Der Zugriff auf Ihre Datei access.log ist sehr einfach und für die Implementierung relativ einfacher Tools wie SQLite, HTML, SQL und einer beliebigen Skriptprogrammiersprache geeignet.
Die Datenquelle für Webalizer ist die Datei server access.log. So sehen die Spalten und Zahlen aus, von denen nur die Gesamtmenge des Datenverkehrs klar ist:
Tools wie Google Analytics erfassen selbst Daten von der geladenen Seite. Sie zeigen uns einige Diagramme und Linien, auf deren Grundlage es oft schwierig ist, die richtigen Schlussfolgerungen zu ziehen. Vielleicht war mehr Aufwand nötig? Weiß nicht.
Was wollte ich in der Statistik der Besuche vor Ort sehen?
Benutzer- und Bot-Verkehr
Oft hat der Site-Verkehr ein Limit und Sie müssen sehen, wie viel nützlicher Verkehr verwendet wird. Zum Beispiel so:
SQL-BerichtsanforderungSELECT 1 as 'StackedArea: Traffic generated by Users and Bots', strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Day', SUM(CASE WHEN USG.AGENT_BOT!='na' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Bots, KB', SUM(CASE WHEN USG.AGENT_BOT='na' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Users, KB' FROM FCT_ACCESS_USER_AGENT_DD FCT, DIM_USER_AGENT USG WHERE FCT.DIM_USER_AGENT_ID=USG.DIM_USER_AGENT_ID AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day') GROUP BY strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) ORDER BY FCT.EVENT_DT
In der Grafik sehen Sie die konstante Aktivität der Bots. Es wäre interessant, die aktivsten Vertreter im Detail zu untersuchen.
Ärgerliche Bots
Wir klassifizieren Bots basierend auf Benutzeragenteninformationen. Zusätzliche Statistiken zum täglichen Datenverkehr, zur Anzahl der erfolgreichen und erfolglosen Anforderungen geben einen guten Überblick über die Aktivität von Bots.
SQL-Berichtsanforderung SELECT 1 AS 'Table: Annoying Bots', MAX(USG.AGENT_BOT) AS 'Bot', ROUND(SUM(FCT.BYTES)/1000 / 14.0, 1) AS 'KB per Day', ROUND(SUM(FCT.IP_CNT) / 14.0, 1) AS 'IPs per Day', ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Client Error', 'Server Error') THEN FCT.REQUEST_CNT / 14.0 ELSE 0 END), 1) AS 'Error Requests per Day', ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Successful', 'Redirection') THEN FCT.REQUEST_CNT / 14.0 ELSE 0 END), 1) AS 'Success Requests per Day', USG.USER_AGENT_NK AS 'Agent' FROM FCT_ACCESS_USER_AGENT_DD FCT, DIM_USER_AGENT USG, DIM_HTTP_STATUS STS WHERE FCT.DIM_USER_AGENT_ID = USG.DIM_USER_AGENT_ID AND FCT.DIM_HTTP_STATUS_ID = STS.DIM_HTTP_STATUS_ID AND USG.AGENT_BOT != 'na' AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day') GROUP BY USG.USER_AGENT_NK ORDER BY 3 DESC LIMIT 10
In diesem Fall führte die Analyse zu der Entscheidung, den Zugriff auf die Site durch Hinzufügen von robots.txt zur Datei einzuschränken
User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5Die ersten beiden Bots verschwanden vom Tisch, und MS-Roboter bewegten sich von den ersten Zeilen nach unten.
Tag und Uhrzeit der meisten Aktivität
Der Verkehr zeigt sich. Um sie im Detail zu untersuchen, ist es notwendig, den Zeitpunkt ihres Auftretens zu identifizieren, während nicht alle Stunden und Tage der Zeitmessung angezeigt werden müssen. So ist es einfacher, einzelne Abfragen in der Protokolldatei zu finden, wenn Sie eine detaillierte Analyse benötigen.
SQL-Berichtsanforderung SELECT 1 AS 'Line: Day and Hour of Hits from Users and Bots', strftime('%d.%m-%H', datetime(EVENT_DT, 'unixepoch')) AS 'Date Time', HIB AS 'Bots, Hits', HIU AS 'Users, Hits' FROM ( SELECT EVENT_DT, SUM(CASE WHEN AGENT_BOT!='na' THEN LINE_CNT ELSE 0 END) AS HIB, SUM(CASE WHEN AGENT_BOT='na' THEN LINE_CNT ELSE 0 END) AS HIU FROM FCT_ACCESS_REQUEST_REF_HH WHERE datetime(EVENT_DT, 'unixepoch') >= date('now', '-14 day') GROUP BY EVENT_DT ORDER BY SUM(LINE_CNT) DESC LIMIT 10 ) ORDER BY EVENT_DT
Wir beobachten die aktivsten Stunden 11, 14 und 20 des ersten Tages auf der Karte. Aber am nächsten Tag um 13 Uhr waren die Bots aktiv.
Wöchentliche durchschnittliche tägliche Benutzeraktivität
Mit Aktivität und Verkehr ein wenig herausgefunden. Die nächste Frage war die Aktivität der Benutzer selbst. Für solche Statistiken sind große Aggregationsperioden, beispielsweise eine Woche, wünschenswert.
SQL-Berichtsanforderung SELECT 1 as 'Line: Average Daily User Activity by Week', strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Week', ROUND(1.0*SUM(FCT.PAGE_CNT)/SUM(FCT.IP_CNT),1) AS 'Pages per IP per Day', ROUND(1.0*SUM(FCT.FILE_CNT)/SUM(FCT.IP_CNT),1) AS 'Files per IP per Day' FROM FCT_ACCESS_USER_AGENT_DD FCT, DIM_USER_AGENT USG, DIM_HTTP_STATUS HST WHERE FCT.DIM_USER_AGENT_ID=USG.DIM_USER_AGENT_ID AND FCT.DIM_HTTP_STATUS_ID = HST.DIM_HTTP_STATUS_ID AND USG.AGENT_BOT='na' AND HST.STATUS_GROUP IN ('Successful') AND datetime(FCT.EVENT_DT, 'unixepoch') > date('now', '-3 month') GROUP BY strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch')) ORDER BY FCT.EVENT_DT
Statistiken für die Woche zeigen, dass durchschnittlich ein Benutzer 1,6 Seiten pro Tag öffnet. Die Anzahl der angeforderten Dateien pro Benutzer hängt in diesem Fall vom Hinzufügen neuer Dateien zur Site ab.
Alle Anfragen und deren Status
Webalizer zeigte immer bestimmte Seitencodes an und wollte immer nur die Anzahl der erfolgreichen Anfragen und Fehler sehen.
SQL-Berichtsanforderung SELECT 1 as 'Line: All Requests by Status', strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Day', SUM(CASE WHEN STS.STATUS_GROUP='Successful' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Success', SUM(CASE WHEN STS.STATUS_GROUP='Redirection' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Redirect', SUM(CASE WHEN STS.STATUS_GROUP='Client Error' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Customer Error', SUM(CASE WHEN STS.STATUS_GROUP='Server Error' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Server Error' FROM FCT_ACCESS_USER_AGENT_DD FCT, DIM_HTTP_STATUS STS WHERE FCT.DIM_HTTP_STATUS_ID=STS.DIM_HTTP_STATUS_ID AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day') GROUP BY strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) ORDER BY FCT.EVENT_DT
Der Bericht zeigt Anforderungen an, keine Klicks (Treffer). Im Gegensatz zu LINE_CNT wird die REQUEST_CNT-Metrik als COUNT (DISTINCT STG.REQUEST_NK) betrachtet. Ziel ist es, effektive Ereignisse anzuzeigen, z. B. MS-Bots, die hunderte Male am Tag eine robots.txt-Datei abfragen. In diesem Fall werden solche Abfragen einmal gezählt. Auf diese Weise können Sie die Sprünge auf dem Diagramm glätten.
In der Grafik sehen Sie viele Fehler - dies sind nicht vorhandene Seiten. Das Ergebnis der Analyse war das Hinzufügen von Weiterleitungen von entfernten Seiten.
Fehlerhafte Anfragen
Für eine detaillierte Überprüfung von Anforderungen können Sie detaillierte Statistiken anzeigen.
SQL-Berichtsanforderung SELECT 1 AS 'Table: Top Error Requests', REQ.REQUEST_NK AS 'Request', 'Error' AS 'Request Status', ROUND(SUM(FCT.LINE_CNT) / 14.0, 1) AS 'Hits per Day', ROUND(SUM(FCT.IP_CNT) / 14.0, 1) AS 'IPs per Day', ROUND(SUM(FCT.BYTES)/1000 / 14.0, 1) AS 'KB per Day' FROM FCT_ACCESS_REQUEST_REF_HH FCT, DIM_REQUEST_V_ACT REQ WHERE FCT.DIM_REQUEST_ID = REQ.DIM_REQUEST_ID AND FCT.STATUS_GROUP IN ('Client Error', 'Server Error') AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day') GROUP BY REQ.REQUEST_NK ORDER BY 4 DESC LIMIT 20
Diese Liste enthält alle Dialer, z. B. eine Anforderung an /wp-login.php. Durch Anpassen der Regeln zum Umschreiben von Anforderungen durch den Server können Sie die Antwort des Servers auf solche Anforderungen anpassen und an die Startseite senden.
Einige einfache Berichte, die auf der Server-Protokolldatei basieren, geben also ein ziemlich vollständiges Bild davon, was auf der Site passiert.
Wie bekomme ich Informationen?
Die SQLite-Datenbank ist völlig ausreichend. Erstellen wir Tabellen: Hilfsmittel für die Protokollierung von ETL-Prozessen.
Stufentabelle, in der wir Protokolldateien mit PHP schreiben. Zwei aggregierte Tabellen. Erstellen Sie eine tägliche Tabelle mit Statistiken zu Benutzeragenten und Anforderungsstatus. Stündlich mit Statistiken zu Anfragen, Statusgruppen und Agenten. Vier Tabellen mit relevanten Messungen.
Das Ergebnis ist das folgende relationale Modell:
Skript zum Erstellen eines Objekts in einer SQLite-Datenbank:
DDL-Objekterstellung DROP TABLE IF EXISTS DIM_USER_AGENT; CREATE TABLE DIM_USER_AGENT ( DIM_USER_AGENT_ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, USER_AGENT_NK TEXT NOT NULL DEFAULT 'na', AGENT_OS TEXT NOT NULL DEFAULT 'na', AGENT_ENGINE TEXT NOT NULL DEFAULT 'na', AGENT_DEVICE TEXT NOT NULL DEFAULT 'na', AGENT_BOT TEXT NOT NULL DEFAULT 'na', UPDATE_DT INTEGER NOT NULL DEFAULT 0, UNIQUE (USER_AGENT_NK) ); INSERT INTO DIM_USER_AGENT (DIM_USER_AGENT_ID) VALUES (-1);
Bühne
Im Fall der Datei access.log müssen Sie alle Anforderungen lesen, analysieren und in die Datenbank schreiben. Dies kann entweder direkt mit der Skriptsprache oder mit SQLite erfolgen.
Protokolldateiformat:
Schlüsselpropaganda
Wenn sich die Rohdaten in der Datenbank befinden, müssen Sie Schlüssel aufzeichnen, die nicht in den Messtabellen enthalten sind. Dann ist es möglich, einen Verweis auf die Messungen zu erstellen. In der Tabelle DIM_REFERRER ist der Schlüssel beispielsweise eine Kombination aus drei Feldern.
Abfrage zur Weitergabe von SQL-Schlüsseln INSERT INTO DIM_REFERRER (HOST_NK, PATH_NK, QUERY_NK, UPDATE_DT) SELECT CLS.HOST_NK, CLS.PATH_NK, CLS.QUERY_NK, STRFTIME('%s','now') AS UPDATE_DT FROM ( SELECT DISTINCT REFERRER_HOST AS HOST_NK, REFERRER_PATH AS PATH_NK, CASE WHEN INSTR(REFERRER_QUERY,'&sid')>0 THEN SUBSTR(REFERRER_QUERY, 1, INSTR(REFERRER_QUERY,'&sid')-1) ELSE REFERRER_QUERY END AS QUERY_NK FROM STG_ACCESS_LOG ) CLS LEFT OUTER JOIN DIM_REFERRER TRG ON (CLS.HOST_NK = TRG.HOST_NK AND CLS.PATH_NK = TRG.PATH_NK AND CLS.QUERY_NK = TRG.QUERY_NK) WHERE TRG.DIM_REFERRER_ID IS NULL
Die Weitergabe an die Benutzeragententabelle kann eine Bot-Logik enthalten, z. B. einen SQL-Auszug:
CASE WHEN INSTR(LOWER(CLS.BROWSER),'yandex.com')>0 THEN 'yandex' WHEN INSTR(LOWER(CLS.BROWSER),'googlebot')>0 THEN 'google' WHEN INSTR(LOWER(CLS.BROWSER),'bingbot')>0 THEN 'microsoft' WHEN INSTR(LOWER(CLS.BROWSER),'ahrefsbot')>0 THEN 'ahrefs' WHEN INSTR(LOWER(CLS.BROWSER),'mj12bot')>0 THEN 'majestic-12' WHEN INSTR(LOWER(CLS.BROWSER),'compatible')>0 OR INSTR(LOWER(CLS.BROWSER),'http')>0 OR INSTR(LOWER(CLS.BROWSER),'libwww')>0 OR INSTR(LOWER(CLS.BROWSER),'spider')>0 OR INSTR(LOWER(CLS.BROWSER),'java')>0 OR INSTR(LOWER(CLS.BROWSER),'python')>0 OR INSTR(LOWER(CLS.BROWSER),'robot')>0 OR INSTR(LOWER(CLS.BROWSER),'curl')>0 OR INSTR(LOWER(CLS.BROWSER),'wget')>0 THEN 'other' ELSE 'na' END AS AGENT_BOT
Einheitentabellen
Zuletzt werden wir aggregierte Tabellen laden, zum Beispiel kann eine tägliche Tabelle wie folgt geladen werden:
SQL-Aggregatladeanforderung INSERT INTO FCT_ACCESS_USER_AGENT_DD (EVENT_DT, DIM_USER_AGENT_ID, DIM_HTTP_STATUS_ID, PAGE_CNT, FILE_CNT, REQUEST_CNT, LINE_CNT, IP_CNT, BYTES) WITH STG AS ( SELECT STRFTIME( '%s', SUBSTR(TIME_NK,9,4) || '-' || CASE SUBSTR(TIME_NK,5,3) WHEN 'Jan' THEN '01' WHEN 'Feb' THEN '02' WHEN 'Mar' THEN '03' WHEN 'Apr' THEN '04' WHEN 'May' THEN '05' WHEN 'Jun' THEN '06' WHEN 'Jul' THEN '07' WHEN 'Aug' THEN '08' WHEN 'Sep' THEN '09' WHEN 'Oct' THEN '10' WHEN 'Nov' THEN '11' ELSE '12' END || '-' || SUBSTR(TIME_NK,2,2) || ' 00:00:00' ) AS EVENT_DT, BROWSER AS USER_AGENT_NK, REQUEST_NK, IP_NR, STATUS, LINE_NK, BYTES FROM STG_ACCESS_LOG ) SELECT CAST(STG.EVENT_DT AS INTEGER) AS EVENT_DT, USG.DIM_USER_AGENT_ID, HST.DIM_HTTP_STATUS_ID, COUNT(DISTINCT (CASE WHEN INSTR(STG.REQUEST_NK,'.')=0 THEN STG.REQUEST_NK END) ) AS PAGE_CNT, COUNT(DISTINCT (CASE WHEN INSTR(STG.REQUEST_NK,'.')>0 THEN STG.REQUEST_NK END) ) AS FILE_CNT, COUNT(DISTINCT STG.REQUEST_NK) AS REQUEST_CNT, COUNT(DISTINCT STG.LINE_NK) AS LINE_CNT, COUNT(DISTINCT STG.IP_NR) AS IP_CNT, SUM(BYTES) AS BYTES FROM STG, DIM_HTTP_STATUS HST, DIM_USER_AGENT USG WHERE STG.STATUS = HST.STATUS_NK AND STG.USER_AGENT_NK = USG.USER_AGENT_NK AND CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from AND CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('now', 'start of day')) GROUP BY STG.EVENT_DT, HST.DIM_HTTP_STATUS_ID, USG.DIM_USER_AGENT_ID
Mit der SQLite-Datenbank können Sie komplexe Abfragen schreiben. WITH enthält die Vorbereitung von Daten und Schlüsseln. Die Hauptabfrage sammelt alle Verweise auf Dimensionen.
Die Bedingung erlaubt nicht das erneute Laden der Story: CAST (STG.EVENT_DT AS INTEGER)> $ param_epoch_from, wobei der Parameter das Ergebnis der Anforderung ist
'COALESCE AUSWÄHLEN (MAX (EVENT_DT), \' 3600 \ ') AS LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD'
Die Bedingung wird nur den ganzen Tag geladen: CAST (STG.EVENT_DT AS INTEGER) <strftime ('% s', Datum ('jetzt', 'Tagesbeginn'))
Das Zählen von Seiten oder Dateien erfolgt primitiv, indem nach einem Punkt gesucht wird.
Berichte
In komplexen Visualisierungssystemen ist es möglich, ein Metamodell basierend auf Datenbankobjekten zu erstellen, Filter und Aggregationsregeln dynamisch zu verwalten. Letztendlich generieren alle anständigen Tools eine SQL-Abfrage.
In diesem Beispiel erstellen wir vorgefertigte SQL-Abfragen und speichern sie als Ansicht in der Datenbank - dies sind die Berichte.
Visualisierung
Bluff: Schöne Grafiken in JavaScript wurden als Visualisierungswerkzeug verwendet.
Dazu musste PHP verwendet werden, um alle Berichte zu durchsuchen und eine HTML-Datei mit Tabellen zu generieren.
$sqls = array( 'SELECT * FROM RPT_ACCESS_USER_VS_BOT', 'SELECT * FROM RPT_ACCESS_ANNOYING_BOT', 'SELECT * FROM RPT_ACCESS_TOP_HOUR_HIT', 'SELECT * FROM RPT_ACCESS_USER_ACTIVE', 'SELECT * FROM RPT_ACCESS_REQUEST_STATUS', 'SELECT * FROM RPT_ACCESS_TOP_REQUEST_PAGE', 'SELECT * FROM RPT_ACCESS_TOP_REQUEST_REFERRER', 'SELECT * FROM RPT_ACCESS_NEW_REQUEST', 'SELECT * FROM RPT_ACCESS_TOP_REQUEST_SUCCESS', 'SELECT * FROM RPT_ACCESS_TOP_REQUEST_ERROR' );
Das Tool visualisiert einfach die Ergebnistabellen.
Fazit
Am Beispiel der Webanalyse beschreibt der Artikel die Mechanismen, die zum Erstellen von Data Warehouses erforderlich sind. Wie aus den Ergebnissen hervorgeht, reichen die einfachsten Werkzeuge für eine eingehende Analyse und Datenvisualisierung aus.
In Zukunft werden wir am Beispiel dieses Speichers versuchen, Strukturen wie sich
langsam ändernde Messungen , Stammdaten, Aggregationsebenen und Datenintegration aus verschiedenen Quellen zu implementieren.
Außerdem werden wir uns das einfachste
ETL-Prozessmanagement-Tool anhand einer einzelnen Tabelle genauer ansehen.
Kehren wir zum Thema der Messung der Datenqualität und der Automatisierung dieses Prozesses zurück.
Wir werden die Probleme der technischen Umgebung und der Wartung von Data Warehouses untersuchen, für die wir einen Speicherserver mit minimalen Ressourcen implementieren, beispielsweise basierend auf dem Raspberry Pi.