The only thing I have that I’d not be mortified to share (only mildly embarrassed) is a very heavy, probably quite inefficient SQL script to grab the vote aggregates for a community grouped by actor id.

Input: Local Community ID

Output:

• Actor ID
• Publish/create date of the actor
• Number of post upvotes given in the community
• Number of post downvotes given in the community
• Number of comment upvotes given in the community
• Number of comment downvotes given in the community
• Number of posts in the community
• Number of comments in the community

If you want the SQL, send me a DM or reply and I can DM it to you. For the action / scoring logic, you’ll probably be better off writing your own since my scripts are a bit of a mess. The basic flow, though, is:

The output from the SQL is fed into other scripts which compute a “participation ratio” for each given actor id. That’s just a fancy way of saying if they have no/few posts/comments in the community, give out X downvotes for every upvote given (if any), the number of interactions is above a minimum threshold, and their account is older than a minimum defined number of days, it’ll add those actor_ids to a queue file.

The thresholds are quite generous, though they can be tightened if needed. One thing to note is that downvotes received in the community is not factored in; just the number of submissions to the community (if any).

The queue file is then processed to ban those users from that community with a preset reason.

That runs via cron about once a day on a list of community IDs in a config file. I usually check the modlog every morning to review any bans that happen overnight (if any). Previously, I would manually review and issue any bans by hand using the list of actor IDs it generated (which has since became the queue file).

The scripts are a bunch of Bash scripts that I’ve put together over a long period of time for various purposes, and many are daisy-chained together. I’ve been meaning to re-write it in Python now that I have a better idea of what I want it to do, but I just haven’t gotten around to it yet.

I will say that the script doesn’t have to take action on a per-community basis that often. Most of the bans issued are to accounts that just downvote everything (which are detected by another, similar script). When those “mass downvote” accounts are banned, they’re also banned from each local community (I don’t enforce that to any communities I mod elsewhere).

@ptz@dubvee.org

PTZ has mentioned he might have a SQL based solution

I just use the api scripts now, which are really really slow.