ADLAB logo
  • script
  • sql

Server log audit kit: tell real Googlebot from fakes with ClickHouse

Scripts and SQL: audit nginx or Apache logs with clickhouse-local, check each bot's IP against official ranges, and get a 19-section report.

  • v1.0
  • updated 8 November 2026
  • MIT
  • Vladislav Krivorutško
Download all (.zip)Jump to the text

In one audit, 531 of 2,278 requests that called themselves Google, 23 %, came from 21 IP addresses outside Google's ranges. The site was a private directory-type site with about 141,000 requests in 30 days. The same log held 1,747 requests from confirmed Googlebot and 7,356 from confirmed OpenAI and Perplexity bots.

This kit repeats that check on your own server logs. It is bash and SQL, run by clickhouse-local on your machine, and it ends in one text report. It is written for site owners and SEOs who can open a terminal, and for anyone who wants an AI agent to run it (see the last section). Nothing is uploaded: the only network calls fetch the crawler IP ranges, your sitemap and, once, the ClickHouse binary.

Quick start

  1. Unzip the download and enter the folder:

    bash
    unzip log-audit-kit.zip && cd log-audit-kit

  2. Install ClickHouse. Skip this if clickhouse is already on your PATH or in ~/bin or ~/.local/bin. The dry run prints the exact command, the download size (about 1.3 GB as of 2026-11) and your free disk space, and downloads nothing:

    bash
    bash install.sh --dry-run
bash install.sh

    On Windows, install WSL first (wsl --install -d Ubuntu in PowerShell), then work inside the Ubuntu terminal, in your Linux home folder.

  3. Run the sample. It is synthetic, uses example.com and includes real Googlebot addresses next to fake ones:

    bash
    SITEMAP_FILE=sample/sitemap.sample.txt bash run.sh -d example.com -i 203.0.113.10 sample/access.sample.log

    The first run downloads the official IP ranges into ranges.tsv. The script prints done: .../report.txt.

  4. Run it on your logs. Give files, a folder or a quoted glob; .gz works. Use access logs only, never error.log. Add -i with a comma-separated list of your own server and monitor IPs, for example -i 198.51.100.7,198.51.100.8, so your own checks are not counted as bots:

    bash
    bash run.sh -d yourdomain.com /var/log/nginx/access.log*

  5. Read report.txt, then delete the work folder (log-audit-yourdomain.com/). It holds logs.parquet and enriched.parquet, which contain visitor IPs.

The sitemap step is optional: SKIP_SITEMAP=1 skips it, SITEMAP_FILE=urls.txt replaces it with your own URL list. Without a sitemap the report says so in section 0, and sections 6 and 7 and the pct_useful columns are not meaningful. Everything else still works.

What it does

StepFileResult
0ranges.shranges.tsv: 13 official lists, 2,585 prefixes on 2026-11-08. Refreshed when older than 30 days.
1build_combined.sqllogs.parquet. The script stops if fewer than 95 % of the lines parse.
2enrich.sqlenriched.parquet: bot, class, verified, page or asset.
3sitemap.shsm_urls.txt: robots.txt, sitemap index, child sitemaps (two levels, .xml.gz included).
4report.sqlreport.txt with 19 sections.

The log must be nginx or Apache combined:

text
203.0.113.5 - - [10/Nov/2026:13:55:36 +0000] "GET /page/?x=1 HTTP/1.1" 200 5316 "https://www.google.com/" "Mozilla/5.0 ..."

JSON logs and CDN exports such as Cloudflare Logpush need their own pattern: copy build_combined.sql to build_<name>.sql, change the regular expression and run with LOG_FORMAT=<name>.

Files, grouped by job

12 files · or all in one zip

Read me

2 files

  • README.md

    Requirements, install, 15-minute run, log format, how to read the report, limits

    8.3 KB

  • LICENSE

    MIT License, ADLAB OÜ, 2026

    1.0 KB

Run it

2 files

  • run.sh

    Runs the whole audit: logs, IP ranges, classification, sitemap, report

    8.8 KB

  • install.sh

    Installs the ClickHouse binary with ClickHouse's own installer; --dry-run checks first

    5.6 KB

Parse and enrich

2 files

  • build_combined.sql

    Parses nginx/Apache combined logs into logs.parquet

    1.6 KB

  • enrich.sql

    Adds bot name, traffic class, verified flag (IP in official ranges) and page or asset

    7.0 KB

Report and inputs

3 files

  • report.sql

    The 19 sections of the text report

    12 KB

  • ranges.sh

    Downloads the official crawler IP ranges (Google, Bing, Apple, OpenAI, Perplexity, Anthropic)

    4.9 KB

  • sitemap.sh

    Collects all sitemap URLs (optional step)

    2.6 KB

Try it first

3 files

  • sample/make_sample_log.py

    Generator for the synthetic sample log

    13 KB

  • sample/access.sample.log

    Synthetic log of 1,954 lines for example.com, with real and fake bots

    415 KB

  • sample/sitemap.sample.txt

    The 108 sitemap URLs that go with the sample log

    3.7 KB

How bots are classified and checked

Every request gets a bot name from its User-Agent and a class. Then the source IP is compared with the operator's official ranges, which gives the verified column: true (IP inside the ranges), false (a bot name with an IP outside them, so a fake) or empty (the operator publishes no usable list).

ClassBots (examples)
googleGooglebot Smartphone and Desktop, Google-InspectionTool, GoogleOther, AdsBot-Google, Storebot-Google
bing, apple, yandexbingbot, Applebot, YandexBot
ai-trainingGPTBot, ClaudeBot, CCBot, Bytespider, meta-externalagent, Amazonbot
ai-searchOAI-SearchBot, Claude-SearchBot, PerplexityBot, DuckAssistBot, YouBot
ai-userChatGPT-User, Claude-User, Perplexity-User, meta-externalfetcher, MistralAI-User
seo-toolAhrefsBot, SemrushBot, MJ12bot, DotBot, DataForSeoBot, Screaming Frog
otherssocial, other-se, script, empty-ua, browser, and own-server for the IPs you pass with -i

Lists loaded by ranges.sh, all answering HTTP 200 on 2026-11-08:

OperatorListsverified
Googlecommon-crawlers, special-crawlers, user-triggered-fetchers, user-triggered-fetchers-google, user-triggered-agentsYes
Bingbingbot.jsonYes
Appleapplebot.json (fallback: the whole 17.0.0.0/8 block)Yes
OpenAIgptbot.json, searchbot.json, chatgpt-user.jsonYes, per bot
Perplexityperplexitybot.json, perplexity-user.jsonYes, per bot
Anthropicbots.json (one list for ClaudeBot, Claude-User and Claude-SearchBot)Yes
Meta, Amazon, ByteDance, Common Crawl, Yandex, SEO toolsnone loadedEmpty, shown as "no IP list"

Common Crawl, DuckDuckGo, Mistral, OpenAI's AdsBot and Amazon (three pages) do publish ranges that this kit does not load yet. All of them, with entry counts, dates and a one-IP checker, are in Official crawler IP ranges.

The report

SectionAnswers
0Was a sitemap loaded?
1, 2Window, volume, traffic by class (s3xx includes 304)
3Requests that say "Google": verified against fake, per product
4, 5What verified Googlebot gets: 200, 304, redirects, 404, 5xx, and which URLs
6, 7Sitemap URLs Googlebot did not request in the window; its requests outside the sitemap
8Per crawler: useful requests against redirects, errors and parameter URLs
9Paths with many distinct parameter URLs (crawl traps)
10, 10bAI bots by name with verified counts; IPs that rotate three or more bot User-Agents
11Pages that assistants open on a user's request; the 404s they hit
12Visits that arrive with an AI assistant as the Referer
13404s that carry a Referer: internal (broken links) and external (lost links)
14, 15, 165xx by path; who requests robots.txt and sitemaps; response time if the log has it
17/.env and /.git/* answered with 200

From the synthetic sample (not real traffic), sections 3 and 17:

text
== 3. Google by product (verified = IP is inside an official Google range)
 bot                   hits  verified_hits  spoofed_hits  pct_spoofed  uniq_paths  pct_200
 Googlebot Smartphone   270            270             0            0          85       66
 Googlebot Desktop      184             66           118         64.1          41       71
== 17. Secret files answered with 200 (/.env, /.git/*) - urgent if any row appears
 p       hits  ips  min_bytes  max_bytes
 /.env      1    1       1043       1043

The report prints counts and URL paths, never an individual visitor IP. Which number is a problem, and why the thresholds sit where they do, is in the companion checklist Server log audit: what each number means and when to act.

Rather have this done for you?

Send the site address and what you want to know. We run the audit and send the report.

Send a request

Who it is not for

  • Sites on hosts that write no access logs, such as Vercel or Cloudflare Workers. There is nothing to read.
  • Anyone who needs live alerts. The kit is a batch report over a log window and does not watch the server.
  • Sites whose host keeps only a few days of logs and where nobody copies them. The short window makes section 6 mislead (see the next section).

Gotchas

  • Do not count bots by User-Agent. The header is free text. The verified column exists because of this.
  • Behind a CDN or proxy the log must hold the real client IP (nginx real_ip_header, Apache mod_remoteip). Otherwise every bot has the proxy's address and looks fake.
  • HTML served from a CDN cache never reaches your server log. On Cloudflare, check cf-cache-status on the homepage: DYNAMIC means the request reaches the origin.
  • ClickHouse's Regexp format matches the whole line. A field the pattern does not cover must end with .*, otherwise zero lines parse and nothing says why. run.sh catches this with its 95 % check.
  • Vendors move their lists. Google moved its range files to developers.google.com/static/crawling/ipranges/; in October 2026 the old address still answered 301. ranges.sh downloads with curl -L for that reason.
  • Some ranges sit in two lists. On 2026-11-08, six ranges appear in both OpenAI's GPTBot and OAI-SearchBot lists. A first-match lookup would mark real GPTBot requests as fake, so enrich.sql keeps every list that matches (ipsrcs).
  • CDNs such as Cloudflare can rate-limit quick repeated sitemap fetches. sitemap.sh pauses between requests and retries three times.
  • Short log windows mislead. Many hosts keep a few days of logs. Copy them weekly if you want a longer window.
  • On a Next.js site, section 13 gives false positives. Scanners send the site's own address as Referer. Check that the URL really appears in your HTML before fixing a "broken link".

Privacy and limits

Logs contain visitor IP addresses. Keep them off shared drives and out of chats, and delete the work folder when you finish. ranges.tsv is not shipped: it is downloaded when you first run the kit.

The kit reads combined logs only, out of the box. "Googlebot did not request this URL in the window" does not mean "this URL is not indexed". Meta, Amazon, ByteDance and Common Crawl have no IP list in the kit, so their numbers are by User-Agent only. The sitemap step follows an index one level down.

Tested on

2026-11-08: Ubuntu 24.04.3 LTS (WSL2), bash 5.2.21, curl 8.5.0, ClickHouse 26.10.1.2121. From an empty folder, with the files of this download only, the sample run took 3.9 s and 4.2 s in two runs, including the download of the IP ranges. A synthetic file of 977,000 lines (the sample repeated, 121 distinct IPs) ran in 2.5 s; logs with many distinct IPs take longer. Not tested on macOS. The ranges, URLs and bot lists are as of that date: verify at the operators' pages before relying on them.

Using it with an AI coding agent

Claude Code, Codex and similar tools can install and run the kit. Do not paste raw log lines into a chat; let the agent work in a terminal on your machine and share only report.txt. The prompt Prompt: have an AI agent set up ClickHouse and audit your server logs does this read-only, applies the thresholds and lists the fixes without applying them.

Files are provided as is: run them on your own data and check the result.

More files from the same audit

Want an audit like this on your site?

If you would rather have it done for you, send a request and describe the site.

Send a request
Download all (.zip)