path: root/webstats/workforrobots.org

#+title: Work for robots
#+PROPERTY: header-args:sqlite :db /scratch/titan/apache2/cgit-logs.sqlite :colnames yes
#+PROPERTY: header-args :exports both :cache yes :eval no-export
#+HTML_HEAD: <script src="http://127.0.0.1:8095/skewer"></script>
#+HTML_HEAD: <link rel="stylesheet" href="static/uPlot.min.css" />
#+HTML_HEAD_EXTRA: <script src="static/uPlot.iife.min.js"></script>
#+HTML_HEAD_EXTRA: <script src="plots.js"></script>


I self-host some of my git repositories to keep sovereignty and independence
from large Internet corporations. The public facing repositories are for
everybody, and today that means for robots. With the =AI-hype= on coding
assistants, I wanted to have a look at what are those AI companies taking. It is
worse than everything, it is idiotically everything. They can't recognize that
they are parsing git repositories and use the appropriate way of downloading
them.

#+begin_src sqlite :exports none
SELECT
    min(date),
    min(datetime (date, 'unixepoch')),
    min(datetime (date, 'unixepoch', 'localtime')),
    max(date),
    max(datetime (date, 'unixepoch')),
    max(datetime (date, 'unixepoch', 'localtime'))
FROM
    logs
#+end_src

#+RESULTS[8f773b86167f2d36db335568f344063f13838a11]:
|  min(date) | min(datetime (date, 'unixepoch')) | min(datetime (date, 'unixepoch', 'localtime')) |  max(date) | max(datetime (date, 'unixepoch')) | max(datetime (date, 'unixepoch', 'localtime')) |
|------------+-----------------------------------+------------------------------------------------+------------+-----------------------------------+------------------------------------------------|
| 1735686035 | 2024-12-31 23:00:35               | 2025-01-01 00:00:35                            | 1745109504 | 2025-04-20 00:38:24               | 2025-04-20 02:38:24                            |
* Who is visiting
I analyzed the =Apache= log files of my =cgit= service in the period from
=2025-01-01= till =2025-04-20=. Table [[top-users]] shows the top /users/ of public
facing git repository. The leading AI companies =OpenAI= and =Anthropic= with
their respective bots =GPTBot= and =ClaudeBot= simply dominate. I found it
unbelievable that they could extract about =≈7GiB= of data each. That is a lot
of Bandwidth out of my server for a few git repositories and in a lightweight
web interphase.

#+begin_src sqlite :exports results
--SELECT
    --count(*) AS Requests,
    --round(total (length) / 1024 / 1024, 1) "Tx MiB",
    ----0,
    --'Everybody else' AS "User Agent"
--FROM
    --logs
--WHERE
    --agentid NOT IN (143, 1, 19, 6, 4602, 3, 2, 4, 10306, 9)
    --AND path NOT LIKE '/ingrid/%'
--UNION
SELECT
    count(*) AS Requests,
    round(total (length) / 1024 / 1024, 1) "Tx MiB",
    --agentid,
    user_agent AS "User Agent"
FROM
    logs
    JOIN agent ON agent.id = logs.agentid
WHERE
    path NOT LIKE '/ingrid/%'
GROUP BY
    agentid
ORDER BY
    2 DESC
LIMIT 10
#+end_src

#+name: top-users
#+caption: Top 10 /users/ as self-identified by their /User Agent/ to the server
#+RESULTS[36d7b647efa39c3af86581279748a2bb53d034f3]:
| Requests | Tx MiB | User Agent                                                                                                                                                            |
|----------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|  3572480 | 8819.6 | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; GPTBot/1.2; +https://openai.com/gptbot)                                                                |
|  1617262 | 6766.3 | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)                                                               |
|   273968 |  721.4 | Mozilla/5.0 (compatible; Barkrowler/0.9; +https://babbar.tech/crawler)                                                                                                |
|    80159 |  498.3 | Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/109.0.0.0 Safari/537.36                                                 |
|   207771 |  475.8 | Scrapy/2.11.2 (+https://scrapy.org)                                                                                                                                   |
|    69697 |  466.1 | Mozilla/5.0 (Linux; Android 7.0;) AppleWebKit/537.36 (KHTML, like Gecko) Mobile Safari/537.36 (compatible; PetalBot;+https://webmaster.petalsearch.com/site/petalbot) |
|    59832 |  416.4 | Mozilla/5.0 (compatible; AhrefsBot/7.0; +http://ahrefs.com/robot/)                                                                                                    |
|    14142 |   83.3 | Mozilla/5.0 (Linux; Android 5.0) AppleWebKit/537.36 (KHTML, like Gecko) Mobile Safari/537.36 (compatible; Bytespider; spider-feedback@bytedance.com)                  |
|     2500 |   53.7 | Mozilla/5.0 (compatible; SeekportBot; +https://bot.seekport.com)                                                                                                      |
|     3578 |   30.9 | Mozilla/5.0 (Linux; Android 6.0.1; Nexus 5X Build/MMB29P) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/135.0.7049.52 Mobile Safari/537.36 (compatible; GoogleOther)  |

That is the total. What does it look like as a function of time? Figure
[[fig:agent-traffic]] shows the load on CGit frontend service by each visiting
agent. Hover over the plot to read the exact value for each agent at a given
time on the legend. You can highlight a specific curve by hovering over it or
its legend.

#+begin_src sqlite :results value file :file top_agent_traffic.csv :exports none
SELECT
    date / 14400 * 14400 AS time, -- 4h bin
    count(*) AS requests,
    total (length) FILTER (WHERE agentid = 143) AS "OpenAI-GPTBot",
    total (length) FILTER (WHERE agentid = 1) AS "ClaudeBot",
    total (length) FILTER (WHERE agentid = 19) AS "Barkrowler",
    total (length) FILTER (WHERE agentid = 6) AS "Macintosh",
    total (length) FILTER (WHERE agentid = 4602) AS "Scrapy",
    total (length) FILTER (WHERE agentid = 3) AS "PetalBot",
    total (length) FILTER (WHERE agentid = 2) AS "AhrefsBot",
    total (length) FILTER (WHERE agentid = 4) AS "Bytespider",
    total (length) FILTER (WHERE agentid = 10306) AS "SeekportBot",
    total (length) FILTER (WHERE agentid = 9) AS "Google",
    total (length) FILTER (WHERE agentid not in (143, 1, 19, 6, 4602, 3, 2, 4, 10306, 9)) AS "Rest"
FROM
    logs
WHERE
    path NOT LIKE '/ingrid/%'
    AND date NOT NULL
GROUP BY
    time
#+end_src

#+RESULTS[87f78b2de4d43785f81e682925c6b6542d794883]:
[[file:top_agent_traffic.csv]]

#+attr_html: :id agent-traffic
#+CAPTION: Load on CGit frontend service by each visiting agent. The first black dashed line shows the total request at the server and uses the right axis scale. All other solid-filled lines, use the left axis and represent the bandwidth usage.
#+NAME: fig:agent-traffic
[[./jsfill.png]]

You can see how aggressively the =ClaudeBot= scrapes pages, consuming a lot of
bandwidth is a /short/ time. =OpenAI-GPTBot= works show its rate-limitation, and
performs its scraping over a /longer/ period of time. However, as seen in table
[[top-users]], it performs more than twice the amount of request consumes =30%= more
bandwidth.

The rest of the visitors are bots too. =Barkrowler= is a regular visitor
gathering metrics for online marketing. =AhrefsBot= is of the same type, yet
started crawling in March. =Macintosh= is certainly a bot hiding itself as a
browser and constantly probing. =Scrappy= also unknown, came at the start of the
year and never came back.

=PetalBot= is for a search engine with AI recommendation by =Huawei=, it lingers
and slowly scrapes everything. =Seekport= is a search engine, it came all of a
sudden, took as much as it found useful, =<1%= of what the big AI bot take, and
swiftly left again.

=Bytespider= is almost background noise, but it is also to train an LLM, this
time for ByteDance, the Chinese owner of TikTok.

The last one =Google= doesn't even seem to be the bot for indexing its search
engine, but rather one to test its chrome browser and how it renders pages.

=Rest= is all the remaining /robots/ or /users/. The have consumed around
=≈400MiB=, placing them in aggregate in a behavior like =Macintosh, Scrapy,
PetalBot & AhrefsBot=.

* How should they visit?

This is a collection of =git= repositories. Yet, you can browse some of my code,
some files, that is it. Yet, when you want *everything*, the correct use of this
service is through the =git= client, downloading my publicly available software.

That makes the data a lot more useful, even for those AI companies as the data
cleanup would be easier. They, themselves should use their AI to recognize what
kind of page they are vising and act accordingly instead of stupidly scraping
everything.

How have the good citizens behaved? That is on table [[git-users]]. The =Software
Heritage= keeps mirrors of git repositories. It thus watches for updates and the
downloads. There are other people besides them that downloaded, but in total, in
the same time they only downloaded =≈21MiB=. That is =0.3%= compared to
=ClaudeBot=.

#+begin_src sqlite :exports results
SELECT
    --agent.id,
    user_agent as "User Agent",
    count(*) AS hits,
    round(total (length) / 1024, 1) AS "tx KiB"
FROM
    logs
    JOIN agent ON logs.agentid = agent.id
WHERE
    user_agent LIKE '%git%'
    and id != 10897
GROUP BY
    user_agent
order by total(length) desc
#+end_src

#+name: git-users
#+caption: Git users
#+RESULTS[333fcbc738819c497f14b4445a3b45f391f0db7e]:
| User Agent                                                                       | hits |  tx KiB |
|----------------------------------------------------------------------------------+------+---------|
| git/2.40.3                                                                       | 1075 | 12821.3 |
| git/2.34.1                                                                       | 1149 |  3687.1 |
| Software Heritage dumb Git loader                                                |  337 |  2533.6 |
| git/2.48.1                                                                       |  115 |  1908.6 |
| Software Heritage cgit lister v6.9.3 (+https://www.softwareheritage.org/contact) |    8 |    21.3 |
| Software Heritage cgit lister v6.9.2 (+https://www.softwareheritage.org/contact) |    8 |    21.0 |
| git/dulwich/0.22.7                                                               |    2 |     8.5 |
| git/dulwich/0.22.6                                                               |    1 |     4.2 |
|----------------------------------------------------------------------------------+------+---------|
| Total                                                                            | 2695 | 21005.6 |

* What are they looking at?
The web front end of git repositories of course, but it there a pattern?

Table [[status-codes]] show the status codes of all requests performed by the users.
The failure rate of =OpenAI= is alarming. It from its =3.5 million= requests
=15%= are client errors, mostly not found pages of error =404=. What is their
scraper doing so wrong? =ClaudeBot= as noted earlier, manages to scrape with
half the requests and a failure rate of =1.6%=.

=Everybody else= are the =Rest=, all users that can't be grouped into the major
users. Yet they have a failure rate of =25%=, but that is normal as they are
mostly hacker robots scanning for vulnerabilities.

#+begin_src sqlite :exports results
SELECT
    CASE WHEN agentid = 143 THEN
        'OpenAI-GPTBot'
    WHEN agentid = 1 THEN
        'ClaudeBot'
    WHEN agentid = 19 THEN
        'Barkrowler'
    WHEN agentid = 6 THEN
        'Macintosh'
    WHEN agentid = 4602 THEN
        'Scrapy'
    WHEN agentid = 3 THEN
        'PetalBot'
    WHEN agentid = 2 THEN
        'AhrefsBot'
    WHEN agentid = 4 THEN
        'Bytespider'
    WHEN agentid = 10306 THEN
        'SeekportBot'
    WHEN agentid = 9 THEN
        'Google'
    ELSE
        'Everybody else'
    END AS Agent,
    --count(*) AS "Requests",
    count(*) FILTER (WHERE status BETWEEN 200 AND 299) AS "2XX",
    count(*) FILTER (WHERE status BETWEEN 300 AND 399) AS "3XX",
    count(*) FILTER (WHERE status BETWEEN 400 AND 499) AS "4XX",
    --(100.0 * count(*) FILTER (WHERE status BETWEEN 400 AND 499)) / count(*) AS "fails 4XX",
    count(*) FILTER (WHERE status BETWEEN 500 AND 599) AS "5XX"
FROM
    logs
GROUP BY
    Agent
ORDER BY
    "4XX" DESC
#+end_src

#+name: status-codes
#+caption: HTTP status codes per user agent
#+RESULTS[4939ff0e53fd1b72a7f39a19a3a50483893b07d5]:
| Agent          |     2XX | 3XX |    4XX |          fails 4XX | 5XX |
|----------------+---------+-----+--------+--------------------+-----|
| OpenAI-GPTBot  | 3017848 |   0 | 554511 |    15.521738400215 | 121 |
| Everybody else |   99066 | 467 |  34630 |   25.8091923354971 |  14 |
| ClaudeBot      | 1591179 |  26 |  25611 |   1.58360240950446 | 446 |
| Barkrowler     |  272343 |   0 |   1618 |  0.590579921742685 |   7 |
| Macintosh      |   79071 |   2 |   1086 |    1.3548073204506 |   0 |
| Bytespider     |   13609 |   0 |    531 |   3.75477301654646 |   2 |
| PetalBot       |   69223 |   0 |    473 |  0.678651878847009 |   1 |
| Scrapy         |  207240 |   0 |    348 |  0.167492094661911 | 183 |
| AhrefsBot      |   59733 |   0 |     90 |  0.150421179302046 |   9 |
| Google         |    3576 |   0 |      2 | 0.0558971492453885 |   0 |
| SeekportBot    |    2500 |   0 |      0 |                0.0 |   0 |

Let's have a look at the most not found pages. Listed in table [[fail-pages]] are
each of the page paths, how much bandwidth(=tx=) each consumed, and then the
request per bots. With one exception, all pages are placeholder from the
template engine. The repository =hugo-minimalist-theme= is a [[https://gohugo.io][Hugo]] theme. Within
the curly braces ={{ }}= the rendering engine replaces values. Certainly the
html parser reads them raw an from the link =a= tag and requests the page.
=ClaudeBot= seems to track error pages and not query them again. =OpenAI= is
incapable of doing that, and stubbornly tries over and over.

If you grep for the string /href="{{ .RelPermalink }}"/ over the entire git
history, you find it appears up to today =954= times. It is surprising how
=OpenAI= manage to request it =3= times that amount.

#+begin_src sqlite :exports results
SELECT
    replace(path, '|', '%7C') AS Page,
    round(total(length) / 1024 / 1024, 2) AS "tx MiB",
    count(*) FILTER (WHERE agentid = 143) AS "OpenAI",
    count(*) FILTER (WHERE agentid = 1) AS "ClaudeBot",
    count(*) FILTER (WHERE agentid NOT IN (1, 143)) AS "Rest"
    --substr(path, 0, 50)
FROM
    logs
WHERE
    path NOT LIKE '/ingrid/%'
    AND status = 404
GROUP BY
    path
ORDER BY
    2 DESC
LIMIT 10
#+end_src

#+name: fail-pages
#+caption: Top 10: =404= error not found pages.
#+attr_html: :class fail-pages
#+RESULTS[c7d6ef8ed6eac11d94e7e007c99399963237da56]:
| Page                                                                                 | tx MiB | OpenAI | ClaudeBot | Rest |
|--------------------------------------------------------------------------------------+--------+--------+-----------+------|
| /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20.RelPermalink%20%7D%7D        |   8.36 |   2805 |         3 |    7 |
| /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20.URL%20%7D%7D                 |   5.39 |   1629 |         1 |   13 |
| /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20.%20%7D%7D                    |   4.82 |   1559 |         1 |    4 |
| /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20$href%20%7D%7D                |   4.28 |   1209 |         4 |    5 |
| /.env                                                                                |   3.84 |      0 |         0 |  744 |
| /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20.Permalink%20%7D%7D           |   3.75 |   1060 |         2 |   15 |
| /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20$pag.Next.URL%20%7D%7D        |   3.36 |    916 |         1 |    7 |
| /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20$pag.Prev.URL%20%7D%7D        |   3.34 |    912 |         0 |    7 |
| /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20if%20ne%20.MediaType.SubType  |   2.95 |    817 |         1 |    0 |
| /hugo-minimalist-theme/plain/layouts/taxonomy/%7B%7B%20.Name%20%7C%20urlize%20%7D%7D |   2.86 |    745 |         5 |    0 |

#+begin_src sqlite :exports results
SELECT
    round(total(length) / 1024 / 1024, 2) AS "tx MiB",
    count(*),
    count(distinct agentid) agents,
    replace(path, '|', '%7C') AS path
    --substr(path, 0, 50)
FROM
    logs
WHERE
    path NOT LIKE '/ingrid/%'
    AND status = 404
    and agentid NOT IN (1, 143)
GROUP BY
    path
ORDER BY
    1 DESC
LIMIT 20
#+end_src

my cgit instance using nginx is not working correctly.
On the home(landing/starting) page all links work. Say I click on sepo "prime".
that leads to the path /prime. That is how is written on the html links.

Now that I'm in path '/prime' I want to go to the about page.
The generated link is '/prime/prime/about'. It is repeating the path, because "im on page /prime", the new pages should be relative "about", or absolute '/prime/about'. But it is concatenating '/prime', to where I want to go '/prime/about', leaving '/prime/prime/about'

Where do I fix this? What would the nginx config look like.
#+RESULTS[5d7adfe6034ad15603705e1501ba90fadc0a8f1c]:
| tx MiB | count(*) | agents | path                                                                                                     |
|--------+----------+--------+----------------------------------------------------------------------------------------------------------|
|   3.84 |      744 |    368 | /.env                                                                                                    |
|   2.26 |      409 |      1 | /cgi-bin/luci/;stok=/locale                                                                              |
|   2.02 |      381 |    182 | /.git/config                                                                                             |
|   1.08 |      195 |      1 | /actuator/gateway/routes                                                                                 |
|   0.88 |      173 |      2 | /hello.world?%ADd+allow_url_include%3d1+%ADd+auto_prepend_file%3dphp://input                             |
|   0.65 |      119 |      6 | /sitemap.xml                                                                                             |
|   0.57 |      222 |     12 | /vendor/phpunit/phpunit/src/Util/PHP/eval-stdin.php                                                      |
|   0.55 |       72 |     10 | /dotfiles/tree/config/doom/snippets/org-mode/daily?h=semgrep&id=0b30f0bf0d1697504e05630b387b8e32302c3f7c |
|   0.54 |       71 |     10 | /dotfiles/tree/config/doom/snippets/org-mode/daily?h=semgrep&id=5b57ec9f30c80a391a1f7ac5c3a69a91658e2c53 |
|   0.47 |      102 |     10 | /config.json                                                                                             |
|   0.45 |      119 |     15 | /api/.env                                                                                                |
|   0.42 |      103 |     55 | /_profiler/phpinfo                                                                                       |
|   0.39 |       87 |     10 | /.env.prod                                                                                               |
|   0.37 |       93 |     11 | /.env.save                                                                                               |
|   0.37 |       92 |      9 | /.env.production                                                                                         |
|   0.35 |       64 |      3 | /src/.git/config                                                                                         |
|   0.35 |       63 |      1 | /server/.git/config                                                                                      |
|   0.35 |       63 |      2 | /media/.git/config                                                                                       |
|   0.35 |       63 |      3 | /cms/.git/config                                                                                         |
|   0.35 |       68 |      1 | /95.111.247.99/.env                                                                                      |


#+begin_src sqlite :results value file :file fails.csv
SELECT
    count(*) as hits,
    sum(length),
    path
    --substr(path, 0, 50)
FROM
    logs
WHERE
    path NOT LIKE '/ingrid/%'
    AND status = 404
GROUP BY
    path
ORDER BY
       hits desc, path
limit 50

#+end_src

#+RESULTS[2972512bc0b63468a3eba78fde5efcce703e5d56]:
[[file:fails.csv]]

#+begin_src sqlite
select count(*), sum(length), path,user_agent from logs
join agent on agentid=id
WHERE
    path NOT LIKE '/ingrid/%'
    AND status = 404
group by path, agentid
ORDER BY
       sum(count(*)) over (partition by path) desc, 1 desc, path, agentid
limit 50
#+end_src

#+RESULTS[cb18bd88215d0873626e54f3a56ea4b6851c4138]:
| count(*) | sum(length) | path                                                                                | user_agent                                                                                                                                                                  |                                                                                                         |
|----------+-------------+-------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------|
|     2805 |     8732358 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20.RelPermalink%20%7D%7D       | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; GPTBot/1.2; +https://openai.com/gptbot)                                                                      |                                                                                                         |
|        7 |       18923 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20.RelPermalink%20%7D%7D       | Mozilla/5.0 (compatible; ImagesiftBot; +imagesift.com)                                                                                                                      |                                                                                                         |
|        3 |       16958 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20.RelPermalink%20%7D%7D       | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)                                                                     |                                                                                                         |
|     1629 |     5597913 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20.URL%20%7D%7D                | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; GPTBot/1.2; +https://openai.com/gptbot)                                                                      |                                                                                                         |
|        7 |       25024 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20.URL%20%7D%7D                | Mozilla/5.0 (compatible; ImagesiftBot; +imagesift.com)                                                                                                                      |                                                                                                         |
|        3 |        8467 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20.URL%20%7D%7D                | AliyunSecBot/Aliyun (AliyunSecBot@service.alibaba.com)                                                                                                                      |                                                                                                         |
|        1 |        2655 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20.URL%20%7D%7D                | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)                                                                     |                                                                                                         |
|        1 |        6609 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20.URL%20%7D%7D                | Mozilla/5.0 (Macintosh; PPC Mac OS X 10_7_9 rv:3.0; mt-MT) AppleWebKit/532.12.1 (KHTML, like Gecko) Version/5.0.3 Safari/532.12.1                                           |                                                                                                         |
|        1 |        6609 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20.URL%20%7D%7D                | Mozilla/5.0 (X11; Linux i686; rv:1.9.6.20) Gecko/8855-02-04 23:24:49.455231 Firefox/3.6.10                                                                                  |                                                                                                         |
|        1 |        6543 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20.URL%20%7D%7D                | Opera/9.30.(X11; Linux x86_64; tn-ZA) Presto/2.9.175 Version/11.00                                                                                                          |                                                                                                         |
|     1559 |     5018667 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20.%20%7D%7D                   | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; GPTBot/1.2; +https://openai.com/gptbot)                                                                      |                                                                                                         |
|        4 |       23869 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20.%20%7D%7D                   | Mozilla/5.0 (compatible; ImagesiftBot; +imagesift.com)                                                                                                                      |                                                                                                         |
|        1 |        7021 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20.%20%7D%7D                   | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)                                                                     |                                                                                                         |
|     1209 |     4437078 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20$href%20%7D%7D               | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; GPTBot/1.2; +https://openai.com/gptbot)                                                                      |                                                                                                         |
|        5 |       27298 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20$href%20%7D%7D               | Mozilla/5.0 (compatible; ImagesiftBot; +imagesift.com)                                                                                                                      |                                                                                                         |
|        4 |       23683 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20$href%20%7D%7D               | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)                                                                     |                                                                                                         |
|     1060 |     3867072 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20.Permalink%20%7D%7D          | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; GPTBot/1.2; +https://openai.com/gptbot)                                                                      |                                                                                                         |
|       12 |       41170 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20.Permalink%20%7D%7D          | Mozilla/5.0 (compatible; ImagesiftBot; +imagesift.com)                                                                                                                      |                                                                                                         |
|        3 |        8087 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20.Permalink%20%7D%7D          | AliyunSecBot/Aliyun (AliyunSecBot@service.alibaba.com)                                                                                                                      |                                                                                                         |
|        2 |       14042 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20.Permalink%20%7D%7D          | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)                                                                     |                                                                                                         |
|      916 |     3490491 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20$pag.Next.URL%20%7D%7D       | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; GPTBot/1.2; +https://openai.com/gptbot)                                                                      |                                                                                                         |
|        7 |       29355 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20$pag.Next.URL%20%7D%7D       | Mozilla/5.0 (compatible; ImagesiftBot; +imagesift.com)                                                                                                                      |                                                                                                         |
|        1 |        2655 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20$pag.Next.URL%20%7D%7D       | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)                                                                     |                                                                                                         |
|      912 |     3471446 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20$pag.Prev.URL%20%7D%7D       | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; GPTBot/1.2; +https://openai.com/gptbot)                                                                      |                                                                                                         |
|        7 |       29395 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20$pag.Prev.URL%20%7D%7D       | Mozilla/5.0 (compatible; ImagesiftBot; +imagesift.com)                                                                                                                      |                                                                                                         |
|      817 |     3081643 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20if%20ne%20.MediaType.SubType | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; GPTBot/1.2; +https://openai.com/gptbot)                                                                      |                                                                                                         |
|        1 |        6981 | /hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20if%20ne%20.MediaType.SubType | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)                                                                     |                                                                                                         |
|      798 |     2487903 | /hugo-minimalist-theme/plain/layouts/%7B%7B%20.Type%20%7D%7D                        | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; GPTBot/1.2; +https://openai.com/gptbot)                                                                      |                                                                                                         |
|        5 |       26298 | /hugo-minimalist-theme/plain/layouts/%7B%7B%20.Type%20%7D%7D                        | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)                                                                     |                                                                                                         |
|        4 |       16227 | /hugo-minimalist-theme/plain/layouts/%7B%7B%20.Type%20%7D%7D                        | Mozilla/5.0 (compatible; ImagesiftBot; +imagesift.com)                                                                                                                      |                                                                                                         |
|      745 |     2977378 | /hugo-minimalist-theme/plain/layouts/taxonomy/%7B%7B%20.Name%20                     | %20urlize%20%7D%7D                                                                                                                                                          | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; GPTBot/1.2; +https://openai.com/gptbot)  |
|        5 |       22642 | /hugo-minimalist-theme/plain/layouts/taxonomy/%7B%7B%20.Name%20                     | %20urlize%20%7D%7D                                                                                                                                                          | Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com) |
|       65 |      348406 | /.env                                                                               | Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:77.0) Gecko/20100101 Firefox/77.0                                                                                          |                                                                                                         |
|       63 |      310359 | /.env                                                                               | Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.129 Safari/537.36                                                                   |                                                                                                         |
|       58 |      310754 | /.env                                                                               | Mozilla/5.0 (Linux; U; Android 4.4.2; en-US; HM NOTE 1W Build/KOT49H) AppleWebKit/534.30 (KHTML, like Gecko) Version/4.0 UCBrowser/11.0.5.850 U3/0.8.0 Mobile Safari/534.30 |                                                                                                         |
|       52 |      301380 | /.env                                                                               | l9explore/1.2.2                                                                                                                                                             |                                                                                                         |
|       22 |      111334 | /.env                                                                               | Go-http-client/1.1                                                                                                                                                          |                                                                                                         |
|       18 |       38352 | /.env                                                                               | -                                                                                                                                                                           |                                                                                                         |
|       16 |       85808 | /.env                                                                               | python-requests/2.32.3                                                                                                                                                      |                                                                                                         |
|       16 |       36940 | /.env                                                                               | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36                                                         |                                                                                                         |
|        9 |       52245 | /.env                                                                               | Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:103.0) Gecko/20100101 Firefox/103.0 abuse.xmco.fr                                                                          |                                                                                                         |
|        9 |       48190 | /.env                                                                               | Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10_6_8; en-us) AppleWebKit/534.50 (KHTML, like Gecko) Version/5.1 Safari/534.50                                                   |                                                                                                         |
|        8 |       42840 | /.env                                                                               | python-requests/2.26.0                                                                                                                                                      |                                                                                                         |
|        6 |       32086 | /.env                                                                               | python-requests/2.31.0                                                                                                                                                      |                                                                                                         |
|        5 |       26773 | /.env                                                                               | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.3                                                          |                                                                                                         |
|        3 |        8719 | /.env                                                                               | Mozilla/5.0 (iPhone; CPU iPhone OS 17_5_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/17.4.1 Mobile/15E148 Safari/604.1                                 |                                                                                                         |
|        3 |       16043 | /.env                                                                               | python-httpx/0.28.1                                                                                                                                                         |                                                                                                         |
|        3 |       17375 | /.env                                                                               | Mozilla/5.0 (Linux; Android 9; Redmi Note 7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/76.0.3809.89 Mobile Safari/537.36                                                |                                                                                                         |
|        3 |       17375 | /.env                                                                               | Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36 SE 2.X MetaSr 1.0                                             |                                                                                                         |
|        3 |       16139 | /.env                                                                               | python-requests/2.27.1                                                                                                                                                      |                                                                                                         |

#+begin_src sqlite :results value file :file leadfail.csv
SELECT
    datetime (date, 'unixepoch'),
    status,
    count(*) as hits,
    round(sum(length) / 1024, 2) as KB,
    user_agent
FROM
    logs
    JOIN agent ON agent.id = logs.agentid
WHERE
    path = '/hugo-minimalist-theme/plain/layouts/partials/%7B%7B%20.RelPermalink%20%7D%7D'
group by agentid
ORDER BY
    referrer
#+end_src

#+RESULTS[e3501152f1ec3e7ff822a6713736ff35fb0dcb46]:
[[file:leadfail.csv]]

#+begin_src sqlite
select
    datetime (date, 'unixepoch'),
    agentid,
    count(*),
    path
from logs
where path like '/hello.world?%'
group by agentid
#+end_src

#+RESULTS[2f837a0274a09a9c00545946c8992d57286667ff]:
| datetime (date, 'unixepoch') | agentid | count(*) | path                                                                         |
|------------------------------+---------+----------+------------------------------------------------------------------------------|
| 2025-04-12 23:49:39          |      12 |      167 | /hello.world?%ADd+allow_url_include%3d1+%ADd+auto_prepend_file%3dphp://input |
| 2025-02-08 23:48:46          |     238 |        6 | /hello.world?%ADd+allow_url_include%3d1+%ADd+auto_prepend_file%3dphp://input |

#+begin_src sqlite
select * from agent where id in (12, 238)
#+end_src

#+RESULTS[6d8a93a2351c1668ba5a7b975fdbbc6271cace36]:
|  id | user_agent                                                            |
|-----+-----------------------------------------------------------------------|
|  12 | Custom-AsyncHttpClient                                                |
| 238 | Mozilla/5.0 (Linux; Linux x86_64; en-US) Gecko/20100101 Firefox/122.0 |

#+begin_src sqlite
SELECT
    round(total (length) / 1024, 2) "tx KiB",
    count(*) hits,
    agentid,
    user_agent
FROM
    logs
    JOIN agent ON agentid = id
    --where agentid in (
    --select id from agent
WHERE
    user_agent LIKE '%google%'
    --)
GROUP BY
    id
ORDER BY
    1 DESC
#+end_src


#+begin_src sqlite
SELECT
    count(*),
    count(distinct ipid),
    count(distinct agentid),
    total (length),
    request_method,
    -- status,
    substr(path, 0, 50)
FROM
    logs
WHERE
    path NOT LIKE '/ingrid/%' AND
    status = 200
GROUP BY
    path
ORDER BY
    1 DESC
LIMIT 15
#+end_src

#+RESULTS[8469c661009196d66d76364873866923b89c8205]:
| count(*) | count(distinct ipid) | count(distinct agentid) | total (length) | request_method | substr(path, 0, 50)             |
|----------+----------------------+-------------------------+----------------+----------------+---------------------------------|
|     3540 |                 1779 |                     425 |     22903047.0 | GET            | /                               |
|     2171 |                 1563 |                      48 |      9744532.0 | GET            | /robots.txt                     |
|      855 |                  518 |                      50 |      4234481.0 | GET            | /favicon.ico                    |
|      334 |                  262 |                      31 |      1304042.0 | GET            | /cgit.css                       |
|      150 |                  116 |                      59 |      1030405.0 | GET            | /hugo-minimalist-theme/about/   |
|      144 |                    1 |                       1 |       854165.0 | GET            | /?XDEBUG_SESSION_START=phpstorm |
|      119 |                   69 |                      52 |       421470.0 | GET            | /cgit.png                       |
|       98 |                   36 |                      14 |       330051.0 | GET            | /hugo-minimalist-theme/         |
|       46 |                   31 |                      15 |       165446.0 | GET            | /homepage/                      |
|       42 |                   32 |                      13 |       157112.0 | GET            | /dotfiles/                      |
|       39 |                   34 |                      14 |       255204.0 | GET            | /hugo-minimalist-theme/log/     |
|       37 |                   30 |                      13 |       132913.0 | GET            | /hugo-minimalist-theme/tree/    |
|       37 |                   33 |                      11 |       103541.0 | GET            | /?s=name                        |
|       36 |                   33 |                      12 |       242095.0 | GET            | /dotfiles/log/                  |
|       35 |                   32 |                      14 |       124829.0 | GET            | /dotfiles/tree/                 |