Narzędzie Webalizer oraz Google Analytics przez wiele lat pomagały mi zrozumieć, co dzieje się na stronach internetowych. Teraz rozumiem, że dostarczają one bardzo mało przydatnych informacji. Mając dostęp do swojego pliku access.log, analiza statystyk staje się bardzo prosta i wystarczą podstawowe narzędzia, takie jak sqlite, html, język SQL i jakikolwiek język skryptowy programowania.
Źródłem danych dla Webalizer jest plik access.log. serwera. Tak wyglądają jego kolumny i liczby, z których można zrozumieć tylko ogólny wolumen ruchu:
Takie narzędzia jak Google Analytics gromadzą dane z załadowanej strony samodzielnie. Wyświetlają nam kilka wykresów i linii, na podstawie których często trudno jest wyciągnąć prawidłowe wnioski. Może powinienem był włożyć w to więcej wysiłku? Nie wiem.
A więc, co chciałem zobaczyć w statystykach odwiedzin strony?
Ruch użytkowników i botów.
Często ruch na stronach internetowych jest ograniczony i trzeba wiedzieć, ile użytecznego ruchu jest wykorzystywane. Na przykład, tak:
Zapytanie SQL raportu
SELECT
1 as 'StackedArea: Ruch generowany przez użytkowników i boty',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Dzień',
SUM(CASE WHEN USG.AGENT_BOT != 'n.a.' THEN FCT.BYTES ELSE 0 END) / 1000 AS 'Boty, KB',
SUM(CASE WHEN USG.AGENT_BOT = 'n.a.' THEN FCT.BYTES ELSE 0 END) / 1000 AS 'Użytkownicy, 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_DTZ wykresu widać stałą aktywność botów. Interesujące byłoby dokładnie przebadać najbardziej aktywnych przedstawicieli.
Uciążliwe boty.
Klasyfikujemy boty na podstawie informacji o agencie użytkownika. Dodatkowe statystyki dotyczące codziennego ruchu, liczby udanych i nieudanych zapytań dają dobre pojęcie o aktywności botów.
Zapytanie SQL raportu
SELECT
1 AS 'Tabela: Uciążliwe Boty',
MAX(USG.AGENT_BOT) AS 'Bot',
ROUND(SUM(FCT.BYTES) / 1000 / 14.0, 1) AS 'KB na Dzień',
ROUND(SUM(FCT.IP_CNT) / 14.0, 1) AS 'IP na Dzień',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Błąd Klienta', 'Błąd Serwera') THEN FCT.REQUEST_CNT / 14.0 ELSE 0 END), 1) AS 'Błędne Zapytania na Dzień',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Udało się', 'Przekierowanie') THEN FCT.REQUEST_CNT / 14.0 ELSE 0 END), 1) AS 'Udało się Zapytania na Dzień',
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 10W tym przypadku wynikiem analizy była decyzja o ograniczeniu dostępu do strony poprzez dodanie do pliku robots.txt.
User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
Pierwsze dwa boty zniknęły z tabeli, a roboty MS przesunęły się w dół z pierwszych miejsc.
Dzień i czas największej aktywności
W ruchu widać wzrosty. Aby je szczegółowo zbadać, należy wyodrębnić czas ich wystąpienia, niekoniecznie wyświetlając wszystkie godziny i dni pomiaru czasu. Ułatwi to znalezienie pojedynczych zapytań w pliku logu w razie potrzeby szczegółowej analizy.
Zapytanie SQL raportu
SELECT
1 AS 'Linia: Dzień i godzina hitów od użytkowników i botów',
strftime('%d.%m-%H', datetime(EVENT_DT, 'unixepoch')) AS 'Data Czas',
HIB AS 'Boty, Hity',
HIU AS 'Użytkownicy, Hity'
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_DTObserwujemy najbardziej aktywne godziny 11, 14 i 20 pierwszego dnia na wykresie. A następnego dnia o 13 aktywność wykazywały boty.
Średnia dzienna aktywność użytkowników według tygodni
Trochę uporządkowaliśmy aktywność i ruch. Następnym pytaniem była aktywność samych użytkowników. Dla tych statystyk pożądane są długie okresy agregacji, na przykład tydzień.
Zapytanie SQL raportu
SELECT
1 as 'Linia: Średnia dzienna aktywność użytkowników według tygodnia',
strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Tydzień',
ROUND(1.0*SUM(FCT.PAGE_CNT)/SUM(FCT.IP_CNT),1) AS 'Strony na IP dziennie',
ROUND(1.0*SUM(FCT.FILE_CNT)/SUM(FCT.IP_CNT),1) AS 'Pliki na IP dziennie'
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.' /* tylko użytkownicy */
AND HST.STATUS_GROUP IN ('Successful') /* dobre strony */
AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-3 month')
GROUP BY strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDER BY FCT.EVENT_DTStatystyki za tydzień pokazują, że przeciętnie jeden użytkownik otwiera 1,6 stron dziennie. Liczba żądanych plików na jednego użytkownika zależy od dodawania nowych plików na stronie.
Wszystkie żądania i ich statusy
Webalizer zawsze pokazywał konkretne kody stron i chciałem zawsze widzieć po prostu liczbę udanych żądań oraz błędów.
Zapytanie SQL raportu
SELECT
1 as 'Linia: Wszystkie żądania według statusu',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Dzień',
SUM(CASE WHEN STS.STATUS_GROUP='Successful' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Sukces',
SUM(CASE WHEN STS.STATUS_GROUP='Redirection' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Przekierowanie',
SUM(CASE WHEN STS.STATUS_GROUP='Client Error' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Błąd klienta',
SUM(CASE WHEN STS.STATUS_GROUP='Server Error' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Błąd serwera'
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_DTRaport pokazuje zapytania, a nie kliknięcia (odwiedziny), w przeciwieństwie do metryki LINE_CNT, metryka REQUEST_CNT jest liczona jako COUNT(DISTINCT STG.REQUEST_NK). Celem jest pokazanie efektywnych zdarzeń, na przykład roboty MS setki razy dziennie odpychają plik robots.txt i w takim przypadku takie odpychania będą liczone tylko raz. To pozwala wygładzić skoki na wykresie.
Z wykresu można zobaczyć wiele błędów — to nieistniejące strony. Wynikiem analizy było dodanie przekierowań ze usuniętych stron.
Błędne zapytania
Aby szczegółowo przeanalizować zapytania, można wygenerować szczegółową statystykę.
Zapytanie SQL raportu
SELECT
1 AS 'Tabela: Najczęstsze błędy zapytań',
REQ.REQUEST_NK AS 'Zapytanie',
'Błąd' AS 'Status zapytania',
ROUND(SUM(FCT.LINE_CNT) / 14.0, 1) AS 'Odwiedziny dziennie',
ROUND(SUM(FCT.IP_CNT) / 14.0, 1) AS 'IP dziennie',
ROUND(SUM(FCT.BYTES)/1000 / 14.0, 1) AS 'KB dziennie'
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 ('Błąd klienta', 'Błąd serwera')
AND datetime(FCT.EVENT_DT, 'unixepoch') >= date('now', '-14 day')
GROUP BY REQ.REQUEST_NK
ORDER BY 4 DESC
LIMIT 20Na tej liście znajdą się także wszystkie próby połączeń, takie jak zapytanie do /wp-login.php Dzięki dostosowaniu zasad przepisywania zapytań serwerem można skorygować reakcję serwera na takie zapytania i kierować je na stronę startową.
Zatem kilka prostych raportów na podstawie pliku dziennika serwera daje dość pełny obraz tego, co dzieje się na stronie.
Jak uzyskać informacje?
Bazy danych sqlite są wystarczające. Stwórzmy tabele: pomocniczą do logowania procesów ETL.
Tabela staging, do której będziemy zapisywać pliki dziennika za pomocą PHP. Dwie tabele agregatów. Stworzymy tabelę dzienną ze statystyką według agentów użytkowników i statusów zapytań. Godzinną tabelę z statystykami zapytań, grup statusów i agentów. Cztery tabele odpowiednich wymiarów.
W wyniku otrzymano następujący model relacyjny:
Model danych
Skrypt do tworzenia obiektu w bazie danych sqlite:
DDL tworzenie obiektu
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);Staging
W przypadku pliku access.log konieczne jest odczytanie, sparsowanie i zapisanie w bazie wszystkich zapytań. Można to zrobić bezpośrednio za pomocą języka skryptowego lub korzystając z narzędzi sqlite.
Format pliku dziennika:
//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-]+) "(.*)" "(.*)"$/';
Propagacja kluczy
Gdy surowe dane znajdują się w bazie, należy zapisać w tabelach wymiarów klucze, których tam nie ma. Wtedy możliwe będzie zbudowanie odwołania do wymiarów. Na przykład w tabeli DIM_REFERRER kluczem jest kombinacja trzech pól.
Zapytanie SQL dotyczące propagacji kluczy
/* 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 NULLPropagacja do tabeli użytkowników może zawierać logikę botów, na przykład fragment sql:
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 'inny'
ELSE 'n.a.' END AS AGENT_BOTTabele agregatów
Na koniec załadujemy tabele agregatów, na przykład dzienna tabela może być ładowana w następujący sposób:
Zapytanie SQL do załadunku agregatu
/* 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_IDBaza danych SQLite pozwala na tworzenie złożonych zapytań. WITH zawiera przygotowanie danych i kluczy. Główne zapytanie gromadzi wszystkie odwołania do wymiarów.
Warunek nie pozwoli załadować historii ponownie: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, gdzie parametr jest wynikiem zapytania
‘SELECT COALESCE(MAX(EVENT_DT), ‘3600’) AS LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD’
Warunek załaduje tylko pełny dzień: CAST(STG.EVENT_DT AS INTEGER) < strftime(‘%s’, date(‘now’, ‘start of day’))
Liczba stron lub plików jest obliczana w sposób prymitywny, poprzez wyszukiwanie punktu.
Raporty
W skomplikowanych systemach wizualizacji istnieje możliwość tworzenia meta-modelu na podstawie obiektów bazy danych, dynamicznego zarządzania filtrami i regułami agregacji. Ostatecznie, wszystkie porządne narzędzia generują zapytanie SQL.
W tym przykładzie stworzymy już gotowe zapytania SQL i zapiszemy je w postaci widoku w bazie danych — to są raporty.
Wizualizacja
Jako narzędzie wizualizacji użyto Bluff: Beautiful graphs in JavaScript
W tym celu wymagało to przebiegu przez wszystkie raporty za pomocą PHP i wygenerowania pliku HTML z tabelami.
$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'
);Narzędzie po prostu wizualizuje tabele wyników.
Wnioski
Na przykładzie analizy internetowej artykuł opisuje mechanizmy niezbędne do budowy magazynów danych. Jak pokazują wyniki, do głębokiej analizy i wizualizacji danych wystarczą najprostsze narzędzia.
W dalszej części, na przykładzie tego magazynu, spróbujemy zrealizować takie struktury jak wolno zmieniające się wymiary, metadane, poziomy agregacji oraz integrację danych z różnych źródeł.
Szczegółowo przyjrzymy się również najprostszemu narzędziu do zarządzania procesami ETL opartym na jednej tabeli.
Wracamy do tematu pomiaru jakości danych i automatyzacji tego procesu.
Zbadamy problemy technicznego otoczenia i utrzymania magazynów danych, dla których zrealizujemy serwer magazynu z minimalnymi zasobami, na przykład na bazie Raspberry Pi.
Źródło: habr.com
