XF 2.2 Mass delete bot accounts

[Facula]

I’m taking over administration of a forum that has had little maintenance done for a hwile

After a quick glance, 150k+ accounts and the majority are bot accounts that registered, posted a spammy profile account and never revisited. Over 1000 per week for who knows how long.

I’d like to be able to select these accounts that were created on any given day where their last visit date is the same and possibly filter by number of consecutive numbers in their username, things like that.

Is that something that has to be done through a set of database inquiries, or can it be done through the admin control panel somehow?

I’m going to take a guess that simply deleting these users out of the XF_user table would leave other tables with associated entries so I would have to also clean all those up and that’s kind of going down a rabbit hole so I’m looking for some direction on how to do this most efficiently

Thanks in advance

 

[Facula]

Also, for some reason they’re still using tapatalk, which is I’m guessing how these bot spammers got past any kind of captcha they had set up

 

[smallwheels]

Is that something that has to be done through a set of database inquiries, or can it be done through the admin control panel somehow?

I don't think that's possible via search or user criteria in ACP but rather with a custom direct db-query. This should on the other hand not be too complex to create. For getting rid of those users maybe it would be an idea to create a user group for them in ACP, add them to this user group as second user group via your custom db-script after you identified them and then batch delete users belonging to that group via ACP. Never done this but this is what first came to my mind what I would try.

 

[Facula]

That is a great idea, but there are also other fields involved whenever you change usergroups. If you look in the tables, there are BLOB fields with unique values depending on a user's commbination of usergroups, and other tables as well. If you simply add a user to one usergroup in ACP, it actually changes more like 3-5 table in the database.

So manual pruning of the database could leave "hangers" and have unintended consequences. That's my concern.

Now, if there were a way to clean up all those "hangers" after the fact (after doing what you suggested), then yes that might work.

 

[smallwheels]

That is a great idea, but there are also other fields involved whenever you change usergroups.

Fair point. The idea is to create a unique identifier directly in the db w/o messing anything up that you can then use later in ACP to create a filter list. Can therefore be anything, i.e. a custom field or the contents of it or a checkbox. Whatever creates the least collateral damage.

 

[Facula]

I think what needs to be done is an SQL query to ID the bot accounts, and then use that list to run a PHP script to add them to the usergroup via the ACP native interface. Then batch delete

 

[AndyB]

You might like this add-on:

https://xenforo.com/community/resources/auto-delete-users.10351/

 

[Facula]

You might like this add-on:

https://xenforo.com/community/resources/auto-delete-users.10351/

nailed it