Preview
Background
{"level":"error","ts":1753604356.9313068,"logger":"http.log.error","msg":"dial tcp: lookup remotely_app on 127.0.0.11:53: no such host","request":{"remote_ip":"49.73.126.48","remote_port":"32192","client_ip":"49.73.126.48","proto":"HTTP/2.0","method":"POST","host":"remotely.aiursoft.cn","uri":"/hubs/service/negotiate?negotiateVersion=1","headers":{"User-Agent":["Microsoft SignalR/8.0 (8.0.2+da7e9894ce22ef8cc02e5acc56e95a6f8cf8f644; Windows NT; .NET; .NET 8.0.2)"],"X-Requested-With":["XMLHttpRequest"],"Content-Length":["0"]},"tls":{"resumed":true,"version":771,"cipher_suite":49195,"proto":"h2","server_name":"remotely.aiursoft.cn"}},"duration":0.003472111,"status":502,"err_id":"sz0r0viky","err_trace":"reverseproxy.statusError (reverseproxy.go:1390)"}
{"level":"error","ts":1753604356.9313471,"logger":"http.log.access","msg":"handled request","request":{"remote_ip":"49.73.126.48","remote_port":"32192","client_ip":"49.73.126.48","proto":"HTTP/2.0","method":"POST","host":"remotely.aiursoft.cn","uri":"/hubs/service/negotiate?negotiateVersion=1","headers":{"X-Requested-With":["XMLHttpRequest"],"Content-Length":["0"],"User-Agent":["Microsoft SignalR/8.0 (8.0.2+da7e9894ce22ef8cc02e5acc56e95a6f8cf8f644; Windows NT; .NET; .NET 8.0.2)"]},"tls":{"resumed":true,"version":771,"cipher_suite":49195,"proto":"h2","server_name":"remotely.aiursoft.cn"}},"bytes_read":0,"user_id":"","duration":0.003472111,"size":0,"status":502,"resp_headers":{"Server":["Caddy"],"Alt-Svc":["h3=\":443\"; ma=2592000"],"Strict-Transport-Security":["max-age=63072000"]}}
Above is the log generated by my Caddy.
Recently, I've been paying close attention to certain data points, such as:
- Which IP address has the highest request volume? X-axis: time, Y-axis: request count, color: IP.
- Which application is the most popular? X-axis: time, Y-axis: request count, color: application.
- For a specific IP, which applications is it requesting? X-axis: time, Y-axis: request count, color: application.
- For a specific application, which IPs are requesting it? X-axis: time, Y-axis: request count.
- Which paths of an application are the slowest and which are the fastest? Sort and display response times? Show changes over time?
- How are error codes distributed for an application? Show changes over time?
- For a specific error code of an application, which paths are involved? Show changes over time?
- Which application has the highest error rate? Show changes over time?
Currently, I need to visualize this data into charts and apply filters conveniently. I tried Grafana, but I feel it is not suitable: Grafana can only display numerical data, not string values like paths. It is more like monitoring a sensor that only generates numerical values over time.
What I want is something similar to a time-series database, where each row represents a single request. At the same time, I need to quickly add filters, group by, order by, etc., to generate charts, similar to Microsoft Kusto or Power BI.
I need an open-source solution. Currently, the logs are in stdout. It must be self-hostable and support Docker.
Ultimately, after investigation, I decided to use ClickHouse + Superset to visualize my interface.
Plan
I think the upcoming work will likely require several steps. But primarily, it comes down to four:
- Ensure Caddy's logs can be output to a file or stdout, and are properly formatted. JSON is preferred.
- Set up a process to consume Caddy's logs and store them in real time in a ClickHouse DB.
- Use a graphical viewer connected to the ClickHouse DB to start analyzing the data, ensuring it is clean, usable, and valuable.
- Build a web portal that visualizes the analysis results into web-based charts to present to IT personnel.
I believe this plan is very comprehensive, with no likely issues remaining. Ready to start implementation!
In detail, I may need Python for collection. Then create tables in ClickHouse. Finally, use Superset for visualization.
Obtain the Standard Log Stream
For the first step, here is what I did: I compiled Caddy myself, then added a few plugins to ensure it could output logs in the format I needed.
I also added support for brotli compression, transform-encoder, and rate limiting.
# ============================
# Prepare caddy Environment
FROM caddy:builder AS caddy-build-env
RUN xcaddy build \
--with github.com/ueffel/caddy-brotli \
--with github.com/caddyserver/transform-encoder \
--with github.com/mholt/caddy-ratelimit
# ============================
# Prepare Caddyfile build Environment
FROM localhost:8080/box_starting/local_ubuntu AS config-build-env
WORKDIR /app
COPY . .
# Outputs to /app/Dist/Caddyfile
RUN chmod +x /app/build_proxy.sh
RUN /bin/bash /app/build_proxy.sh
# ============================
# Prepare Runtime Environment
FROM caddy
WORKDIR /app
EXPOSE 80 443
COPY --from=caddy-build-env /usr/bin/caddy /usr/bin/caddy
COPY --from=config-build-env /app/Dist/Caddyfile /etc/caddy/Caddyfile
# Check if any fatal errors in caddyfile
RUN caddy validate --config /etc/caddy/Caddyfile --adapter caddyfile && \
mkdir -p /var/log/caddy && \
touch /data/caddy/logs/web.log
ENTRYPOINT ["sh", "-c", "caddy run --config /etc/caddy/Caddyfile --adapter caddyfile & tail -f /data/caddy/logs/web.log & wait"]
I use the Dockerfile mentioned above to compile caddy.
And at the beginning of the caddyfile, I added:
{
email [email protected]
log {
format json
# 注意，日志写到了：/data/caddy/logs/web.log
# /data 目录是容器的一个 VOLUME， 会穿透到主机。
output file /data/caddy/logs/web.log {
roll_size 1gb
roll_uncompressed
}
level debug
}
servers :443 {
listener_wrappers {
proxy_protocol {
# 注意：这里的 proxy_protocol 需要开启，才能支持 FRP 的代理。
timeout 2s
allow 10.233.0.0/16
}
http_redirect
tls
}
}
}
to ensure that it outputs logs.
Then I used the following docker-compose to start my caddy:
version: '3.9'
services:
sites:
image: localhost:8080/box_starting/local_sites
ports:
# These ports are for internal use. For external, FRP will handle it.
- target: 80
published: 80
protocol: tcp
mode: host
- target: 443
published: 443
protocol: tcp
mode: host
networks:
- frp_net
- proxy_app
volumes:
# 注意：Sites 容器里的 /data 目录存着 log。
- sites-data:/data
stop_grace_period: 60s
deploy:
resources:
limits:
cpus: '8.0'
memory: 16G
update_config:
order: stop-first
delay: 60s
volumes:
sites-data:
driver: local
driver_opts:
type: none
o: bind
device: /swarm-vol/sites-data
networks:
proxy_app:
external: true
Now, I have confirmed that the logs are normal:
anduin@proart:/swarm-vol/Box$ sudo docker ps | grep sites
bd9f24b9c79f localhost:8080/box_starting/local_sites:latest "sh -c 'caddy run --…" 5 minutes ago Up 5 minutes 0.0.0.0:80->80/tcp, [::]:80->80/tcp, 0.0.0.0:443->443/tcp, [::]:443->443/tcp, 443/udp, 2019/tcp incoming_sites.1.mai5g7xgxugy5p4prfwk4ohey
anduin@proart:/swarm-vol/Box$ sudo docker logs -f bd9f24b9c79f
{"level":"debug","ts":1753604514.283448,"logger":"tls.handshake","msg":"matched certificate in cache","remote_ip":"49.13.130.29","remote_port":"3132","subjects":["auth.aiursoft.cn"],"managed":true,"expiration":1759912228,"hash":"35212a5b8ae720958f58e378d367bba7c7f2fb75451851a14eec9cb96b54542e"}
{"level":"debug","ts":1753604514.3975098,"logger":"http.handlers.reverse_proxy","msg":"selected upstream","dial":"stathub_stathubserver:5000","total_upstreams":1}
{"level":"debug","ts":1753604514.3991885,"logger":"http.handlers.reverse_proxy","msg":"upstream roundtrip","upstream":"stathub_stathubserver:5000","duration":0.001577048,"request":{"remote_ip":"119.29.247.197","remote_port":"57854","client_ip":"119.29.247.197","proto":"HTTP/1.1","method":"POST","host":"stathub.aiursoft.cn","uri":"/api/metrics","headers":{"X-Forwarded-Host":["stathub.aiursoft.cn"],"Via":["1.1 Caddy"],"Accept":["application/json"],"Content-Type":["application/json; charset=utf-8"],"Content-Length":["2604"],"User-Agent":[""],"X-Forwarded-For":["119.29.247.197"],"X-Forwarded-Proto":["https"]},"tls":{"resumed":false,"version":772,"cipher_suite":4865,"proto":"","server_name":"stathub.aiursoft.cn"}},"headers":{"X-Frame-Options":["SAMEORIGIN"],"Content-Security-Policy":["frame-ancestors 'self'"],"Content-Type":["application/json; charset=utf-8"],"Date":["Sun, 27 Jul 2025 08:21:53 GMT"],"Server":["Kestrel"],"Vary":["Accept-Encoding"]},"status":201}
{"level":"info","ts":1753604514.3996112,"logger":"http.log.access","msg":"handled request","request":{"remote_ip":"119.29.247.197","remote_port":"57854","client_ip":"119.29.247.197","proto":"HTTP/1.1","method":"POST","host":"stathub.aiursoft.cn","uri":"/api/metrics","headers":{"Accept":["application/json"],"Content-Type":["application/json; charset=utf-8"],"Content-Length":["2604"]},"tls":{"resumed":false,"version":772,"cipher_suite":4865,"proto":"","server_name":"stathub.aiursoft.cn"}},"bytes_read":2604,"user_id":"","duration":0.002170407,"size":56,"status":201,"resp_headers":{"Alt-Svc":["h3=\":443\"; ma=2592000"],"Vary":["Accept-Encoding"],"Content-Security-Policy":["frame-ancestors 'self'"],"Date":["Sun, 27 Jul 2025 08:21:53 GMT"],"Server":["Kestrel"],"Via":["1.1 Caddy"],"Strict-Transport-Security":["max-age=63072000"],"X-Frame-Options":["SAMEORIGIN"],"Content-Type":["application/json; charset=utf-8"]}}
{"level":"debug","ts":1753604514.6455557,"logger":"http.handlers.reverse_proxy","msg":"selected upstream","dial":"authentik_server:9000","total_upstreams":1}
{"level":"debug","ts":1753604514.672455,"logger":"http.handlers.reverse_proxy","msg":"upstream roundtrip","upstream":"authentik_server:9000","duration":0.026780181,"request":{"remote_ip":"49.13.130.29","remote_port":"3132","client_ip":"49.13.130.29","proto":"HTTP/1.1","method":"HEAD","host":"auth.aiursoft.cn","uri":"/application/o/authorize/?client_id=wzt4srMcPxcNgaQh70XZtOoBCAlmTgV6IjeRAyUj&response_type=code&scope=email+nextcloud+openid&redirect_uri=https%3A%2F%2Fnextcloud.aiursoft.cn%2Findex.php%2Fapps%2Fuser_oidc%2Fcode&claims=%7B%22id_token%22%3A%7B%22email%22%3Anull%2C%22name%22%3Anull%2C%22quota%22%3Anull%2C%22groups%22%3Anull%2C%22user_id%22%3A%7B%22essential%22%3Atrue%7D%7D%2C%22userinfo%22%3A%7B%22email%22%3Anull%2C%22name%22%3Anull%2C%22quota%22%3Anull%2C%22groups%22%3Anull%2C%22user_id%22%3A%7B%22essential%22%3Atrue%7D%7D%7D&state=YN7C2YTH1G0GUERS9AWJIEGJE3VIHP6Y&nonce=0DHHR241WY94Q359GYXM9RL1CEOGQMC9&code_challenge=3WDMHL3_vwH_RAtCF1EQe62C957HZPcLqQz3Uhky8uM&code_challenge_method=S256","headers":{"Cache-Control":["no-cache"],"User-Agent":["Mozilla/5.0+(compatible; UptimeRobot/2.0; http://www.uptimerobot.com/)"],"Accept":["text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8"],"Accept-Encoding":["gzip, deflate, br"],"X-Forwarded-Proto":["https"],"Via":["1.1 Caddy"],"X-Forwarded-For":["49.13.130.29"],"Referer":["https://nextcloud.aiursoft.cn"],"Accept-Language":["en-US,en;q=0.8"],"X-Forwarded-Host":["auth.aiursoft.cn"]},"tls":{"resumed":false,"version":772,"cipher_suite":4865,"proto":"","server_name":"auth.aiursoft.cn"}},"headers":{"X-Authentik-Id":["8c7197d81ceb4b59b6bea27db85eb80f"],"X-Frame-Options":["DENY"],"Content-Type":["text/html; charset=utf-8"],"Location":["/flows/-/default/authentication/?next=/application/o/authorize/%3Fclient_id%3Dwzt4srMcPxcNgaQh70XZtOoBCAlmTgV6IjeRAyUj%26response_type%3Dcode%26scope%3Demail%2Bnextcloud%2Bopenid%26redirect_uri%3Dhttps%253A%252F%252Fnextcloud.aiursoft.cn%252Findex.php%252Fapps%252Fuser_oidc%252Fcode%26claims%3D%257B%2522id_token%2522%253A%257B%2522email%2522%253Anull%252C%2522name%2522%253Anull%252C%2522quota%2522%253Anull%252C%2522groups%2522%253Anull%252C%2522user_id%2522%253A%257B%2522essential%2522%253Atrue%257D%257D%252C%2522userinfo%2522%253A%257B%2522email%2522%253Anull%252C%2522name%2522%253Anull%252C%2522quota%2522%253Anull%252C%2522groups%2522%253Anull%252C%2522user_id%2522%253A%257B%2522essential%2522%253Atrue%257D%257D%257D%26state%3DYN7C2YTH1G0GUERS9AWJIEGJE3VIHP6Y%26nonce%3D0DHHR241WY94Q359GYXM9RL1CEOGQMC9%26code_challenge%3D3WDMHL3_vwH_RAtCF1EQe62C957HZPcLqQz3Uhky8uM%26code_challenge_method%3DS256"],"Referrer-Policy":["same-origin"],"Set-Cookie":["REDACTED"],"Vary":["Accept-Encoding","Cookie"],"X-Content-Type-Options":["nosniff"],"X-Powered-By":["authentik"],"Content-Length":["23"],"Content-Encoding":["gzip"],"Date":["Sun, 27 Jul 2025 08:21:54 GMT"]},"status":302}
{"level":"info","ts":1753604514.6726158,"logger":"http.log.access","msg":"handled request","request":{"remote_ip":"49.13.130.29","remote_port":"3132","client_ip":"49.13.130.29","proto":"HTTP/1.1","method":"HEAD","host":"auth.aiursoft.cn","uri":"/application/o/authorize/?client_id=wzt4srMcPxcNgaQh70XZtOoBCAlmTgV6IjeRAyUj&response_type=code&scope=email+nextcloud+openid&redirect_uri=https%3A%2F%2Fnextcloud.aiursoft.cn%2Findex.php%2Fapps%2Fuser_oidc%2Fcode&claims=%7B%22id_token%22%3A%7B%22email%22%3Anull%2C%22name%22%3Anull%2C%22quota%22%3Anull%2C%22groups%22%3Anull%2C%22user_id%22%3A%7B%22essential%22%3Atrue%7D%7D%2C%22userinfo%22%3A%7B%22email%22%3Anull%2C%22name%22%3Anull%2C%22quota%22%3Anull%2C%22groups%22%3Anull%2C%22user_id%22%3A%7B%22essential%22%3Atrue%7D%7D%7D&state=YN7C2YTH1G0GUERS9AWJIEGJE3VIHP6Y&nonce=0DHHR241WY94Q359GYXM9RL1CEOGQMC9&code_challenge=3WDMHL3_vwH_RAtCF1EQe62C957HZPcLqQz3Uhky8uM&code_challenge_method=S256","headers":{"Referer":["https://nextcloud.aiursoft.cn"],"User-Agent":["Mozilla/5.0+(compatible; UptimeRobot/2.0; http://www.uptimerobot.com/)"],"Accept":["text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8"],"Accept-Encoding":["gzip, deflate, br"],"Accept-Language":["en-US,en;q=0.8"],"Cache-Control":["no-cache"],"Connection":["close"]},"tls":{"resumed":false,"version":772,"cipher_suite":4865,"proto":"","server_name":"auth.aiursoft.cn"}},"bytes_read":0,"user_id":"","duration":0.027245944,"size":0,"status":302,"resp_headers":{"Via":["1.1 Caddy"],"Content-Length":["23"],"Date":["Sun, 27 Jul 2025 08:21:54 GMT"],"X-Frame-Options":["DENY"],"Alt-Svc":["h3=\":443\"; ma=2592000"],"Content-Encoding":["gzip"],"X-Authentik-Id":["8c7197d81ceb4b59b6bea27db85eb80f"],"Set-Cookie":["REDACTED"],"Strict-Transport-Security":["max-age=63072000"],"Content-Type":["text/html; charset=utf-8"],"Referrer-Policy":["same-origin"],"X-Content-Type-Options":["nosniff"],"Location":["/flows/-/default/authentication/?next=/application/o/authorize/%3Fclient_id%3Dwzt4srMcPxcNgaQh70XZtOoBCAlmTgV6IjeRAyUj%26response_type%3Dcode%26scope%3Demail%2Bnextcloud%2Bopenid%26redirect_uri%3Dhttps%253A%252F%252Fnextcloud.aiursoft.cn%252Findex.php%252Fapps%252Fuser_oidc%252Fcode%26claims%3D%257B%2522id_token%2522%253A%257B%2522email%2522%253Anull%252C%2522name%2522%253Anull%252C%2522quota%2522%253Anull%252C%2522groups%2522%253Anull%252C%2522user_id%2522%253A%257B%2522essential%2522%253Atrue%257D%257D%252C%2522userinfo%2522%253A%257B%2522email%2522%253Anull%252C%2522name%2522%253Anull%252C%2522quota%2522%253Anull%252C%2522groups%2522%253Anull%252C%2522user_id%2522%253A%257B%2522essential%2522%253Atrue%257D%257D%257D%26state%3DYN7C2YTH1G0GUERS9AWJIEGJE3VIHP6Y%26nonce%3D0DHHR241WY94Q359GYXM9RL1CEOGQMC9%26code_challenge%3D3WDMHL3_vwH_RAtCF1EQe62C957HZPcLqQz3Uhky8uM%26code_challenge_method%3DS256"],"Vary":["Accept-Encoding","Cookie"],"X-Powered-By":["authentik"]}}
{"level":"debug","ts":1753604514.6838558,"logger":"http.handlers.reverse_proxy","msg":"selected upstream","dial":"stathub_stathubserver:5000","total_upstreams":1}
{"level":"debug","ts":1753604514.6847696,"logger":"http.handlers.reverse_proxy","msg":"upstream roundtrip","upstream":"stathub_stathubserver:5000","duration":0.000850831,"request":{"remote_ip":"192.168.50.62","remote_port":"58904","client_ip":"192.168.50.62","proto":"HTTP/1.1","method":"POST","host":"stathub.aiursoft.cn","uri":"/api/metrics","headers":{"Accept":["application/json"],"Content-Type":["application/json; charset=utf-8"],"Content-Length":["2650"],"User-Agent":[""],"X-Forwarded-For":["192.168.50.62"],"X-Forwarded-Proto":["https"],"X-Forwarded-Host":["stathub.aiursoft.cn"],"Via":["1.1 Caddy"]},"tls":{"resumed":true,"version":772,"cipher_suite":4865,"proto":"","server_name":"stathub.aiursoft.cn"}},"headers":{"Content-Type":["application/json; charset=utf-8"],"Date":["Sun, 27 Jul 2025 08:21:53 GMT"],"Server":["Kestrel"],"Vary":["Accept-Encoding"],"X-Frame-Options":["SAMEORIGIN"],"Content-Security-Policy":["frame-ancestors 'self'"]},"status":201}
{"level":"info","ts":1753604514.684859,"logger":"http.log.access","msg":"handled request","request":{"remote_ip":"192.168.50.62","remote_port":"58904","client_ip":"192.168.50.62","proto":"HTTP/1.1","method":"POST","host":"stathub.aiursoft.cn","uri":"/api/metrics","headers":{"Accept":["application/json"],"Content-Type":["application/json; charset=utf-8"],"Content-Length":["2650"]},"tls":{"resumed":true,"version":772,"cipher_suite":4865,"proto":"","server_name":"stathub.aiursoft.cn"}},"bytes_read":2650,"user_id":"","duration":0.001125525,"size":56,"status":201,"resp_headers":{"Content-Security-Policy":["frame-ancestors 'self'"],"Content-Type":["application/json; charset=utf-8"],"Alt-Svc":["h3=\":443\"; ma=2592000"],"X-Frame-Options":["SAMEORIGIN"],"Date":["Sun, 27 Jul 2025 08:21:53 GMT"],"Server":["Kestrel"],"Vary":["Accept-Encoding"],"Via":["1.1 Caddy"],"Strict-Transport-Security":["max-age=63072000"]}}
{"level":"debug","ts":1753604514.756761,"logger":"http.handlers.reverse_proxy","msg":"selected upstream","dial":"stathub_stathubserver:5000","total_upstreams":1}
{"level":"debug","ts":1753604514.7580035,"logger":"http.handlers.reverse_proxy","msg":"upstream roundtrip","upstream":"stathub_stathubserver:5000","duration":0.001143758,"request":{"remote_ip":"45.77.178.215","remote_port":"41356","client_ip":"45.77.178.215","proto":"HTTP/1.1","method":"POST","host":"stathub.aiursoft.cn","uri":"/api/metrics","headers":{"Via":["1.1 Caddy"],"Accept":["application/json"],"Content-Type":["application/json; charset=utf-8"],"Content-Length":["2591"],"User-Agent":[""],"X-Forwarded-For":["45.77.178.215"],"X-Forwarded-Proto":["https"],"X-Forwarded-Host":["stathub.aiursoft.cn"]},"tls":{"resumed":false,"version":772,"cipher_suite":4865,"proto":"","server_name":"stathub.aiursoft.cn"}},"headers":{"Date":["Sun, 27 Jul 2025 08:21:53 GMT"],"Server":["Kestrel"],"Vary":["Accept-Encoding"],"X-Frame-Options":["SAMEORIGIN"],"Content-Security-Policy":["frame-ancestors 'self'"],"Content-Type":["application/json; charset=utf-8"]},"status":201}
Consuming Logs
Regarding the Caddy logs above, it is crucial to understand: they are not in standard JSON format. Instead, each line is a separate JSON object. You must read them line by line. Attempting to deserialize the entire file at once will fail. Furthermore, as new requests arrive, new lines are continuously appended to the file.
Before proceeding, to ensure that my deep understanding of the file structure is correct, I accessed the host:
anduin@proart:/swarm-vol/sites-data/caddy/logs$ ls
web.log
anduin@proart:/swarm-vol/sites-data/caddy/logs$ ls -ashl
total 6.3M
4.0K drwxr-xr-x 2 root root 4.0K Jul 27 08:44 .
4.0K drwxr-xr-x 7 root root 4.0K Jul 27 08:44 ..
6.2M -rw------- 1 root root 6.2M Jul 27 08:54 web.log
anduin@proart:/swarm-vol/sites-data/caddy/logs$
This confirms that sites — specifically, the /sites-data/caddy/logs directory of the caddy container — already contains web.log.
Now, I believe the next step should focus on setting up the ClickHouse database and configuring the parsing of Caddy logs so they can be streamed into ClickHouse.
We can proceed in three steps:
- Deploy ClickHouse
- Design and create the log table schema
- Use Python to collect Caddy JSON logs, parse them, and write them to ClickHouse
Deploy ClickHouse
To deploy Clickhouse, I modified docker-compose.yml
clickhouse:
image: hub.aiursoft.cn/clickhouse/clickhouse-server:latest
ports:
- target: 8123
published: 8123
protocol: tcp
mode: host
- target: 9000
published: 9000
protocol: tcp
mode: host
volumes:
- click-house-data:/var/lib/clickhouse
stop_grace_period: 60s
deploy:
resources:
limits:
cpus: '4.0'
memory: 8G
update_config:
order: stop-first
delay: 60s
Pretty fast, I got clickhouse running:
anduin@proart:/swarm-vol/Box$ sudo docker ps | grep clickhouse
99dae96430f9 hub.aiursoft.cn/clickhouse/clickhouse-server:latest "/entrypoint.sh" 8 seconds ago Up 7 seconds 0.0.0.0:8123->8123/tcp, [::]:8123->8123/tcp, 0.0.0.0:9000->9000/tcp, [::]:9000->9000/tcp, 9009/tcp incoming_clickhouse.1.3w3lo1xg8fywgzswzyvufi072
anduin@proart:/swarm-vol/Box$ sudo docker logs -f 99dae96430f9
/entrypoint.sh: neither CLICKHOUSE_USER nor CLICKHOUSE_PASSWORD is set, disabling network access for user 'default'
Processing configuration file '/etc/clickhouse-server/config.xml'.
Merging configuration file '/etc/clickhouse-server/config.d/docker_related_config.xml'.
Logging trace to /var/log/clickhouse-server/clickhouse-server.log
Logging errors to /var/log/clickhouse-server/clickhouse-server.err.log
^Canduin@proart:/swarm-vol/Box$ cd /swarm-vol/sites-click-house-data/
anduin@proart:/swarm-vol/sites-click-house-data$ ls
access data flags format_schemas metadata metadata_dropped preprocessed_configs status store tmp user_files uuid
anduin@proart:/swarm-vol/sites-click-house-data$
It looks like ClickHouse is working fine, so I quickly jumped in as well:
anduin@proart:/swarm-vol/sites-click-house-data$ sudo docker exec -it 99dae96430f9 clickhouse-client --user default
ClickHouse client version 25.6.4.12 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 25.6.4.
Warnings:
* Delay accounting is not enabled, OSIOWaitMicroseconds will not be gathered. You can enable it using `echo 1 > /proc/sys/kernel/task_delayacct` or by using sysctl.
99dae96430f9 :)
Creating a ClickHouse Table
Next, we should carefully analyze the caddy logs. Here, we need to proceed with caution when designing the tables, as changing them may be very difficult in the future. After thinking about it for a while, I executed:
99dae96430f9 :) CREATE DATABASE IF NOT EXISTS logs;
CREATE TABLE logs.caddy_requests (
ts DateTime64(3, 'UTC'),
level LowCardinality(String),
logger LowCardinality(String),
msg String,
remote_ip String,
remote_port UInt16,
method LowCardinality(String),
host LowCardinality(String),
uri String,
status UInt16,
duration_ms Float32,
bytes_sent UInt64,
user_agent String,
err_id LowCardinality(String),
err_trace String
)
ENGINE = MergeTree()
-- 按“年-月”分区，也可改成 toYYYYMMDD(ts) 做日分区
PARTITION BY toYYYYMM(ts)
-- 主键兼排序键
ORDER BY (ts, host, remote_ip)
-- 自动 90 天后清理旧数据（可选）
TTL ts + INTERVAL 90 DAY
SETTINGS index_granularity = 8192;
CREATE DATABASE IF NOT EXISTS logs
Query id: 418c462f-58cd-42d2-af34-192a793ebd92
Ok.
0 rows in set. Elapsed: 0.002 sec.
CREATE TABLE logs.caddy_requests
(
`ts` DateTime64(3, 'UTC'),
`level` LowCardinality(String),
`logger` LowCardinality(String),
`msg` String,
`remote_ip` String,
`remote_port` UInt16,
`method` LowCardinality(String),
`host` LowCardinality(String),
`uri` String,
`status` UInt16,
`duration_ms` Float32,
`bytes_sent` UInt64,
`user_agent` String,
`err_id` LowCardinality(String),
`err_trace` String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (ts, host, remote_ip)
TTL ts + toIntervalDay(90)
SETTINGS index_granularity = 8192
Query id: 91e21c7a-e33a-40fc-a65c-48a35105358b
Ok.
0 rows in set. Elapsed: 0.007 sec.
99dae96430f9 :)
It seems there are no issues. To verify that the table exists and has a reasonable structure, I ran the following:
99dae96430f9 :) -- 列出所有数据库，确认 logs 库存在
SHOW DATABASES;
-- 列出 logs 库中的所有表，确认 caddy_requests 在列
SHOW TABLES FROM logs;
-- 查看表结构，确认各列类型和分区定义
DESCRIBE TABLE logs.caddy_requests;
-- 或者直接看完整的 CREATE TABLE 语句
SHOW CREATE TABLE logs.caddy_requests;
SHOW DATABASES
Query id: 6fa522c4-8e60-4be9-a47c-496e89d8cd97
┌─name───────────────┐
1. │ INFORMATION_SCHEMA │
2. │ default │
3. │ information_schema │
4. │ logs │
5. │ system │
└────────────────────┘
5 rows in set. Elapsed: 0.002 sec.
SHOW TABLES FROM logs
Query id: 4372beac-da98-48c8-94b8-32b653a87a2a
┌─name───────────┐
1. │ caddy_requests │
└────────────────┘
1 row in set. Elapsed: 0.002 sec.
DESCRIBE TABLE logs.caddy_requests
Query id: 8c24a725-5a93-4ec3-9199-6265cb3a60e4
┌─name────────┬─type────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
1. │ ts │ DateTime64(3, 'UTC') │ │ │ │ │ │
2. │ level │ LowCardinality(String) │ │ │ │ │ │
3. │ logger │ LowCardinality(String) │ │ │ │ │ │
4. │ msg │ String │ │ │ │ │ │
5. │ remote_ip │ String │ │ │ │ │ │
6. │ remote_port │ UInt16 │ │ │ │ │ │
7. │ method │ LowCardinality(String) │ │ │ │ │ │
8. │ host │ LowCardinality(String) │ │ │ │ │ │
9. │ uri │ String │ │ │ │ │ │
10. │ status │ UInt16 │ │ │ │ │ │
11. │ duration_ms │ Float32 │ │ │ │ │ │
12. │ bytes_sent │ UInt64 │ │ │ │ │ │
13. │ user_agent │ String │ │ │ │ │ │
14. │ err_id │ LowCardinality(String) │ │ │ │ │ │
15. │ err_trace │ String │ │ │ │ │ │
└─────────────┴─────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
15 rows in set. Elapsed: 0.001 sec.
SHOW CREATE TABLE logs.caddy_requests
Query id: ff56bcfd-6cd5-4d30-a760-0cca533bdef3
┌─statement─────────────────────────────────┐
1. │ CREATE TABLE logs.caddy_requests ↴│
│↳( ↴│
│↳ `ts` DateTime64(3, 'UTC'),↴│
│↳ `level` LowCardinality(String), ↴│
│↳ `logger` LowCardinality(String), ↴│
│↳ `msg` String, ↴│
│↳ `remote_ip` String, ↴│
│↳ `remote_port` UInt16, ↴│
│↳ `method` LowCardinality(String), ↴│
│↳ `host` LowCardinality(String), ↴│
│↳ `uri` String, ↴│
│↳ `status` UInt16, ↴│
│↳ `duration_ms` Float32, ↴│
│↳ `bytes_sent` UInt64, ↴│
│↳ `user_agent` String, ↴│
│↳ `err_id` LowCardinality(String), ↴│
│↳ `err_trace` String ↴│
│↳) ↴│
│↳ENGINE = MergeTree ↴│
│↳PARTITION BY toYYYYMM(ts) ↴│
│↳ORDER BY (ts, host, remote_ip) ↴│
│↳TTL ts + toIntervalDay(90) ↴│
│↳SETTINGS index_granularity = 8192 │
└───────────────────────────────────────────┘
1 row in set. Elapsed: 0.001 sec.
99dae96430f9 :)
Writing a Simple Collector in Python
Next, I believe we should focus on writing Caddy's data into the ClickHouse database. To accomplish this, I plan to write a collector in Python.
Of course, we also need to add a service to docker-compose. However, before that, we first need to write a Dockerfile. This Dockerfile will configure Python to read logs from JSON, convert each line of JSON into structured fields, and then write them to ClickHouse.
Once this Docker image has been built, we can formally add it to docker-compose.
So I wrote the file Dockerfile:
# ===========================
# Build/Runtime in one stage
# ===========================
FROM python:3.12-slim
ENV PYTHONUNBUFFERED=1 \
PIP_NO_CACHE_DIR=1
RUN apt-get update && apt-get install -y --no-install-recommends \
curl ca-certificates \
&& rm -rf /var/lib/apt/lists/*
RUN pip install clickhouse-connect==0.7.19 uvloop==0.20.0 watchdog==4.0.1
WORKDIR /app
COPY main.py /app/main.py
# state 目录保存 offset
VOLUME ["/state"]
# 默认环境变量（可在 compose/Swarm 覆盖）
ENV LOG_FILE=/data/caddy/logs/web.log \
CLICKHOUSE_URL=http://clickhouse:8123 \
CLICKHOUSE_USER=default \
CLICKHOUSE_PASSWORD=123456 \
CLICKHOUSE_DATABASE=logs \
CLICKHOUSE_TABLE=caddy_requests \
BATCH_SIZE=1000 \
FLUSH_INTERVAL_SEC=2
CMD ["python", "/app/main.py"]
At the same time, I also wrote the main.py file. This Python program is a custom-built log ETL pipeline: it continuously tails Caddy’s line-based JSON log files (with support for log rotation), filters only http.log.access records, parses and cleans them into a fixed set of 15 columns (timestamp, IP, method, URI, status code, duration, etc.), converts Caddy’s floating-point timestamps into local-timezone DateTime64(3), explicitly casts numeric fields, and safely extracts the first item from the User-Agent header. It then buffers records in an asynchronous queue and triggers batch inserts based on either record count or elapsed time, writing to ClickHouse via clickhouse-connect. On startup, it first queries max(ts) and uses binary search to locate the file offset, avoiding duplicate imports. Upon receiving SIGINT/SIGTERM, it performs a graceful shutdown and executes a final flush. The overall goal is to avoid the complexity of Vector while maintaining recoverability, low latency, batch efficiency, and type stability.
#!/usr/bin/env python3
import os
import json
import time
import signal
import errno
import threading
import asyncio
from datetime import datetime, timezone
from zoneinfo import ZoneInfo
import logging
import uvloop
from clickhouse_connect import get_client
# === Logging setup ===
logging.basicConfig(level=logging.DEBUG, format='[%(levelname)s] %(asctime)s %(message)s')
logger = logging.getLogger(__name__)
# === Configuration ===
LOG_FILE = os.getenv('LOG_FILE', '/data/caddy/logs/web.log')
CH_URL = os.getenv('CLICKHOUSE_URL', 'http://clickhouse:8123')
CH_USER = os.getenv('CLICKHOUSE_USER', 'default')
CH_PASS = os.getenv('CLICKHOUSE_PASSWORD', '')
CH_DB = os.getenv('CLICKHOUSE_DATABASE', 'logs')
CH_TABLE = os.getenv('CLICKHOUSE_TABLE', 'caddy_requests')
BATCH_SIZE = int(os.getenv('BATCH_SIZE', '1000'))
FLUSH_INTERVAL = float(os.getenv('FLUSH_INTERVAL_SEC', '2'))
TZ = ZoneInfo(os.getenv('TZ', 'UTC'))
# Columns in ClickHouse order
COLUMNS = [
'ts', 'level', 'logger', 'msg',
'remote_ip', 'remote_port', 'method', 'host', 'uri',
'status', 'duration_ms', 'bytes_sent',
'user_agent', 'err_id', 'err_trace'
]
# Shutdown event
stop_event = threading.Event()
def create_ch_client():
secure = CH_URL.startswith('https')
parts = CH_URL.split('://')[-1].split(':')
host = parts[0]
port = int(parts[1]) if len(parts) > 1 else 8123
logger.debug("Creating ClickHouse client to %s:%d (secure=%s)", host, port, secure)
return get_client(host=host, port=port, username=CH_USER, password=CH_PASS, database=CH_DB, secure=secure)
def get_last_timestamp():
client = create_ch_client()
q = f"SELECT max(ts) FROM {CH_DB}.{CH_TABLE}"
logger.debug("Querying last timestamp with: %s", q)
res = client.query(q)
rows = getattr(res, 'result_rows', None)
if rows and rows[0][0] is not None:
val = rows[0][0]
try:
ts = val.timestamp() if hasattr(val, 'timestamp') else float(val)
logger.info("Last timestamp in ClickHouse: %s", ts)
return ts
except Exception as e:
logger.error("Failed to parse max(ts) result '%s': %s", val, e)
logger.info("No existing timestamp found, starting from 0")
return 0.0
def find_start_offset(path, last_ts):
logger.debug("Finding start offset in %s for ts > %s", path, last_ts)
with open(path, 'r', encoding='utf-8', errors='ignore') as f:
f.seek(0, os.SEEK_END)
file_size = f.tell()
low, high, best = 0, file_size, 0
while low < high:
mid = (low + high) // 2
f.seek(mid, os.SEEK_SET)
f.readline() # skip partial
pos = f.tell()
line = f.readline()
if not line:
high = mid
continue
try:
rec = json.loads(line)
ts_val = float(rec.get('ts', 0.0))
except Exception:
low = pos + len(line)
continue
if ts_val <= last_ts:
best = pos + len(line)
low = best
else:
high = mid
logger.info("Determined start offset: %d", best)
return best
def to_datetime(ts_float):
dt_utc = datetime.fromtimestamp(ts_float, tz=timezone.utc)
return dt_utc.astimezone(TZ)
def extract_first(hdrs, key):
v = hdrs.get(key)
if isinstance(v, list):
return v[0]
if isinstance(v, str):
return v
return ''
def transform(rec):
logger.debug("Transforming record with ts=%s, logger=%s", rec.get('ts'), rec.get('logger'))
request = rec.get('request', {}) or {}
headers = request.get('headers', {}) or {}
try:
ts_dt = to_datetime(float(rec.get('ts', 0.0)))
except Exception:
ts_dt = datetime.now(TZ)
duration = rec.get('duration')
duration_ms = float(duration) * 1000.0 if duration is not None else float(rec.get('duration_ms', 0))
row = (
ts_dt,
str(rec.get('level', '')),
str(rec.get('logger', '')),
str(rec.get('msg', '')),
str(request.get('remote_ip', '')),
int(request.get('remote_port', 0) or 0),
str(request.get('method', '')),
str(request.get('host', '')),
str(request.get('uri', '')),
int(rec.get('status', 0) or 0),
float(duration_ms),
int(rec.get('size', rec.get('bytes_sent', 0)) or 0),
str(extract_first(headers, 'User-Agent')),
str(rec.get('err_id', '')),
str(rec.get('err_trace', ''))
)
logger.debug("Transformed row: %s", row)
return row
async def flusher(queue):
client = create_ch_client()
batch, last_flush = [], time.time()
while not stop_event.is_set():
try:
item = await asyncio.wait_for(queue.get(), timeout=FLUSH_INTERVAL)
batch.append(item)
logger.debug("Queued batch size: %d", len(batch))
except asyncio.TimeoutError:
pass
now = time.time()
if batch and (len(batch) >= BATCH_SIZE or now - last_flush >= FLUSH_INTERVAL):
try:
logger.info("Flushing %d rows to ClickHouse", len(batch))
client.insert(CH_TABLE, batch, column_names=COLUMNS)
except Exception as e:
logger.error("Insert failed: %s", e)
else:
batch.clear()
last_flush = now
if batch:
try:
logger.info("Final flush of %d rows", len(batch))
client.insert(CH_TABLE, batch, column_names=COLUMNS)
except Exception as e:
logger.error("Final insert failed: %s", e)
async def reader(queue):
last_ts = get_last_timestamp()
logger.info("Starting reader loop")
start_offset = find_start_offset(LOG_FILE, last_ts)
f = open(LOG_FILE, 'r', encoding='utf-8', errors='ignore')
inode = os.fstat(f.fileno()).st_ino
f.seek(start_offset)
while not stop_event.is_set():
line = f.readline()
if not line:
try:
st = os.stat(LOG_FILE)
except OSError as e:
if e.errno == errno.ENOENT:
await asyncio.sleep(0.5)
continue
else:
raise
if st.st_ino != inode:
logger.info("Detected log rotation, recalculating start offset")
f.close()
start_offset = find_start_offset(LOG_FILE, last_ts)
f = open(LOG_FILE, 'r', encoding='utf-8', errors='ignore')
inode = os.fstat(f.fileno()).st_ino
f.seek(start_offset)
continue
await asyncio.sleep(0.2)
continue
try:
rec = json.loads(line)
except Exception as e:
logger.warning("Failed to parse JSON line: %s", e)
continue
if rec.get('logger') != 'http.log.access':
continue
try:
row = transform(rec)
except Exception as e:
logger.error("Transform error: %s", e)
continue
await queue.put(row)
logger.debug("Row queued, timestamp=%s", row[0])
def handle_signal(signum, frame):
logger.info("Received signal %d, shutting down", signum)
stop_event.set()
def main():
signal.signal(signal.SIGTERM, handle_signal)
signal.signal(signal.SIGINT, handle_signal)
uvloop.install()
loop = asyncio.get_event_loop()
queue = asyncio.Queue(maxsize=50000)
tasks = [
loop.create_task(reader(queue)),
loop.create_task(flusher(queue)),
]
try:
loop.run_until_complete(asyncio.gather(*tasks))
finally:
for t in tasks:
t.cancel()
loop.stop()
loop.close()
if __name__ == '__main__':
main()
Using Collectors to Ingest Caddy Logs into ClickHouse
Now that I've prepared the Vector container, it's time to add it to my docker-compose.yml!
At this point, the three key service definitions are as follows:
version: '3.9'
services:
sites:
image: localhost:8080/box_starting/local_sites
ports:
# These ports are for internal use. For external, FRP will handle it.
- target: 80
published: 80
protocol: tcp
mode: host
- target: 443
published: 443
protocol: tcp
mode: host
networks:
- frp_net
- proxy_app
volumes:
- sites-data:/data
stop_grace_period: 60s
deploy:
resources:
limits:
cpus: '8.0'
memory: 16G
update_config:
order: stop-first
delay: 60s
clickhouse:
image: hub.aiursoft.cn/clickhouse/clickhouse-server:latest
environment:
- CLICKHOUSE_USER=default
- CLICKHOUSE_PASSWORD=123456
networks:
- internal
volumes:
- click-house-data:/var/lib/clickhouse
stop_grace_period: 60s
deploy:
resources:
limits:
cpus: '4.0'
memory: 8G
update_config:
order: stop-first
delay: 60s
pysyncer:
image: localhost:8080/box_starting/local_pysyncer
depends_on:
- clickhouse
networks:
- internal
volumes:
- sites-data:/data:ro
stop_grace_period: 60s
deploy:
resources:
limits:
cpus: '1.0'
memory: 1G
update_config:
order: stop-first
delay: 60s
Before proceeding, let's first review the format of Caddy's log files:
anduin@proart:/swarm-vol/Box$ cd /swarm-vol/sites-data/
anduin@proart:/swarm-vol/sites-data$ ls
caddy
anduin@proart:/swarm-vol/sites-data$ cd caddy/logs/
anduin@proart:/swarm-vol/sites-data/caddy/logs$ ls
web.log
anduin@proart:/swarm-vol/sites-data/caddy/logs$ tail -n 20 ./web.log
tail: cannot open './web.log' for reading: Permission denied
anduin@proart:/swarm-vol/sites-data/caddy/logs$ sudo tail -n 20 ./web.log
{"level":"info","ts":1753607471.3656464,"logger":"http.log.access","msg":"handled request","request":{"remote_ip":"52.81.114.99","remote_port":"50108","client_ip":"52.81.114.99","proto":"HTTP/2.0","method":"POST","host":"gitlab.aiursoft.cn","uri":"/api/graphql","headers":{"Accept":["*/*"],"Content-Type":["application/json"],"Accept-Language":["zh,zh-CN;q=0.9"],"Accept-Encoding":["gzip, deflate, br"],"Content-Length":["2034"],"User-Agent":["Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.5232.1704 Safari/537.36"],"Sec-Fetch-Dest":["empty"],"X-Csrf-Token":["3KmgeS9sfn-Nc9rEiGp9Om810EuS-zUlqjEG6Nimm1abwyEf4J74xYV2OEbm96o1UBxsgur_Nn9Hn0ePxO6WzA"],"X-Gitlab-Feature-Category":["source_code_management"],"Sec-Fetch-Mode":["cors"],"Origin":["https://gitlab.aiursoft.cn"],"Sec-Fetch-Site":["same-origin"],"Referer":["https://gitlab.aiursoft.cn/edgeneko/transformers/-/blob/a0857740c0e6127485c11476650314df3accc2b6/src/transformers/models/patchtsmixer/modeling_patchtsmixer.py"],"Cookie":["REDACTED"]},"tls":{"resumed":false,"version":772,"cipher_suite":4865,"proto":"h2","server_name":"gitlab.aiursoft.cn"}},"bytes_read":2034,"user_id":"","duration":0.146402042,"size":1840,"status":200,"resp_headers":{"X-Permitted-Cross-Domain-Policies":["none"],"X-Content-Type-Options":["nosniff"],"X-Frame-Options":["SAMEORIGIN"],"Content-Security-Policy":[""],"Via":["1.1 Caddy"],"Access-Control-Allow-Origin":["https://gitlab.aiursoft.cn"],"Nel":["{\"max_age\": 0}"],"Server":["nginx"],"Access-Control-Expose-Headers":["Link, X-Total, X-Total-Pages, X-Per-Page, X-Page, X-Next-Page, X-Prev-Page, X-Gitlab-Blob-Id, X-Gitlab-Commit-Id, X-Gitlab-Content-Sha256, X-Gitlab-Encoding, X-Gitlab-File-Name, X-Gitlab-File-Path, X-Gitlab-Last-Commit-Id, X-Gitlab-Ref, X-Gitlab-Size, X-Request-Id, ETag"],"Etag":["W/\"32190250e9efca1b52f562aebee5a31f\""],"X-Download-Options":["noopen"],"X-Ua-Compatible":["IE=edge"],"Permissions-Policy":["interest-cohort=()"],"Vary":["Accept-Encoding","Accept, Origin"],"X-Runtime":["0.113391"],"Referrer-Policy":["strict-origin-when-cross-origin"],"X-Gitlab-Meta":["{\"correlation_id\":\"01K15JD04HXEXJ5RW0ERVCN3FD\",\"version\":\"1\"}"],"Content-Type":["application/json; charset=utf-8"],"Date":["Sun, 27 Jul 2025 09:11:11 GMT"],"X-Request-Id":["01K15JD04HXEXJ5RW0ERVCN3FD"],"Access-Control-Allow-Credentials":["true"],"Cache-Control":["max-age=0, private, must-revalidate"],"X-Xss-Protection":["1; mode=block"],"Access-Control-Allow-Methods":["GET, HEAD, POST, PUT, PATCH, DELETE, OPTIONS"],"Alt-Svc":["h3=\":443\"; ma=2592000"],"Strict-Transport-Security":["max-age=63072000","max-age=63072000"],"Content-Encoding":["gzip"],"Access-Control-Max-Age":["7200"]}}
{"level":"debug","ts":1753607471.3894894,"logger":"http.handlers.reverse_proxy","msg":"selected upstream","dial":"gitlab_gitlab:80","total_upstreams":1}
{"level":"debug","ts":1753607471.4118912,"logger":"http.handlers.reverse_proxy","msg":"selected upstream","dial":"gitlab_gitlab:80","total_upstreams":1}
{"level":"debug","ts":1753607471.4228811,"logger":"http.handlers.reverse_proxy","msg":"upstream roundtrip","upstream":"gitlab_gitlab:80","duration":0.033285786,"request":{"remote_ip":"52.81.114.99","remote_port":"50108","client_ip":"52.81.114.99","proto":"HTTP/2.0","method":"POST","host":"gitlab.aiursoft.cn","uri":"/-/collect_events","headers":{"Accept-Encoding":["gzip, deflate, br"],"Accept":["*/*"],"Content-Length":["2739"],"Sec-Fetch-Site":["same-origin"],"Sec-Fetch-Mode":["cors"],"Referer":["https://gitlab.aiursoft.cn/edgeneko/transformers/-/blob/a0857740c0e6127485c11476650314df3accc2b6/src/transformers/models/patchtsmixer/modeling_patchtsmixer.py"],"X-Forwarded-Proto":["https"],"Cookie":["REDACTED"],"Origin":["https://gitlab.aiursoft.cn"],"Sec-Fetch-Dest":["empty"],"User-Agent":["Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.5232.1704 Safari/537.36"],"X-Forwarded-For":["52.81.114.99"],"X-Forwarded-Host":["gitlab.aiursoft.cn"],"Content-Type":["application/json; charset=UTF-8"],"Accept-Language":["zh,zh-CN;q=0.9"],"Via":["2.0 Caddy"]},"tls":{"resumed":false,"version":772,"cipher_suite":4865,"proto":"h2","server_name":"gitlab.aiursoft.cn"}},"headers":{"Date":["Sun, 27 Jul 2025 09:11:11 GMT"],"Connection":["keep-alive"],"Cache-Control":["no-cache"],"X-Frame-Options":["SAMEORIGIN"],"X-Gitlab-Meta":["{\"correlation_id\":\"01K15JD09MW83H4ECSDB3F0W2H\",\"version\":\"1\"}"],"X-Permitted-Cross-Domain-Policies":["none"],"X-Request-Id":["01K15JD09MW83H4ECSDB3F0W2H"],"Content-Security-Policy":[""],"X-Download-Options":["noopen"],"X-Runtime":["0.008243"],"Content-Type":["text/html"],"X-Xss-Protection":["0"],"Strict-Transport-Security":["max-age=63072000"],"Referrer-Policy":["strict-origin-when-cross-origin"],"Server":["nginx"],"Content-Length":["0"],"Nel":["{\"max_age\": 0}"],"X-Content-Type-Options":["nosniff"]},"status":200}
Regarding the Caddy logs mentioned above, it is crucial to understand that they are not in standard JSON format; rather, each line is a separate JSON object. You must read them line by line. If you attempt to deserialize the entire file at once, it will fail. Additionally, as requests arrive, new lines are continuously appended to the log.
After actually deploying the container described above, it worked very well.
anduin@proart:/swarm-vol/Box$ sudo docker ps | grep incoming
12c0c00c8bc1 localhost:8080/box_starting/local_pysyncer:latest "python /app/main.py" 19 seconds ago Up 14 seconds incoming_pysyncer.1.zcri465vte59q7ykcvkazicmz
3e2644ae35e8 hub.aiursoft.cn/clickhouse/clickhouse-server:latest "/entrypoint.sh" 25 seconds ago Up 16 seconds 8123/tcp, 9000/tcp, 9009/tcp incoming_clickhouse.1.g78dnhblb5lo47a3r4slpwlud
b9dbeec4c2e4 localhost:8080/box_starting/local_sites:latest "sh -c 'caddy run --…" 2 hours ago Up 2 hours 0.0.0.0:80->80/tcp, [::]:80->80/tcp, 0.0.0.0:443->443/tcp, [::]:443->443/tcp, 443/udp, 2019/tcp incoming_sites.1.tchujhi57vpj5h54yb6cvhot0
cd64d810cd87 localhost:8080/box_starting/local_frp:latest "sh -c /entrypoint.sh" 2 days ago Up 2 days incoming_frpc_ningbo.1.sizoca54zijta6fl4f7d6atc0
8038df1ff221 localhost:8080/box_starting/local_frp:latest "sh -c /entrypoint.sh" 2 days ago Up 2 days incoming_frpc_jp.1.cbuf8m2wdfr7qoms9wd9a9jdb
anduin@proart:/swarm-vol/Box$ sudo docker logs -f 12c0c00c8bc1
/app/main.py:226: DeprecationWarning: There is no current event loop
loop = asyncio.get_event_loop()
[DEBUG] 2025-07-27 10:32:39,699 Creating ClickHouse client to clickhouse:8123 (secure=False)
[DEBUG] 2025-07-27 10:32:39,711 Querying last timestamp with: SELECT max(ts) FROM logs.caddy_requests
[INFO] 2025-07-27 10:32:39,720 Last timestamp in ClickHouse: 1753612350.828
[INFO] 2025-07-27 10:32:39,720 Starting reader loop
[DEBUG] 2025-07-27 10:32:39,720 Finding start offset in /data/caddy/logs/web.log for ts > 1753612350.828
[INFO] 2025-07-27 10:32:39,720 Determined start offset: 139816254
[DEBUG] 2025-07-27 10:32:39,720 Transforming record with ts=1753612350.8285708, logger=http.log.access
[DEBUG] 2025-07-27 10:32:39,720 Transformed row: (datetime.datetime(2025, 7, 27, 10, 32, 30, 828571, tzinfo=zoneinfo.ZoneInfo(key='UTC')), 'info', 'http.log.access', 'handled request', '51.222.253.18', 43610, 'GET', 'nuget.aiursoft.cn', '/packages/microsoft.extensions.caching.stackexchangeredis/9.0.6', 200, 905.742426, 6435, 'Mozilla/5.0 (compatible; AhrefsBot/7.0; +http://ahrefs.com/robot/)', '', '')
[DEBUG] 2025-07-27 10:32:39,720 Row queued, timestamp=2025-07-27 10:32:30.828571+00:00
[DEBUG] 2025-07-27 10:32:39,720 Transforming record with ts=1753612350.8735032, logger=http.log.access
Checking Collected Structured Data in ClickHouse
We can query ClickHouse to check if the data is normal:
anduin@proart:~$ sudo docker ps | grep clickhouse
3e2644ae35e8 hub.aiursoft.cn/clickhouse/clickhouse-server:latest "/entrypoint.sh" 4 minutes ago Up 4 minutes 8123/tcp, 9000/tcp, 9009/tcp incoming_clickhouse.1.g78dnhblb5lo47a3r4slpwlud
anduin@proart:~$ sudo docker exec -it 3e2644ae35e8 clickhouse-client --user default
ClickHouse client version 25.6.4.12 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 25.6.4.
Warnings:
* Delay accounting is not enabled, OSIOWaitMicroseconds will not be gathered. You can enable it using `echo 1 > /proc/sys/kernel/task_delayacct` or by using sysctl.
3e2644ae35e8 :)
Next, you can first check how much data there is:
3e2644ae35e8 :) SELECT count() FROM logs.caddy_requests;
SELECT count()
FROM logs.caddy_requests
Query id: 52567be0-23f9-46d6-896f-04166b1d6fa7
┌─count()─┐
1. │ 38177 │
└─────────┘
1 row in set. Elapsed: 0.004 sec.
Check the recent HTTP requests:
3e2644ae35e8 :) SELECT ts, level, logger, msg, remote_ip, remote_port, method, host, status, duration_ms, bytes_sent, uri
FROM logs.caddy_requests
ORDER BY ts DESC
LIMIT 30
FORMAT Pretty;
SELECT
ts,
level,
logger,
msg,
remote_ip,
remote_port,
method,
host,
status,
duration_ms,
bytes_sent,
uri
FROM logs.caddy_requests
ORDER BY ts DESC
LIMIT 30
FORMAT Pretty
Query id: 334414e5-7810-4e3e-8cda-7140480d9ed2
┏━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ ts ┃ level ┃ logger ┃ msg ┃ remote_ip ┃ remote_port ┃ method ┃ host ┃ status ┃ duration_ms ┃ bytes_sent ┃ uri ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
├─────────────────────────┼───────┼─────────────────┼─────────────────┼───────────────────────────────────────┼─────────────┼──────────┼─────────────────────────────────┼────────┼─────────────┼────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────┤
20. │ 2025-07-27 18:37:56.431 │ info │ http.log.access │ handled request │ 222.222.222.222 │ 43734 │ POST │ stathub.aiursoft.cn │ 201 │ 0.911467 │ 56 │ /api/metrics │
├─────────────────────────┼───────┼─────────────────┼─────────────────┼───────────────────────────────────────┼─────────────┼──────────┼─────────────────────────────────┼────────┼─────────────┼────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────┤
21. │ 2025-07-27 18:37:56.374 │ info │ http.log.access │ handled request │ 222.222.222.222 │ 52612 │ GET │ gitlab.aiursoft.cn │ 404 │ 44.58619 │ 27 │ /api/v4/projects/anduin%2Fanduinos-docs/releases/permalink/latest │
├─────────────────────────┼───────┼─────────────────┼─────────────────┼───────────────────────────────────────┼─────────────┼──────────┼─────────────────────────────────┼────────┼─────────────┼────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────┤
22. │ 2025-07-27 18:37:56.370 │ info │ http.log.access │ handled request │ 222.222.222.222 │ 52612 │ GET │ gitlab.aiursoft.cn │ 200 │ 36.16609 │ 430 │ /api/v4/projects/anduin%2Fanduinos-docs │
├─────────────────────────┼───────┼─────────────────┼─────────────────┼───────────────────────────────────────┼─────────────┼──────────┼─────────────────────────────────┼────────┼─────────────┼────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────┤
23. │ 2025-07-27 18:37:56.344 │ info │ http.log.access │ handled request │ 222.222.222.222 │ 42668 │ POST │ gitlab.aiursoft.cn │ 204 │ 2.366945 │ 0 │ /api/v4/jobs/request │
├─────────────────────────┼───────┼─────────────────┼─────────────────┼───────────────────────────────────────┼─────────────┼──────────┼─────────────────────────────────┼────────┼─────────────┼────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────┤
24. │ 2025-07-27 18:37:55.762 │ info │ http.log.access │ handled request │ 222.222.222.222 │ 49907 │ PROPFIND │ nextcloud.aiursoft.cn │ 207 │ 47.25869 │ 254 │ /remote.php/dav/files/angelwing32/ │
├─────────────────────────┼───────┼─────────────────┼─────────────────┼───────────────────────────────────────┼─────────────┼──────────┼─────────────────────────────────┼────────┼─────────────┼────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────┤
25. │ 2025-07-27 18:37:55.738 │ info │ http.log.access │ handled request │ 222.222.222.222 │ 39766 │ POST │ stathub.aiursoft.cn │ 201 │ 0.807904 │ 56 │ /api/metrics │
├─────────────────────────┼───────┼─────────────────┼─────────────────┼───────────────────────────────────────┼─────────────┼──────────┼─────────────────────────────────┼────────┼─────────────┼────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────┤
26. │ 2025-07-27 18:37:55.497 │ info │ http.log.access │ handled request │ 222.222.222.222 │ 56234 │ HEAD │ hub.aiursoft.cn │ 200 │ 3.154572 │ 0 │ /v2/anduin/anduinos-docs/manifests/latest │
├─────────────────────────┼───────┼─────────────────┼─────────────────┼───────────────────────────────────────┼─────────────┼──────────┼─────────────────────────────────┼────────┼─────────────┼────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────┤
27. │ 2025-07-27 18:37:55.319 │ info │ http.log.access │ handled request │ 222.222.222.222 │ 37020 │ GET │ dashboard.inventory.aiursoft.cn │ 308 │ 0.019892 │ 0 │ /assets/js/kcfinder/upload.php │
├─────────────────────────┼───────┼─────────────────┼─────────────────┼───────────────────────────────────────┼─────────────┼──────────┼─────────────────────────────────┼────────┼─────────────┼────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────┤
28. │ 2025-07-27 18:37:54.759 │ info │ http.log.access │ handled request │ 222.222.222.222 │ 57226 │ POST │ stathub.aiursoft.cn │ 201 │ 1.095069 │ 56 │ /api/metrics │
├─────────────────────────┼───────┼─────────────────┼─────────────────┼───────────────────────────────────────┼─────────────┼──────────┼─────────────────────────────────┼────────┼─────────────┼────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────┤
29. │ 2025-07-27 18:37:54.672 │ info │ http.log.access │ handled request │ 222.222.222.222 │ 36668 │ POST │ stathub.aiursoft.cn │ 201 │ 0.99698 │ 56 │ /api/metrics │
├─────────────────────────┼───────┼─────────────────┼─────────────────┼───────────────────────────────────────┼─────────────┼──────────┼─────────────────────────────────┼────────┼─────────────┼────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────┤
30. │ 2025-07-27 18:37:54.393 │ info │ http.log.access │ handled request │ 222.222.222.222 │ 40810 │ POST │ stathub.aiursoft.cn │ 201 │ 1.495005 │ 56 │ /api/metrics │
└─────────────────────────┴───────┴─────────────────┴─────────────────┴───────────────────────────────────────┴─────────────┴──────────┴─────────────────────────────────┴────────┴─────────────┴────────────┴───────────────────────────────────────────────────────────────────────────────────────────────────────┘
30 rows in set. Elapsed: 0.004 sec. Processed 3.24 thousand rows, 407.31 KB (742.98 thousand rows/s., 93.37 MB/s.)
Peak memory usage: 1008.68 KiB.
As you can see, it's perfect.
Write Queries for the Most Common Data I Need
I can also do something else, such as ranking businesses by request count:
SELECT
host,
count() AS request_count
FROM logs.caddy_requests
GROUP BY host
ORDER BY request_count DESC
FORMAT Pretty;
I can also quickly identify crawlers that are scraping data from my system:
SELECT remote_ip, COUNT(*) AS count
FROM logs.caddy_requests
WHERE host = 'git.aiursoft.cn'
AND uri LIKE '/PublicVault%'
AND uri LIKE '%commit%'
GROUP BY remote_ip
ORDER BY count DESC;
I can also quickly see which specific IPs are being used by a particular business (host):
SELECT remote_ip, COUNT(*) AS count
FROM logs.caddy_requests
WHERE host = 'clickhouse.aiursoft.cn'
GROUP BY remote_ip
ORDER BY count DESC;
TOP 100 Most Popular Business Rankings, Request Counts per Host, Ranked by Unique IP Count:
SELECT
host,
uniqExact(remote_ip) AS unique_ip_count
FROM logs.caddy_requests
GROUP BY host
ORDER BY unique_ip_count DESC
LIMIT 100;
TOP 100 CPU-intensive business ranking, i.e., the total request processing time for each host:
SELECT
host,
sum(duration_ms) AS total_duration_ms,
count() AS requests
FROM logs.caddy_requests
GROUP BY host
ORDER BY total_duration_ms DESC
LIMIT 100;
Top 100 hottest interfaces, i.e., the interfaces with the highest number of requests.
SELECT
host,
splitByChar('?', uri)[1] AS path,
count() AS requests
FROM logs.caddy_requests
GROUP BY
host,
path
ORDER BY
requests DESC
LIMIT 100;
Top 100 Error Endpoints / Error Code Ranking: Identify the endpoints with the highest failure concentration or the most common 4xx/5xx status codes:
SELECT
host,
uri,
status,
count() AS errors
FROM logs.caddy_requests
WHERE 1=1
AND status >= 400
GROUP BY
host,
uri,
status
ORDER BY
errors DESC
LIMIT 100;
Top 100 Slow Interfaces, Based on Average:
SELECT
host,
splitByChar('?', uri)[1] AS path,
avg(duration_ms) AS avg_response_time_ms,
count() AS requests
FROM logs.caddy_requests WHERE status != '101'
GROUP BY
host,
path
ORDER BY
avg_response_time_ms DESC
LIMIT 100;
Top 100 slow interfaces, if using P95:
SELECT
host,
splitByChar('?', uri)[1] AS path,
quantile(0.95)(duration_ms) AS p95_response_time_ms,
count() AS requests
FROM logs.caddy_requests WHERE status != '101'
GROUP BY
host,
path
ORDER BY
p95_response_time_ms DESC
LIMIT 100;
P95 / P99 response latency trends. Beyond the average, P95 (95th percentile) and P99 better reflect "tail" latency:
SELECT
toStartOfInterval(ts, INTERVAL {rounding:UInt32} SECOND) AS t,
quantile(0.95)(duration_ms) AS p95_latency_ms,
quantile(0.99)(duration_ms) AS p99_latency_ms
FROM logs.caddy_requests
WHERE ts >= now() - {seconds:UInt32}
GROUP BY t
ORDER BY t
WITH FILL STEP {rounding:UInt32}
Request size P95 / P50 distribution; use quantiles to examine the distribution of response body sizes:
SELECT
toStartOfInterval(ts, INTERVAL {rounding:UInt32} SECOND) AS t,
quantile(0.5)(bytes_sent) AS p50_bytes,
quantile(0.95)(bytes_sent) AS p95_bytes
FROM logs.caddy_requests
WHERE ts >= now() - {seconds:UInt32}
GROUP BY t
ORDER BY t
WITH FILL STEP {rounding:UInt32}
Request the heatmap by "day of the week + hour" to observe traffic peaks throughout the week and across different times of day:
SELECT
toDayOfWeek(ts) AS weekday, -- 1=Monday … 7=Sunday
toHour(ts) AS hour,
count() AS cnt
FROM logs.caddy_requests
WHERE ts >= now() - {seconds:UInt32}
GROUP BY weekday, hour
ORDER BY weekday, hour
User‑Agent distribution: view which callers (browsers, crawlers, scripts, etc.) are accessing your service:
SELECT
user_agent,
count() AS cnt
FROM logs.caddy_requests
WHERE ts >= now() - {seconds:UInt32}
GROUP BY user_agent
ORDER BY cnt DESC
LIMIT 10
These visualizations help you gain deep insights into service performance and potential bottlenecks from multiple angles (hottest endpoints, tail latency, traffic by time period, error clustering, and client types).
Now that we have successfully read Caddy's log data from files and converted it into a format usable by ClickHouse, we can use Superset or other visualization tools to analyze this data and generate various reports and charts.
Investigating Hacker's Flood Attacks
A wave of hackers is currently attacking me. I'm using the newly built database to conveniently investigate the methods behind this attack:
Got it. The time window here is in UTC, so no timezone conversion is needed. Just use the following SQL directly:
1. Count requests by business (descending, UTC 2025‑07‑27 14:35–14:45)
SELECT
host,
count() AS requests
FROM logs.caddy_requests
WHERE ts BETWEEN
toDateTime('2025-07-27 14:35:00','UTC')
AND toDateTime('2025-07-27 14:45:00','UTC')
GROUP BY host
ORDER BY requests DESC;
2. Total traffic size at 10-second granularity (UTC 2025‑07‑27 14:35–14:45)
SELECT
toStartOfInterval(ts, INTERVAL 10 SECOND) AS t,
sum(bytes_sent) AS total_bytes
FROM logs.caddy_requests
WHERE ts BETWEEN
toDateTime('2025-07-27 14:35:00','UTC')
AND toDateTime('2025-07-27 14:45:00','UTC')
GROUP BY t
ORDER BY t;
3. Client IP list and corresponding hosts in descending order of request count (UTC 2025‑07‑27 14:35–14:45)
SELECT
remote_ip,
host,
count() AS requests
FROM logs.caddy_requests
WHERE ts BETWEEN
toDateTime('2025-07-27 14:35:00','UTC')
AND toDateTime('2025-07-27 14:45:00','UTC')
GROUP BY remote_ip, host
ORDER BY requests DESC;
4. Status Code Distribution (UTC 2025‑07‑27 14:35–14:45)
SELECT
status,
count() AS count
FROM logs.caddy_requests
WHERE ts BETWEEN
toDateTime('2025-07-27 14:35:00','UTC')
AND toDateTime('2025-07-27 14:45:00','UTC')
GROUP BY status
ORDER BY count DESC;
5. Distribution of the First 20 Character Prefixes of URIs (Descending, UTC 2025‑07‑27 14:35–14:45)
SELECT
substring(uri, 1, 20) AS path_prefix,
count() AS count
FROM logs.caddy_requests
WHERE ts BETWEEN
toDateTime('2025-07-27 14:35:00','UTC')
AND toDateTime('2025-07-27 14:45:00','UTC')
GROUP BY path_prefix
ORDER BY count DESC;
Build a Beautiful Dashboard
ClickHouse comes with a built-in dashboard. By organizing the SQL queries above, it is not difficult to create a very polished dashboard.
Grafana
With Grafana, you can also create even more beautiful dashboards.
Conclusion
If you need to continue with visualization, you can also use Apache Superset. However, with visualization to this extent, I have already obtained all the data I care about.
In the short term, this is more than sufficient for me to meet my needs.
Being able to visualize Caddy's traffic is very important for business development. If you don't know how the application is being used, no metric can evaluate the growth of the business. Overall, building this infrastructure is not difficult. Once it is set up, you can simply monitor the dashboards daily and enjoy the results!
Comments (0)
No comments yet. Be the first to leave one!