[Answered] Want to prune a lot of bot registrations with GMAIL adresses

[Hansi]

I am trying to find a way to sort out and delete hundreds of bot-registered members with gmail adresses.

I was hoping that i could do something like this:

UPDATE
`user`
SET membergroupids = 19
WHERE
`email`
LIKE '%@gmail.com'

to make all users with @gmail.com added to the additional user group "user that have gmail"

I do NOT want to move all "gmail" users to a PRIMARY usergroup because many of them should remain in the "registered" group, while others should remain in the "banned" primary group. If I change the primary group for all gmail users I would end up in a mess.

OK, i have tried the query above but with a more specific "LIKE" so only a single member have been added to the secondary group.

Yes i know this query would replace any other secondary groups, but no users with gmail are in any secondary group now.

What i would hope to do was to use the vbulletin PRUNE USERS function in admin to delete any user that is in secondary "gmail" group, have not logged in for a year, have no posts or whatever.

Unfortunately, the prune users will only look for members that have the choosen group as their primary.

Any ideas?

[Hansi]

Also, I found a feauture request in the tracker that I agree with.

The Prune/move function should be improved.

If you agree, please go to the request and add your vote (look for the vote link under the "operations" heading in the left panel).

http://tracker.vbulletin.com/browse/VBIV-12194

[Huskermax]

Voted.

[Andy]

I suggest deleting the spam accounts. Then prevent them from registering so you don't have to deal with them. Here are some tips.

https://www.vbulletin.com/forum/showthread.php/377906-How-to-prevent-spammers-from-registering-on-your-forum

[Hansi]

I suggest deleting the spam accounts. Then prevent them from registering so you don't have to deal with them. Here are some tips.

https://www.vbulletin.com/forum/show...-on-your-forum

Thank you for the suggestion.

Deleting the spam accounts is the goal yes. I dont want to spend two days doing it one by one. I want to play with sql and prune in a way so that I can do it with as few mouseclicks as possible.

I can not ban gmail, too many real users in that domain.

I have captcha and a registration question "that only humans can answer" that keep down the volume of new bot registrations.

(Yes, I have read all the good tips. My forum is more than 10 years old, even I have managed to learn a thing or two during the years :-) )

[Wayne Luke]

Use the Prune User tool in the Admin CP. If you run a SQL query to delete the record out of the user table, you're only doing 1/20th of the job and can destroy your forums beyond repair.

[Hansi]

Use the Prune User tool in the Admin CP. If you run a SQL query to delete the record out of the user table, you're only doing 1/20th of the job and can destroy your forums beyond repair.

Thank you Wayne. Actually, that is exactly what I am doing.

The problem is:

1: The PRUNE tool does not use any secondary usergroups in its search criteria
2: I dont want to make my "gmail-users usergroup" the primary usergroup

I am leaning towards trying to make a quick temporary patch to the prune code and change it so it uses the secondary usergroup field instead. Seem like a quick and dirty fix for now.

[Hansi]

Thank you for kicking my thinking in the right (or at least working) direction

admincp/user.php, changed

if ($vbulletin->GPC['usergroupid'] != -1)
	{
		$sqlconds = "WHERE user.usergroupid = " . $vbulletin->GPC['usergroupid'] . ' ';
	}

to

	if ($vbulletin->GPC['usergroupid'] != -1)
	{
		$sqlconds = "WHERE user.membergroupids = " . $vbulletin->GPC['usergroupid'] . ' ';
	}

and now I can do what I wanted to do :-)

[Hansi]

Now I found out, that by editing admincp/user.php, changing ">" to "<", I get the search to filter by "join date is after" instead of "join date is before"

Combining that with "Has not logged on for x days" I can limit the list to between the date the bots started "attacking" my forum and the date I closed the door by adding counter measures.

if ($joindateunix)
		{
			$sqlconds .= iif(empty($sqlconds), 'WHERE', 'AND') . " joindate > $joindateunix ";
		}

Example:
I want to pinpoint one month, the period May 01 2011 to May 31 2011
Today is February 16 2012

With my modified admincp/user.php, i enter:
Usergroup: gmail users
Has not logged in for x days: 250
Join date is before (means join date is AFTER with modded code) : Month May, Day 1, year 2011
Post is less than: 1 (or what you like)
Order by: latest activity (or what you like)


Or try:

usergroup: All usergropups
has not logged in for x days:0
Join date is before: (reversed to join date is AFTER with modded code) : Try a few weeks before todays date, depending on how many registrations you get per day
Posts is less than: 1
Order by: Registered or latest activity

Now you get a list of the most recently registered users, make sure to uncheck all boxes (unless you are VERY spambot infected) using the checkbox at the very top, and then recheck all boxes for users that are obvious bot registrations.

Kind of a moderation queue if used like that, but working like a "morning after pill" as you dont need to leave your users waiting for moderation before they can use the forum. Now you can "moderate" your new users once a week or once a month.

Let me know what you think!




5048 users with gmail down to 4167.
Almost 1000 bot registrations removed.
Trying to keep real users, even the "0 posts, not logged in for 365 days" users.
Next step would be emailing them and remove all users that "bounce" the email. Hopefully some will decide to revisit the forum. That will be another project...

[Hansi]

Moderator:
Change the thread prefix to "solved"?
Or can I do that?