How to remove spam requests?

[shi-gg]

One day a year ago, I got a shit ton of spammy requests (probably a (D)DoS against my site itself or targeted at my plausible instance itself)

Now, how would I go about deleting it?

[Debcharon]

Plausible events database in Clickhouse

root@hetzner-fsn1-002:~# docker container exec -it plausible-plausible_events_db-1 bash
root@8c7102c30aa3:/# clickhouse-client
ClickHouse client version 24.12.2.29 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 24.12.2.

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.

8c7102c30aa3 :) SHOW DATABASES;

SHOW DATABASES

Query id: 75220467-32d2-41f6-ad46-17fe96b69144

   ┌─name────────────────┐
1. │ INFORMATION_SCHEMA  │
2. │ default             │
3. │ information_schema  │
4. │ plausible_events_db │
5. │ system              │
   └─────────────────────┘

5 rows in set. Elapsed: 0.002 sec. 

8c7102c30aa3 :) USE plausible_events_db

USE plausible_events_db

Query id: a9d098bd-fb37-4edc-97d8-6e240251ed10

Ok.

0 rows in set. Elapsed: 0.003 sec. 

8c7102c30aa3 :) SHOW TABLES;

SHOW TABLES

Query id: 2571a69e-2c7d-456b-b94a-5ad93f73685b

    ┌─name───────────────────────┐
 1. │ events                     │
 2. │ events_v2                  │
 3. │ imported_browsers          │
 4. │ imported_custom_events     │
 5. │ imported_devices           │
 6. │ imported_entry_pages       │
 7. │ imported_exit_pages        │
 8. │ imported_locations         │
 9. │ imported_operating_systems │
10. │ imported_pages             │
11. │ imported_sources           │
12. │ imported_visitors          │
13. │ ingest_counters            │
14. │ location_data              │
15. │ location_data_dict         │
16. │ schema_migrations          │
17. │ sessions                   │
18. │ sessions_v2                │
19. │ sessions_v2_tmp_versioned  │
    └────────────────────────────┘

19 rows in set. Elapsed: 0.002 sec. 

Events data in events_v2 table and Sessions data in sessions_v2 tables

Export with format CSV

In container

root@8c7102c30aa3:/# clickhouse-client --query="SELECT * FROM plausible_events_db.events_v2" --format=CSVWithnames > ./output_events_v2.csv
root@8c7102c30aa3:/# clickhouse-client --query="SELECT * FROM plausible_events_db.sessions_v2" --format=CSVWithnames > ./output_sessions_v2.csv

In host

root@hetzner-fsn1-002:~# docker cp plausible-plausible_events_db-1:/output_events_v2.csv ./output_events_v2.csv
Successfully copied 7.09MB to /root/output_events_v2.csv
root@hetzner-fsn1-002:~# docker cp plausible-plausible_events_db-1:/output_sessions_v2.csv ./output_sessions_v2.csv
Successfully copied 5.59MB to /root/output_sessions_v2.csv

output_events_v2.csv

output_sessions_v2.csv

Then you can modify it or execute command directly in container clickhouse-client like below if you already backup database

ALTER TABLE events_v2 DELETE WHERE timestamp >= '2025-01-04 02:00:00' AND timestamp <= '2025-01-04 03:00:00';
ALTER TABLE sessions_v2 DELETE WHERE timestamp >= '2025-01-04 02:00:00' AND timestamp <= '2025-01-04 03:00:00';