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.
!lemmy_admin@lemmy.ml and a lot of SQL stuff in !lemmyperformance@lemmy.ml
I’ll x-post it over there.
Well, my bots haven’t done anything apparently, but there are a couple thousand of them. I’m surprised considering my instance wasn’t even listed anywhere lol. Have a query for safely deleting all but two accounts by chance?
Well- do note, it could very-well be normal user accounts too, which have not done anything at all.
Those queries show all users.
21,000 users with no comments, posts, or votes on an instance that’s never been advertised and isn’t on the community browser or thefederation… Yeah nah lol.
Can I just drop them from public.persons and move on?
I- wouldn’t recommend that, without knowing the schema/layout better.
You can, however
update public.persons set banned = 'true' where --criteria here