De Webalizer-tool en Google Analytics hebben jarenlang geholpen om een idee te krijgen van wat er gebeurt op websites. Tegenwoordig begrijp ik dat ze zeer weinig nuttige informatie bieden. Met toegang tot mijn access.log-bestand is het heel eenvoudig om de statistieken te begrijpen, en hiervoor zijn elementaire hulpmiddelen zoals sqlite, html, de taal sql en elke programmeertaal voldoende.
De gegevensbron voor Webalizer is het access.log-bestand. de server. Dit zijn de kolommen en cijfers die alleen het totale verkeersvolume duidelijk maken:
Hulpmiddelen zoals Google Analytics verzamelen gegevens van de geladen pagina zelf. Ze tonen ons een paar diagrammen en lijnen, waarop het vaak moeilijk is om de juiste conclusies te trekken. Misschien had ik meer moeite moeten doen? Ik weet het niet.
Dus, wat wilde ik in de bezoekersstatistieken van de website zien?
Het verkeer van gebruikers en bots.
Vaak heeft websiteverkeer beperkingen, en het is nodig om te zien hoeveel nuttig verkeer wordt gebruikt. Bijvoorbeeld zo:
SQL-query voor rapport
SELECT
1 as 'StackedArea: Verkeer gegenereerd door Gebruikers en Bots',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Dag',
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 'Gebruikers, 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_DTUit de grafiek blijkt een constante activiteit van bots. Het zou interessant zijn om de meest actieve vertegenwoordigers gedetailleerd te bestuderen.
Lastige bots.
We classificeren bots op basis van informatie van de gebruikersagent. Verdere statistieken over dagverkeer, het aantal succesvolle en mislukte verzoeken geven een goed overzicht van de activiteit van bots.
SQL-query voor rapport
SELECT
1 AS 'Tabel: Lastige Bots',
MAX(USG.AGENT_BOT) AS 'Bot',
ROUND(SUM(FCT.BYTES) / 1000 / 14.0, 1) AS 'KB per Dag',
ROUND(SUM(FCT.IP_CNT) / 14.0, 1) AS 'IPs per Dag',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Client Error', 'Server Error') THEN FCT.REQUEST_CNT / 14.0 ELSE 0 END), 1) AS 'Foutverzoeken per Dag',
ROUND(SUM(CASE WHEN STS.STATUS_GROUP IN ('Successful', 'Redirection') THEN FCT.REQUEST_CNT / 14.0 ELSE 0 END), 1) AS 'Sucessverzoeken per Dag',
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 dit geval was de analyse het resultaat van de beslissing om de toegang tot de site te beperken door deze toe te voegen aan het robots.txt-bestand.
Gebruikersagent: AhrefsBot
Verbieden: /
Gebruikersagent: dotbot
Verbieden: /
Gebruikersagent: bingbot
Crawl-vertraging: 5
De eerste twee bots zijn uit de tabel verdwenen, en de MS-bots zijn van de bovenste rijen naar beneden verschoven.
Dag en tijd van hoogste activiteit
In het verkeer zijn pieken zichtbaar. Om deze gedetailleerd te onderzoeken, is het nodig om tijdstippen van hun ontstaan aan te duiden, zonder dat het nodig is om alle uren en dagen van de tijdmetingen weer te geven. Dit maakt het gemakkelijker om individuele verzoeken in het logbestand te vinden wanneer een grondige analyse vereist is.
SQL-query voor rapport
SELECT
1 AS 'Regel: Dag en Uur van Hits van Gebruikers en Bots',
strftime('%d.%m-%H', datetime(EVENT_DT, 'unixepoch')) AS 'Datum Tijd',
HIB AS 'Bots, Hits',
HIU AS 'Gebruikers, 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_DTWe zien de meest actieve uren op de eerste dag om 11, 14 en 20 uur. De volgende dag waren de bots actief om 13 uur.
Gemiddelde dagelijkse activiteit van gebruikers per week
We hebben de activiteit en het verkeer iets begrepen. De volgende vraag was de activiteit van de gebruikers zelf. Voor deze statistieken zijn lange aggregatieperioden wenselijk, bijvoorbeeld een week.
SQL-query voor rapport
SELECT
1 as 'Regel: Gemiddelde Dagelijkse Activiteit van Gebruikers per Week',
strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Week',
ROUND(1.0*SUM(FCT.PAGE_CNT)/SUM(FCT.IP_CNT),1) AS 'Paginas per IP per Dag',
ROUND(1.0*SUM(FCT.FILE_CNT)/SUM(FCT.IP_CNT),1) AS 'Bestanden per IP per Dag'
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.' /* alleen gebruikers */
AND HST.STATUS_GROUP IN ('Successful') /* goede pagina's */
AND datetime(FCT.EVENT_DT, 'unixepoch') > date('now', '-3 month')
GROUP BY strftime('%W week', datetime(FCT.EVENT_DT, 'unixepoch'))
ORDER BY FCT.EVENT_DTStatistieken voor de week tonen aan dat gemiddeld één gebruiker 1,6 pagina's per dag opent. Het aantal opgevraagde bestanden per gebruiker hangt in dit geval af van de toevoeging van nieuwe bestanden op de site.
Alle verzoeken en hun statussen
Webalizer toonde altijd specifieke pagina-codes en het was altijd fijn om gewoon het aantal succesvolle aanvragen en fouten te zien.
SQL-query voor rapport
SELECT
1 as 'Regel: Alle Verzoeken per Status',
strftime('%d.%m', datetime(FCT.EVENT_DT, 'unixepoch')) AS 'Dag',
SUM(CASE WHEN STS.STATUS_GROUP='Successful' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Succes',
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 'Klantenfout',
SUM(CASE WHEN STS.STATUS_GROUP='Server Error' THEN FCT.REQUEST_CNT ELSE 0 END) AS 'Serverfout'
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_DTHet rapport toont aanvragen en niet klikken (hits); in tegenstelling tot de LINE_CNT-metriek wordt REQUEST_CNT berekend als COUNT(DISTINCT STG.REQUEST_NK). Het doel is om effectieve gebeurtenissen te tonen, bijvoorbeeld, MS-bots die honderden keren per dag het bestand robots.txt opvragen en in dit geval worden zulke aanvragen maar één keer geteld. Dit helpt om pieken in de grafiek te verzachten.
Uit de grafiek zijn veel fouten te zien — dit zijn niet-bestaande pagina's. De uitkomst van de analyse was het toevoegen van omleidingen vanaf verwijderde pagina's.
Foutieve aanvragen
Voor een gedetailleerde beoordeling van de aanvragen kan gedetailleerde statistiek worden weergegeven.
SQL-query voor rapport
SELECT
1 AS 'Tabel: Top Foutieve Aanvragen',
REQ.REQUEST_NK AS 'Aanvraag',
'Fout' AS 'Aanvraagstatus',
ROUND(SUM(FCT.LINE_CNT) / 14.0, 1) AS 'Hits per Dag',
ROUND(SUM(FCT.IP_CNT) / 14.0, 1) AS 'IPs per Dag',
ROUND(SUM(FCT.BYTES)/1000 / 14.0, 1) AS 'KB per Dag'
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 deze lijst zullen ook alle doorverbindingen staan, bijvoorbeeld een aanvraag naar /wp-login.php. Door de herschrijvingsregels aan te passen, server kan de serverreactie op dergelijke aanvragen worden gecorrigeerd en kunnen ze naar de startpagina worden gestuurd.
Dus, enkele eenvoudige rapporten op basis van het serverlogbestand geven een vrij compleet beeld van wat er op de website gebeurt.
Hoe krijg ik informatie?
SQLite-databases zijn meer dan genoeg. Laten we tabellen aanmaken: een hulptable voor het loggen van ETL-processen.
Een staging-tabel waar we logbestanden met PHP zullen schrijven. Twee aggregatietabellen. Laten we een dagelijkse tabel maken met statistieken voor gebruikersagenten en aanvraagstatussen. Een uurtabel met statistieken voor aanvragen, statusgroepen en agenten. Vier tabellen voor de bijbehorende dimensies.
Het resultaat is het volgende relationale model:
Datamodel
Script voor het creëren van een object in de SQLite-database:
DDL-objectcreatie
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
In het geval van het access.log-bestand moeten alle aanvragen worden gelezen, geparsed en in de database worden geschreven. Dit kan ofwel direct met behulp van de scripts, of met behulp van SQLite-hulpmiddelen.
Logbestandformaat:
//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-]+) "(.*)" "(.*)"$/';
Sleutels propagatie
Wanneer ruwe gegevens zich in de database bevinden, moeten de sleutels die daar niet zijn, in de meettabellen worden geschreven. Dan is het mogelijk om een link naar de metingen te bouwen. Bijvoorbeeld, in de tabel DIM_REFERRER is de sleutel een combinatie van drie velden.
SQL-query voor sleutels propagatie
/* 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 NULLDe propagatie in de tabel van gebruikersagenten kan logica voor bots bevatten, bijvoorbeeld een SQL-fragment:
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_BOTAggregatentabellen
Als laatste zullen we de aggregatentabellen laden; bijvoorbeeld, de dagelijkse tabel kan als volgt worden geladen:
SQL-query voor het laden van een aggregaat
/* 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_IDDe SQLite-database maakt het mogelijk om complexe queries te schrijven. WITH bevat de voorbereiding van gegevens en sleutels. De hoofdquery verzamelt alle links naar de metingen.
De voorwaarde zorgt ervoor dat de geschiedenis niet opnieuw wordt geladen: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, waarbij de parameter het resultaat van de query is
‘SELECT COALESCE(MAX(EVENT_DT), '3600') AS LAST_EVENT_EPOCH FROM FCT_ACCESS_USER_AGENT_DD’
De voorwaarde laadt alleen een volledige dag: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('now', 'start of day'))
Het tellen van pagina's of bestanden gebeurt op een primitieve manier, door naar een punt te zoeken.
Rapporten
In complexe visualisatiesystemen is het mogelijk om een meta-model te creëren op basis van database-objecten en dynamisch filters en aggregatieregels te beheren. Uiteindelijk genereren alle fatsoenlijke tools een SQL-query.
In dit voorbeeld zullen we vooraf gemaakte SQL-queries creëren en deze opslaan als views in de database — dat zijn de rapporten.
Visualisatie
Als visualisatietool werd Bluff gebruikt: prachtige grafieken in JavaScript
Hiervoor was het nodig om met PHP door alle rapporten te lopen en een HTML-bestand met tabellen te genereren.
$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'
);De tool visualiseert eenvoudig de resulteren tabellen.
Uitslag
Aan de hand van webanalyse beschrijft het artikel de mechanismen die nodig zijn voor het opbouwen van datawarehouses. Zoals uit de resultaten blijkt, zijn de eenvoudigste tools voldoende voor een grondige analyse en visualisatie van data.
We zullen deze structuur gebruiken als voorbeeld om zaken te implementeren zoals langzaam veranderende dimensies, metadata, aggregatieniveaus en de integratie van gegevens uit verschillende bronnen.
Daarnaast bekijken we een eenvoudige tool voor het beheren van ETL-processen op basis van één tabel.
Laten we terugkeren naar het onderwerp van datakwaliteit en de automatisering van dit proces.
We onderzoeken de problemen van de technische omgeving en het onderhoud van datawarehouses, waarvoor we een opslagserver met minimale middelen zullen inzetten, bijvoorbeeld op basis van een Raspberry Pi.
Bron: habr.com
