Bulk suspend users based on criteria

Can I suspend all users who were seen prior to some date, for example December 31, 2016, and add reason for the suspension as membership expired?

I have ~1700 of these.

2 Likes

I believe @jomaxro or @blake have done this for a customer before.

2 Likes

Hmm, I think I can use @david’s plugin for this, unless it’s better to do it in the rails console?

I only need to do this once as part of cleaning up stuff from an imported forum.

5 Likes

If you would like to use the rails console to bulk suspend users, something like this should do the trick:

suspend_till = DateTime.new(2057,1,1)
users = User.where(last_seen_at: nil, id: 1..Float::INFINITY, admin: false)
users.each do |u|
  u.suspended_till = suspend_till
  u.suspended_at = DateTime.now
  u.save!
end
5 Likes

Thanks, Blake.

Since I want to suspend users who were last seen at a defined date, will this work instead of nil?

users = User.where(last_seen_at: < '2016-12-31', id: 1..Float::INFINITY, admin: false)

Also, what does id: 1..Float::INFINITY do in this case?

Yes something like that will work but I don’t think the less than sign works with the hash syntax but you can play around in the rails console first to see the results before you execute the commands in the loop. The infinity thing is just a hack to write greater than 1 so you don’t disable system and discobot who have negative user ids.

3 Likes

Borrowing from @pfaffman’s syntax here, this seems like it will work:

users = User.where("last_seen_at < '2016-12-31'", id: 1..Float::INFINITY, admin: false)

How would I get a count of these users in order to cross-check with an exported csv list of users before executing it?

I would like to include a reason for the suspension, but the users schema doesn’t list it, so what to use?

1 Like

Add

 .count

To the end of that line.

4 Likes

I’ve realized that using last_seen_at for the filter is going to suspend too many current members to fix manually, so I’d like tweak this slightly to suspend all users who are not members of a group.

My attempt with

users = User.where.not(group_id: 41, id: 1..Float::INFINITY, admin: false)

failed because

ERROR: column users.group_id does not exist

What should I use instead?

There is no group_id column on users. There is instead a separate group_users table and groups table. I usually connect to pg directly to list all the tables and poke around, but you can list all the tables in the rails console with ActiveRecord::Base.connection.tables

You will need do do a join like:

users = User.joins(:group_users).where.not(group_users: {group_id: 41}).where(id: 1..Float::INFINITY, admin: false)
3 Likes

There seems to be some funny counting happening.

The count of users not in group_id: 41 comes up as 4830, but there are a total of 2597 users. group_id:41 has 748 members.

Oddly, the dashboard shows 2597 users, but a csv export shows 2572.

You probably need to add a DISTINCT somewhere.

2 Likes

Thanks Blake - distinct does the trick:

User.joins(:group_users).where.not(group_users: {group_id: 41}).distinct.count
=> 2572

But something about my .not is off as it’s counting all users, not just the non-members of the group. The number should be 1823 instead of 2572.

 users = User.joins(:group_users).where(group_users: {group_id: 41}).distinct.count
=> 749

Sorry I’m on my phone but I think the not is turning into a ‘!=‘ and I think we need to do a “not in” query

1 Like
results = ActiveRecord::Base.exec_sql("SELECT id FROM users WHERE NOT EXISTS ( SELECT 1 FROM group_users AS gu WHERE gu.group_id = 41 AND gu.user_id = users.id )")
users = results.map { |row| User.find row[:id] }
2 Likes

Thanks, Kane.

The count comes up correct:

=> #<PG::Result:0x000055b571a732e0 status=PGRES_TUPLES_OK ntuples=1823 nfields=1 cmd_tuples=1823>

Do I just append the remainder of the code in the console to execute it, like this?

suspend_till = DateTime.new(2057,1,1)
results = ActiveRecord::Base.exec_sql("SELECT id FROM users WHERE NOT EXISTS ( SELECT 1 FROM group_users AS gu WHERE gu.group_id = 41 AND gu.user_id = users.id )")
users = results.map { |row| User.find row[:id] }
users.each do |u|
  u.suspended_till = suspend_till
  u.suspended_at = DateTime.now
  u.save!
end
2 Likes

I could use a little help with this; I’m trying to suspend all users in a specific group. When I run the following script it generates a long list of users, runs for some time but the users aren’t actually updated. What am I doing wrong?

suspend_till = DateTime.new(2057,1,1)
users = User.joins(:group_users).where(group_users: {group_id: 49})
users.each do |u|
  u.suspended_till = suspend_till
  u.suspended_at = DateTime.now
  u.save!
end```

I spent some time figuring out how to do this. The easiest approach I found was to use the StaffActionLogger class to add an entry to the UserHistory Table. To use the StaffActionLogger class you need to initialize an object with an admin user from your site. For example, on my site, my user ID is 1, so I initialize the object and assign it to a variable with:

logger = StaffActionLogger.new(User.find(1)) # use your user ID instead of 1 here.

With an initialized StaffActionLogger, you can then use Blake’s code, but add a suspension reason and a line to enter a record into the UserHistory table:

suspend_till = DateTime.new(2057,1,1)
reason = 'Your Suspension Reason'
users = User.where(last_seen_at: nil, id: 1..Float::INFINITY, admin: false)
users.each do |u|
  u.suspended_till = suspend_till
  u.suspended_at = DateTime.now
  u.save!

  logger.log_user_suspend(u,reason)
end

The suspension reason will be displayed on the user’s card and profile page. Suspended users will see the reason if they attempt to login to the site. This will also add an entry to your Staff Action logs for each suspended user.

One thing to note, if you are running the above code in the rails console, you’ll need to initialize the logger variable before copying the rest of the code into the console

5 Likes

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.