# Bulk suspend users based on criteria
**URL:** https://meta.discourse.org/t/bulk-suspend-users-based-on-criteria/76804
**Category:** Support
**Created:** [December 27, 2017, 8:06am UTC](https://meta.discourse.org/t/bulk-suspend-users-based-on-criteria/76804 "2017-12-27T08:06:27Z")
**Posts on this page:** 19
**Page:** 1
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [December 27, 2017, 8:06am UTC](https://meta.discourse.org/t/bulk-suspend-users-based-on-criteria/76804/1 "2017-12-27T08:06:27Z")
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.
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [December 27, 2017, 8:07am UTC](https://meta.discourse.org/t/bulk-suspend-users-based-on-criteria/76804/2 "2017-12-27T08:07:46Z")
I believe @jomaxro or @blake have done this for a customer before.
---
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [December 27, 2017, 8:17am UTC](https://meta.discourse.org/t/bulk-suspend-users-based-on-criteria/76804/3 "2017-12-27T08:17:58Z")
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](https://meta.discourse.org/t/auto-suspend-inactive-users/65444):
>
> 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. [https://github.com/davidtaylorhq/discourse-auto-suspend](https://github.com/davidtaylorhq/discourse-auto-suspend) Options available: [Installing a plugin](https://meta.discourse.org/t/install-a-plugin/19157)
---
### Author:  [@blake](https://meta.discourse.org/u/blake)
#### Post date: [December 27, 2017, 4:00pm UTC](https://meta.discourse.org/t/bulk-suspend-users-based-on-criteria/76804/4 "2017-12-27T16:00:00Z")
If you would like to use the rails console to bulk suspend users, something like this should do the trick:
```plaintext
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
```
---
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [December 27, 2017, 6:43pm UTC](https://meta.discourse.org/t/bulk-suspend-users-based-on-criteria/76804/5 "2017-12-27T18:43:40Z")
Thanks, Blake.
Since I want to suspend users who were last seen at a defined date, will this work instead of `nil`?
```ruby
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?
---
### Author:  [@blake](https://meta.discourse.org/u/blake)
#### Post date: [December 27, 2017, 6:47pm UTC](https://meta.discourse.org/t/bulk-suspend-users-based-on-criteria/76804/6 "2017-12-27T18:47:49Z")
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.
---
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [December 28, 2017, 1:00am UTC](https://meta.discourse.org/t/bulk-suspend-users-based-on-criteria/76804/7 "2017-12-28T01:00:45Z")
Borrowing from @pfaffman’s syntax [here](https://meta.discourse.org/t/bulk-deactivation-of-inactive-users/64562/2), this seems like it will work:
```rails
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?
>
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [December 28, 2017, 11:35am UTC](https://meta.discourse.org/t/bulk-suspend-users-based-on-criteria/76804/8 "2017-12-28T11:35:37Z")
Add
```
.count
```
To the end of that line.
---
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [January 1, 2018, 12:27am UTC](https://meta.discourse.org/t/bulk-suspend-users-based-on-criteria/76804/9 "2018-01-01T00:27:14Z")
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
```plaintext
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?
---
### Author:  [@blake](https://meta.discourse.org/u/blake)
#### Post date: [January 1, 2018, 4:12pm UTC](https://meta.discourse.org/t/bulk-suspend-users-based-on-criteria/76804/10 "2018-01-01T16:12:25Z")
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:
```plaintext
users = User.joins(:group_users).where.not(group_users: {group_id: 41}).where(id: 1..Float::INFINITY, admin: false)
```
---
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [January 1, 2018, 6:18pm UTC](https://meta.discourse.org/t/bulk-suspend-users-based-on-criteria/76804/11 "2018-01-01T18:18:14Z")
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.
---
### Author:  [@blake](https://meta.discourse.org/u/blake)
#### Post date: [January 1, 2018, 7:31pm UTC](https://meta.discourse.org/t/bulk-suspend-users-based-on-criteria/76804/12 "2018-01-01T19:31:10Z")
You probably need to add a `DISTINCT` somewhere.
---
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [January 1, 2018, 7:45pm UTC](https://meta.discourse.org/t/bulk-suspend-users-based-on-criteria/76804/13 "2018-01-01T19:45:52Z")
Thanks Blake - distinct does the trick:
```plaintext
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.
```plaintext
users = User.joins(:group_users).where(group_users: {group_id: 41}).distinct.count
=> 749
```
---
### Author:  [@blake](https://meta.discourse.org/u/blake)
#### Post date: [January 1, 2018, 8:36pm UTC](https://meta.discourse.org/t/bulk-suspend-users-based-on-criteria/76804/14 "2018-01-01T20:36:26Z")
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
---
### Author:  [@riking](https://meta.discourse.org/u/riking)
#### Post date: [January 1, 2018, 8:50pm UTC](https://meta.discourse.org/t/bulk-suspend-users-based-on-criteria/76804/15 "2018-01-01T20:50:04Z")
```plaintext
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] }
```
---
### Author:  [@omarfilip](https://meta.discourse.org/u/omarfilip)
#### Post date: [January 1, 2018, 9:32pm UTC](https://meta.discourse.org/t/bulk-suspend-users-based-on-criteria/76804/16 "2018-01-01T21:32:51Z")
Thanks, Kane.
The count comes up correct:
```plaintext
=> #
```
Do I just append the remainder of the code in the console to execute it, like this?
```rails
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
```
---
### Author:  [@bartv](https://meta.discourse.org/u/bartv)
#### Post date: [July 6, 2018, 12:40pm UTC](https://meta.discourse.org/t/bulk-suspend-users-based-on-criteria/76804/17 "2018-07-06T12:40:15Z")
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?
````plaintext
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```
````
---
### Author:  [@simon](https://meta.discourse.org/u/simon)
#### Post date: [June 7, 2019, 11:44pm UTC](https://meta.discourse.org/t/bulk-suspend-users-based-on-criteria/76804/18 "2019-06-07T23:44:21Z")
> [@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:
```ruby
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](https://meta.discourse.org/t/bulk-suspend-users-based-on-criteria/76804/4), but add a suspension reason and a line to enter a record into the `UserHistory` table:
```ruby
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
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [July 7, 2019, 11:44pm UTC](https://meta.discourse.org/t/bulk-suspend-users-based-on-criteria/76804/19 "2019-07-07T23:44:26Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.