Demo

Who really visits a website, and what to fix first

Run a small forensics pipeline over a week of raw web log rows from an invented neighbourhood bakery’s website. It queries the log, looks up who owns each network, checks every claimed search crawler, separates real visitors from crawlers, datacenter traffic and the site team’s own sessions, then writes a summary and a remediation plan that points at the evidence.

This is a simulation. No AI model runs on this page, and nothing is looked up: the log, the network owners, the DNS answers and the site checks are all invented, and the addresses are from ranges kept for documentation. The queries are shown as they would run in DuckDB; here the same steps run as plain code in your browser. Nothing you do leaves your browser or is kept, the evidence file is made in your browser, and reloading the page starts again.

The pipeline

Sample log

Each log is an invented week, days 1 to 7, made from its number: the same log always gives the same results.

Collect the log: Not run yet.
Load and query: Not run yet.
Enrich the addresses: Not run yet.
Classify the traffic: Not run yet.
Write the report: Not run yet.

1. The raw log

Each row is one request the site answered: the day and time, the address it came from, what was asked for, the status of the answer and the user agent the requester claimed. The class column fills in once the traffic is classified.

Show rows from

Log rows
Row	Day	Time	Address	Request	Status	User agent	Class

2. Loaded and queried

In the pipeline the rows load into DuckDB and these queries run over them. Here the same steps run as plain code in your browser, and each table shows what the query returns. The requests table holds one row per log line: its day, time, address (ip, and ip_number, the same address as a number so it sorts correctly), method, path, status and user agent.

Requests and addresses per day

SELECT day, count(*) AS requests, count(DISTINCT ip) AS addresses
FROM requests
GROUP BY day
ORDER BY day;

The five busiest addresses

SELECT ip, count(*) AS requests, count(DISTINCT path) AS paths
FROM requests
GROUP BY ip, ip_number
ORDER BY -count(*), ip_number
LIMIT 5;

The five most requested paths

SELECT path, count(*) AS requests, count(*) FILTER (WHERE status >= 400) AS errors
FROM requests
GROUP BY path
ORDER BY -count(*), path
LIMIT 5;

What the user agents claim

SELECT agent, count(*) AS requests, count(DISTINCT ip) AS addresses
FROM requests
GROUP BY agent
ORDER BY -count(*), agent;

3. Enriched with network owners

A user agent is whatever the requester chooses to write, so the pipeline asks who owns each address’s network instead. In the pipeline a free DNS-based lookup answers that; here a mocked ownership table stands in for it.

Who owns each network

SELECT r.ip, o.owner, o.kind, count(*) AS requests
FROM requests r
LEFT JOIN owners o ON r.ip_number BETWEEN o.first_number AND o.last_number
GROUP BY r.ip, o.owner, o.kind;

Checking every claimed search crawler

Anyone can claim to be a search crawler. A real one has a reverse DNS name under the search company’s own name, and a forward lookup of that name leads back to the same address.

4. Classified

Each address gets one class, by the first rule that fits:

Search crawler: it claims to be one, and reverse DNS with a forward lookup confirms it.
Datacenter or bot: its network belongs to a hosting company, whatever its user agent says.
Site team: at least the chosen share of its requests go to pages that only work when signed in, so one stray request never removes a real visitor.
Likely genuine visitor: everything left, on home broadband and mobile networks.

Site team line

Try 5%: a visitor who typed the admin address once becomes the site team. Try 50%: the team member who mostly reads orders on a phone becomes a visitor.

Count by Requests Addresses

Share of requests in each class

Likely genuine visitors
By user agent alone:
By network owner and behaviour:
Search crawlers
By user agent alone:
By network owner and behaviour:
Datacenter traffic and bots
By user agent alone:
By network owner and behaviour:
The site team
By user agent alone:
By network owner and behaviour:

The classification query

WITH team AS (
  SELECT ip FROM requests
  GROUP BY ip
  HAVING avg(CASE WHEN path LIKE '/admin/%' OR path LIKE '/edit/%' THEN 1 ELSE 0 END) >= 0.20
)
SELECT r.*, CASE
  WHEN c.confirmed THEN 'search crawler'
  WHEN o.kind IS NULL OR o.kind IN ('hosting', 'search') THEN 'datacenter or bot'
  WHEN r.ip IN (SELECT ip FROM team) THEN 'site team'
  ELSE 'visitor'
END AS class
FROM requests r
LEFT JOIN owners o ON r.ip_number BETWEEN o.first_number AND o.last_number
LEFT JOIN crawler_checks c USING (ip);

Every address and why it got its class

What the demo shows

A pipeline in stages. Collect the log, load and query it, enrich it, classify it, report. Each stage builds on the one before, and the same log always gives the same results.
Classification that does not trust the user agent. Two addresses claim to be a search crawler and scrapers claim to be browsers. Network ownership and a reverse and forward DNS check sort them out, and the chart shows how far the user agent alone would have been off.
The site team found by behaviour. An address counts as the site team only when a real share of its requests go to pages that need a sign-in, so a visitor who typed the admin address once stays a visitor. Change the line and watch who moves.
Evidence behind every finding. Each item in the remediation plan links to the exact log rows or site checks behind it, and the evidence file lists them all.
A report for people who do not read logs. The executive summary gives the few numbers that matter and the three things to fix first.