# Various question about cleaning up an imported vbulletin
**URL:** https://meta.discourse.org/t/various-question-about-cleaning-up-an-imported-vbulletin/161643
**Category:** Support
**Created:** [August 21, 2020, 9:29am UTC](https://meta.discourse.org/t/various-question-about-cleaning-up-an-imported-vbulletin/161643 "2020-08-21T09:29:45Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@Canapin](https://meta.discourse.org/u/Canapin)
#### Post date: [August 21, 2020, 9:29am UTC](https://meta.discourse.org/t/various-question-about-cleaning-up-an-imported-vbulletin/161643/1 "2020-08-21T09:29:45Z")
Hi,
I imported a 20 years old forum to Discourse.
It has many unused or spam accounts and spam messages.
I’d like to do a cleanup.
I set up all my users to trust level 0.
I tried to know how many users have never posted any message with this [data explorer](https://meta.discourse.org/t/32566?silent=true) query:
```SQL
SELECT COUNT (DISTINCT user_id) from posts
```
It counts 28530.
I tried to know how many total users I have:
```SQL
SELECT COUNT (DISTINCT id) FROM users
```
It counts 180000 (vBulletin stats said we indeed had 180000 accounts)
I triggered the **CleanUpInactiveUsers** Sidekiq job.
Only a few hundred users were removed. I looked at one of these unused profiles and saw no activity: no post, no topic… However, Discobot sent them a message after I imported all the users from vBulletin. It was 5 days ago.
So I Discourse settings, I set **clean up inactive users after days** to 1.
I trigger **CleanUpInactiveUsers** Sidekiq job again.
I lost about 1000 users.
I still have 178000 users and I know that most of them are empty and unused profiles with no message.
Any idea why they aren’t removed by CleanUpInactiveUsers?
Plus, since there are many spams accounts and messages. Is it possible to trigger spam detection on existing users and messages, and clean all that up too?
---
### Author:  [@david](https://meta.discourse.org/u/david)
#### Post date: [August 21, 2020, 1:51pm UTC](https://meta.discourse.org/t/various-question-about-cleaning-up-an-imported-vbulletin/161643/2 "2020-08-21T13:51:11Z")
> [@Canapin](#):
>
> I trigger **CleanUpInactiveUsers** Sidekiq job again.
> I lost about 1000 users.
The job has a limit of 1000 per run, to avoid clogging the sidekiq queue
>
You could run it from the rails console like
```plaintext
Jobs::CleanUpInactiveUsers.new.execute({})
```
And then put it in a loop like
```plaintext
100.times do
Jobs::CleanUpInactiveUsers.new.execute({})
puts "Done iteration. Total user count #{User.count}"
end
```
> [@Canapin](#):
>
> Is it possible to trigger spam detection on existing users and messages, and clean all that up too?
What kind of spam detection were you thinking? Akismet?
---
### Author:  [@Canapin](https://meta.discourse.org/u/Canapin)
#### Post date: [August 21, 2020, 2:05pm UTC](https://meta.discourse.org/t/various-question-about-cleaning-up-an-imported-vbulletin/161643/3 "2020-08-21T14:05:20Z")
Thanks for the clarification!
> [@david](#):
>
> What kind of spam detection were you thinking? Akismet?
Honestly, I don’t know. I don’t know what Discourse uses to detect and prevent spam.
Also I believe a fair number of my spammers posted messages on public users’ profiles, a feature that doesn’t exist on Discourse.
These messages were imported on Discourse as “regular” topics, with no category and no title, making them easy to identify.:
Example of a spam profile:
I don’t want to delete **all** these titleless messages, most being harmless and potentially containing information that some users would like to get back
What I’d like to do is delete users that posted **only titleless topics** and remove the user’s topics too.
Could such a thing be done fairly easily with rails commands?
---
### Author:  [@david](https://meta.discourse.org/u/david)
#### Post date: [August 21, 2020, 2:11pm UTC](https://meta.discourse.org/t/various-question-about-cleaning-up-an-imported-vbulletin/161643/4 "2020-08-21T14:11:44Z")
> [@Canapin](#):
>
> What I’d like to do is delete users that posted **only titleless topics** and remove the user’s topics too.
> Could such a thing be done fairly easily with rails commands?
It might take a little experimentation, but yes. Something like this might work as a starting point:
```ruby
User.find_each do |user|
untitled_topic_count = user.topics.where(title: "").count
titled_topic_count = user.topics.where.not(title: "").count
if untitled_topic_count > 0 && titled_topic_count == 0
# delete the topics and/or the user
end
end
```
---
### Author:  [@Canapin](https://meta.discourse.org/u/Canapin)
#### Post date: [September 9, 2020, 8:42pm UTC](https://meta.discourse.org/t/various-question-about-cleaning-up-an-imported-vbulletin/161643/5 "2020-09-09T20:42:12Z")
For the record, here are my conditions:
In my case, a spammer must have:
- More than 1 post
- at least 1 titleless topic
- No titled topic
- The same number of posts as the number of topics (since a topic IS a post)
So I just added conditions:
```ruby
User.find_each do |user|
untitled_topic_count = user.topics.where(title: "").count
titled_topic_count = user.topics.where.not(title: "").count
topic_count = untitled_topic_count + titled_topic_count
post_count = user.posts.count
if post_count > 1 && untitled_topic_count > 0 && titled_topic_count == 0 && post_count == topic_count
puts "SPAMMER ?"
end
end
```
Let’s hope that won’t target legit users, but that _seems_ safe so far, looking randomly at targets. 😉
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [October 9, 2020, 8:43pm UTC](https://meta.discourse.org/t/various-question-about-cleaning-up-an-imported-vbulletin/161643/6 "2020-10-09T20:43:24Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.