Skip Navigation
Lemmy @lemmy.ml
HTTP_404_NotFound @lemmyonline.com

Queries for instance admins, for detecting bots and spam users.

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.

13 comments
13 comments