Extracting `ufw` Logs from VPS for Analysis

The goal is to extract ufw firewall logs from UpCloud server to my local machine and convert it to a DuckDB database.

Copying Logs on the Server

My admin user has no root privileges on the server, so I first need to copy all ufw logs from /var/log/ to a /tmp/ufw-logs folder.

mkdir /tmp/ufw-logs
sudo cp /var/log/ufw* /tmp/ufw-logs
sudo chown -R $USER:$USER /tmp/ufw-logs

Now get those logs from the server:

rsync -avz UpCloud:'/tmp/ufw-logs/ufw*' ~/upcloud_ufw_logs/

Now parse those logs and convert them into a .duckdb file.

pyenv activate analytics
python ~/Coding/python-coding/ingest_ufw.py ~/upcloud_ufw_logs ~/ufw.duckdb

Launch DuckDB and start poking around.

duckdb ~/ufw.duckdb
D: ufw: CALL start_ui();