Webalizer en Google Analytics helpen mij al jaren inzicht te krijgen in wat er op websites gebeurt. Nu begrijp ik dat ze heel weinig nuttige informatie bieden. Als u toegang heeft tot uw access.log-bestand, is het heel gemakkelijk om de statistieken te begrijpen en vrij eenvoudige tools te implementeren, zoals sqlite, html, de sql-taal en elke script-programmeertaal.
De gegevensbron voor Webalizer is het access.log-bestand van de server. Zo zien de balken en cijfers eruit, waaruit alleen het totale verkeersvolume duidelijk is:
Tools zoals Google Analytics verzamelen zelf gegevens van de geladen pagina. Ze laten ons een aantal diagrammen en lijnen zien, op basis waarvan het vaak moeilijk is om juiste conclusies te trekken. Had er misschien meer moeite gedaan moeten worden? Weet het niet.
Wat wilde ik zien in de bezoekersstatistieken van de website?
Gebruikers- en botverkeer
Vaak is het siteverkeer beperkt en is het noodzakelijk om te zien hoeveel nuttig verkeer er wordt gebruikt. Bijvoorbeeld zoals dit:
SQL-rapportquery
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_DTDe grafiek toont de constante activiteit van bots. Het zou interessant zijn om de meest actieve vertegenwoordigers in detail te bestuderen.
Vervelende bots
We classificeren bots op basis van user-agentinformatie. Aanvullende statistieken over het dagelijkse verkeer, het aantal succesvolle en niet-succesvolle verzoeken geven een goed beeld van de botactiviteit.
SQL-rapportquery
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 dit geval was het resultaat van de analyse de beslissing om de toegang tot de site te beperken door deze toe te voegen aan het robots.txt-bestand
User-agent: AhrefsBot
Disallow: /
User-agent: dotbot
Disallow: /
User-agent: bingbot
Crawl-delay: 5
De eerste twee bots verdwenen van de tafel en de MS-robots gingen vanaf de eerste rijen naar beneden.
Dag en tijd van de grootste activiteit
Oplevingen zijn zichtbaar in het verkeer. Om ze in detail te bestuderen, is het noodzakelijk om het tijdstip waarop ze voorkomen te markeren, en het is niet nodig om alle uren en dagen van tijdmeting weer te geven. Dit maakt het gemakkelijker om individuele verzoeken in het logbestand te vinden als een gedetailleerde analyse nodig is.
SQL-rapportquery
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_DTWe observeren de meest actieve uren 11, 14 en 20 van de eerste dag op de kaart. Maar de volgende dag om 13 uur waren de bots actief.
Gemiddelde dagelijkse gebruikersactiviteit per week
We hebben de zaken een beetje op orde gebracht met activiteit en verkeer. De volgende vraag was de activiteit van de gebruikers zelf. Voor dergelijke statistieken zijn lange aggregatieperioden, zoals een week, wenselijk.
SQL-rapportquery
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_DTUit wekelijkse statistieken blijkt dat gemiddeld één gebruiker 1,6 pagina's per dag opent. Het aantal opgevraagde bestanden per gebruiker is in dit geval afhankelijk van de toevoeging van nieuwe bestanden aan de site.
Alle verzoeken en hun status
Webalizer liet altijd specifieke paginacodes zien en ik wilde altijd alleen het aantal succesvolle verzoeken en fouten zien.
SQL-rapportquery
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_DTIn het rapport worden verzoeken weergegeven, geen klikken (treffers). In tegenstelling tot LINE_CNT wordt de REQUEST_CNT-statistiek berekend als COUNT(DISTINCT STG.REQUEST_NK). Het doel is om effectieve gebeurtenissen weer te geven. MS-bots pollen bijvoorbeeld honderden keren per dag het robots.txt-bestand en in dit geval worden dergelijke polls één keer geteld. Hiermee kunt u sprongen in de grafiek gladstrijken.
Uit de grafiek kun je veel fouten zien - dit zijn niet-bestaande pagina's. Het resultaat van de analyse was de toevoeging van omleidingen vanaf externe pagina's.
Slechte verzoeken
Om verzoeken gedetailleerd te onderzoeken, kunt u gedetailleerde statistieken weergeven.
SQL-rapportquery
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 20Deze lijst bevat ook alle oproepen, bijvoorbeeld een verzoek aan /wp-login.php. Door de regels voor het herschrijven van verzoeken door de server aan te passen, kunt u de reactie van de server op dergelijke verzoeken aanpassen en deze naar de startpagina sturen.
Een paar eenvoudige rapporten op basis van het serverlogbestand geven dus een redelijk compleet beeld van wat er op de site gebeurt.
Hoe informatie verkrijgen?
Een sqlite-database is voldoende. Laten we tabellen maken: hulpprogramma voor het loggen van ETL-processen.
Tabelfase waarin we logbestanden zullen schrijven met behulp van PHP. Twee aggregatietabellen. Laten we een dagelijkse tabel maken met statistieken over user-agents en verzoekstatussen. Per uur met statistieken over aanvragen, statusgroepen en agenten. Vier tabellen met relevante metingen.
Het resultaat is het volgende relationele model:
Gegevensmodel
Script om een object in een sqlite-database te maken:
Creëren van DDL-objecten
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);Fase
In het geval van het access.log-bestand is het noodzakelijk om alle verzoeken naar de database te lezen, parseren en schrijven. Dit kan rechtstreeks worden gedaan met behulp van een scripttaal of met behulp van sqlite-tools.
Formaat logbestand:
//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-]+) "(.*)" "(.*)"$/';
Sleutelpropagatie
Wanneer de onbewerkte gegevens in de database staan, moet u sleutels die er niet zijn, in de meettabellen schrijven. Dan is het mogelijk om een referentie naar de metingen op te bouwen. In de tabel DIM_REFERRER is de sleutel bijvoorbeeld een combinatie van drie velden.
Query voor het doorgeven van SQL-sleutels
/* 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 NULLDoorgifte naar de user-agenttabel kan botlogica bevatten, bijvoorbeeld het 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_BOTGeaggregeerde tabellen
Ten slotte zullen we de aggregatietabellen laden; de dagelijkse tabel kan bijvoorbeeld als volgt worden geladen:
SQL-query voor het laden van 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_IDMet de SQLite-database kunt u complexe query's schrijven. WITH bevat de voorbereiding van gegevens en sleutels. De hoofdquery verzamelt alle verwijzingen naar dimensies.
De voorwaarde staat niet toe dat de geschiedenis opnieuw wordt geladen: CAST(STG.EVENT_DT AS INTEGER) > $param_epoch_from, waarbij de parameter het resultaat is van het verzoek
'SELECT COALESCE(MAX(EVENT_DT), '3600') ALS LAST_EVENT_EPOCH VAN FCT_ACCESS_USER_AGENT_DD'
De voorwaarde wordt alleen de volledige dag geladen: CAST(STG.EVENT_DT AS INTEGER) < strftime('%s', date('nu', 'begin van de dag'))
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 metamodel te creëren op basis van databaseobjecten, en filters en aggregatieregels dynamisch te beheren. Uiteindelijk genereren alle fatsoenlijke tools een SQL-query.
In dit voorbeeld maken we kant-en-klare SQL-query's en slaan deze op als views in de database - dit zijn rapporten.
Visualisatie
Bluff: Er werd gebruik gemaakt van prachtige grafieken in JavaScript als visualisatietool
Om dit te doen, was het nodig om alle rapporten met behulp van PHP te doorlopen 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 eenvoudigweg tabellen met resultaten.
Uitgang
Met webanalyse als voorbeeld beschrijft het artikel de mechanismen die nodig zijn om datawarehouses te bouwen. Zoals uit de resultaten blijkt, zijn de eenvoudigste tools voldoende voor diepgaande analyse en visualisatie van gegevens.
In de toekomst zullen we, met deze repository als voorbeeld, proberen structuren als langzaam veranderende dimensies, metadata, aggregatieniveaus en integratie van gegevens uit verschillende bronnen te implementeren.
Laten we ook eens nader kijken naar de eenvoudigste tool voor het beheren van ETL-processen op basis van een enkele tabel.
Laten we terugkeren naar het onderwerp van het meten van de gegevenskwaliteit en het automatiseren van dit proces.
We zullen de problemen van de technische omgeving en het onderhoud van dataopslag bestuderen, waarvoor we een opslagserver met minimale middelen zullen implementeren, bijvoorbeeld op basis van een Raspberry Pi.
Bron: www.habr.com