Bulk suspend users based on criteria

[omarfilip]

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.

[codinghorror (Jeff Atwood)]

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

[omarfilip]

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.

Auto-suspend inactive users

A very simple plugin which automatically suspends users after they haven’t logged in for a while. It has a very narrow use case, but thought I’d share it in case someone else finds it useful. The plugin simply uses the built-in suspension functionality, so once the users are suspended you can safely uninstall the plugin and they will remain suspended. Options available: [image] Installing a plugin

[blake (Blake Erickson)]

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

[omarfilip]

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?

[blake (Blake Erickson)]

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.

[omarfilip]

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?

https://github.com/discourse/discourse/blob/master/app/models/user.rb#L1175-L1176

[pfaffman (Jay Pfaffman)]

Add

 .count

To the end of that line.

[omarfilip]

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?

[blake (Blake Erickson)]

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)

[omarfilip]

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.

[blake (Blake Erickson)]

You probably need to add a DISTINCT somewhere.

[omarfilip]

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

[blake (Blake Erickson)]

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

[riking (Kane York)]

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] }

[omarfilip]

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

[bartv (Bart)]

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```

[simon]

@omarfilip

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

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