Since, everything done on behalf of your instance is logged, detecting if you have a large number of bots, or invalid users isn't that challenging.
These queries can be executed via docker exec -it
, via remoting into the container, via pg query tools, or via pgadmin.
For listing all comments performed by users on your instance (This includes comments made remotely):
SELECT
p.actor_id
, p.name
, c.content as comment
FROM public.comment c
JOIN public.person p on p.id = c.creator_id
WHERE
p.local = 'true'
AND p.admin = 'false' -- Exclude Admins
;
For listing all posts created, by users, from your instance-
SELECT
p.actor_id
, c.name AS title
, c.body as body
FROM public.post c
JOIN public.person p on p.id = c.creator_id
WHERE
p.local = 'true'
AND p.admin = 'false' -- Exclude Admins
;
Lastly, here is a query to identify users who consistently upvotes or downvotes the same user over and over.
SELECT
p.id
, p.name
, p.actor_id
, cr.name as creator
, count(1)
FROM public.comment_like l
JOIN public.comment c on c.id = l.comment_id
JOIN public.person p on p.id = l.person_id
JOIN public.person cr on cr.id = c.creator_id
WHERE
p.id != cr.id
AND p.local = 'true'
AND p.admin = 'false' -- Exclude Admins
GROUP BY p.id, p.name, p.actor_id, cr.name
ORDER BY count(1) desc
;
If- anyone has idea of other queries which can be created for detecting suspicious activity, please LMK.
Edit- added where clause to exclude admins. If your admins are spambots, you have bigger issues to worry about.
I cannot confirm, nor deny.
But, I will say, once upon a time, before the days of netflix, if you wanted to watch things, you needed to spend a fuckload of money, to watch it on cable, with commercials every 10 minutes.... or, you drove to a blockbuster. So, you either did that, or you obtained the movie/tv/etc, via a torrent.
Then, netflix came along, gave you a ton of content, at a reasonable price. And- then, there wasn't really much of an advantage to obtaining media via other alternative means. So, netflix took over by storm, and piracy went way down.
Then, everyone wanted a piece of the action. So, then Hulu, Netflix, Amazon Prime, Disney Plus, HBO+, ESPN+, (And insert 50 other network-specific streaming services) jumped into the fray. Then, they all made exclusive streaming contracts. So, if you watch a handful of things, you would need a handful of streaming service subscriptions.
And- again, the alternative option of piracy, became the better option, as you can watch whatever the f- you want, WHENever you want, without having to pay for 50 different subscriptions every month, just to watch a TV series, which they decide to cancel after the 2nd season.
If the fucking scumbags didn't get greedy in the first place, we wouldn't be in this situation. But, no, everyone wanted an extremely generous piece of the pie, and now everything has went to shit again. Fuck those guys. Isn't like the actual actors/writers staring in movies gets any of the money anyways.