Webalizer und Google Analytics helfen mir seit vielen Jahren dabei, Einblicke in das Geschehen auf Websites zu gewinnen. Jetzt verstehe ich, dass sie nur sehr wenige nützliche Informationen liefern. Wenn Sie Zugriff auf Ihre access.log-Datei haben, ist es sehr einfach, die Statistiken zu verstehen und ganz einfache Tools wie SQLite, HTML, die SQL-Sprache und jede beliebige Skript-Programmiersprache zu implementieren.
Die Datenquelle für Webalizer ist die access.log-Datei des Servers. So sehen seine Balken und Zahlen aus, aus denen nur das Gesamtverkehrsaufkommen ersichtlich ist:
Tools wie Google Analytics sammeln selbst Daten von der geladenen Seite. Sie zeigen uns ein paar Diagramme und Linien, anhand derer es oft schwierig ist, richtige Schlussfolgerungen zu ziehen. Vielleicht hätte man sich mehr Mühe geben sollen? Weiß nicht.
Was wollte ich also in den Website-Besucherstatistiken sehen?
Benutzer- und Bot-Verkehr
Oft ist der Site-Traffic begrenzt und es ist notwendig zu sehen, wie viel nützlicher Traffic verwendet wird. Zum Beispiel so:
SQL-Berichtsabfrage
SELECT
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!='n.a.' THEN FCT.BYTES ELSE 0 END)/1000 AS 'Bots, KB',
SUM(CASE WHEN USG.AGENT_BOT='n.a.' 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_DTDie Grafik zeigt die konstante Aktivität von Bots. Es wäre interessant, die aktivsten Vertreter im Detail zu studieren.
Nervige Bots
Wir klassifizieren Bots basierend auf User-Agent-Informationen. Zusätzliche Statistiken zum täglichen Datenverkehr sowie zur Anzahl erfolgreicher und erfolgloser Anfragen geben einen guten Überblick über die Bot-Aktivität.
SQL-Berichtsabfrage
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 != 'n.a.'
AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day')
GROUP BY USG.USER_AGENT_NK
ORDER BY 3 DESC
LIMIT 10In diesem Fall war das Ergebnis der Analyse die Entscheidung, den Zugriff auf die Website durch Hinzufügen zur robots.txt-Datei einzuschränken
User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Die ersten beiden Bots verschwanden vom Tisch und die MS-Roboter rückten von den ersten Reihen nach unten.
Tag und Uhrzeit der größten Aktivität
Im Verkehr sind Aufschwünge erkennbar. Um sie im Detail zu untersuchen, ist es notwendig, den Zeitpunkt ihres Auftretens hervorzuheben, und es ist nicht notwendig, alle Stunden und Tage der Zeitmessung anzuzeigen. Dies erleichtert das Auffinden einzelner Anfragen in der Protokolldatei, wenn eine detaillierte Analyse erforderlich ist.
SQL-Berichtsabfrage
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!='n.a.' THEN LINE_CNT ELSE 0 END) AS HIB,
SUM(CASE WHEN AGENT_BOT='n.a.' 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_DTWir beobachten die aktivsten Stunden 11, 14 und 20 des ersten Tages auf dem Diagramm. Doch am nächsten Tag um 13:XNUMX Uhr waren die Bots aktiv.
Durchschnittliche tägliche Benutzeraktivität pro Woche
Mit Aktivität und Verkehr haben wir die Sache ein wenig geklärt. Die nächste Frage war die Aktivität der Benutzer selbst. Für solche Statistiken sind lange Aggregationszeiträume, beispielsweise eine Woche, wünschenswert.
SQL-Berichtsabfrage
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='n.a.' /* users only */
AND HST.STATUS_GROUP IN ('Successful') /* good pages */
AND datetime(FCT.EVENT_DT, 'unixepoch') > date('now', '-3 month')
GROUP BY strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDER BY FCT.EVENT_DTWöchentliche Statistiken zeigen, dass ein Benutzer durchschnittlich 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 spezifische Seitencodes an und ich wollte immer nur die Anzahl erfolgreicher Anfragen und Fehler sehen.
SQL-Berichtsabfrage
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_DTDer Bericht zeigt Anfragen und keine Klicks (Treffer) an. Im Gegensatz zu LINE_CNT wird die Metrik REQUEST_CNT als COUNT(DISTINCT STG.REQUEST_NK) berechnet. Das Ziel besteht darin, effektive Ereignisse anzuzeigen. Beispielsweise fragen MS-Bots die robots.txt-Datei hunderte Male am Tag ab. In diesem Fall werden solche Abfragen einmal gezählt. Dadurch können Sie Sprünge im Diagramm glätten.
Aus der Grafik können Sie viele Fehler erkennen – es handelt sich dabei um nicht vorhandene Seiten. Das Ergebnis der Analyse war das Hinzufügen von Weiterleitungen von entfernten Seiten.
Schlechte Anfragen
Um Anfragen im Detail zu untersuchen, können Sie sich detaillierte Statistiken anzeigen lassen.
SQL-Berichtsabfrage
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 20In dieser Liste sind auch alle Aufrufe enthalten, beispielsweise eine Anfrage an /wp-login.php. Durch Anpassen der Regeln zum Umschreiben von Anfragen durch den Server können Sie die Reaktion des Servers auf solche Anfragen anpassen und diese an die Startseite senden.
Ein paar einfache Berichte, die auf der Serverprotokolldatei basieren, geben also ein ziemlich vollständiges Bild davon, was auf der Website passiert.
Wie bekomme ich Informationen?
Eine SQLite-Datenbank ist ausreichend. Lassen Sie uns Tabellen erstellen: Hilfsmittel zur Protokollierung von ETL-Prozessen.
Tabellenphase, in der wir Protokolldateien mit PHP schreiben. Zwei Aggregattabellen. Lassen Sie uns eine tägliche Tabelle mit Statistiken zu Benutzeragenten und Anforderungsstatus erstellen. Stündlich mit Statistiken zu Anfragen, Statusgruppen und Agenten. Vier Tabellen mit relevanten Messungen.
Das Ergebnis ist das folgende relationale Modell:
Datenmodell
Skript zum Erstellen eines Objekts in einer SQLite-Datenbank:
Erstellung von DDL-Objekten
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 'n.a.',
AGENT_OS TEXT NOT NULL DEFAULT 'n.a.',
AGENT_ENGINE TEXT NOT NULL DEFAULT 'n.a.',
AGENT_DEVICE TEXT NOT NULL DEFAULT 'n.a.',
AGENT_BOT TEXT NOT NULL DEFAULT 'n.a.',
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 ist es notwendig, alle Anfragen in die Datenbank zu lesen, zu analysieren und zu schreiben. Dies kann entweder direkt über eine Skriptsprache oder über SQLite-Tools erfolgen.
Protokolldateiformat:
//67.221.59.195 - - [28/Dec/2012:01:47:47 +0100] "GET /files/default.css HTTP/1.1" 200 1512 "https://project.edu/" "Mozilla/4.0"
//host ident auth time method request_nk protocol status bytes ref browser
$log_pattern = '/^([^ ]+) ([^ ]+) ([^ ]+) ([[^]]+]) "(.*) (.*) (.*)" ([0-9-]+) ([0-9-]+) "(.*)" "(.*)"$/';
Schlüsselweitergabe
Wenn sich die Rohdaten in der Datenbank befinden, müssen Sie Schlüssel, die nicht vorhanden sind, in die Messtabellen schreiben. Dann ist es möglich, einen Bezug zu den Messungen herzustellen. In der Tabelle DIM_REFERRER ist der Schlüssel beispielsweise eine Kombination aus drei Feldern.
Abfrage zur SQL-Schlüsselweitergabe
/* Propagate the referrer from access log */
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) /* отрезаем sid - специфика цмс */
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 NULLDie Weitergabe an die Benutzeragententabelle kann Bot-Logik enthalten, zum Beispiel das SQL-Snippet:
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 'n.a.' END AS AGENT_BOTAggregierte Tabellen
Zuletzt laden wir die Aggregattabellen; die Tagestabelle kann beispielsweise wie folgt geladen werden:
SQL-Abfrage zum Laden von Aggregaten
/* Load fact from access log */
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 /* load epoch date */
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_IDMit der SQLite-Datenbank können Sie komplexe Abfragen schreiben. MIT beinhaltet die Aufbereitung von Daten und Schlüsseln. Die Hauptabfrage sammelt alle Verweise auf Dimensionen.
Die Bedingung erlaubt kein erneutes Laden des Verlaufs: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, wobei der Parameter das Ergebnis der Anfrage ist
'SELECT COALESCE(MAX(EVENT_DT), '3600') AS LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD'
Die Bedingung lädt nur den ganzen Tag: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('now', 'start of day'))
Das Zählen von Seiten oder Dateien erfolgt auf einfache Weise durch die Suche nach einem Punkt.
Berichte
In komplexen Visualisierungssystemen ist es möglich, ein Metamodell auf Basis von Datenbankobjekten zu erstellen, Filter und Aggregationsregeln dynamisch zu verwalten. Letztendlich generieren alle guten Tools eine SQL-Abfrage.
In diesem Beispiel erstellen wir vorgefertigte SQL-Abfragen und speichern diese als Ansichten in der Datenbank – das sind Berichte.
Visualisierung
Bluff: Als Visualisierungstool wurden wunderschöne Grafiken in JavaScript verwendet
Dazu war es notwendig, alle Berichte mit PHP durchzugehen 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 Ergebnistabellen.
Abschluss
Der Artikel beschreibt am Beispiel der Webanalyse die notwendigen Mechanismen zum Aufbau von Data Warehouses. Wie aus den Ergebnissen hervorgeht, genügen einfachste Werkzeuge für eine tiefgreifende Analyse und Visualisierung von Daten.
In Zukunft werden wir am Beispiel dieses Repositorys versuchen, Strukturen wie sich langsam ändernde Dimensionen, Metadaten, Aggregationsebenen und die Integration von Daten aus verschiedenen Quellen zu implementieren.
Schauen wir uns außerdem das einfachste Tool zur Verwaltung von ETL-Prozessen basierend auf einer einzelnen Tabelle genauer an.
Kehren wir zum Thema der Messung der Datenqualität und der Automatisierung dieses Prozesses zurück.
Wir werden die Probleme der technischen Umgebung und Wartung von Datenspeichern untersuchen, für die wir einen Speicherserver mit minimalen Ressourcen implementieren, beispielsweise auf Basis eines Raspberry Pi.
Source: habr.com