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.