Use PRUNE USERS with a slightly modified admincp/user.php to "moderate" NEW users

[Hansi]

(If you want to do what I describe in the subject, using prune/move users to get rid of NEW users instead of OLD users - keep reading, that part is at the end of my post)

I needed to remove a lot of bot registered users.

MANY of those used gmail.com accounts.

So I came up with two patches and found some SQL statements that can be run from the control panel. And found that for me, they made the prune/move users VERY useful!

To pinpoint gmail.com accounts, i created a new usergroup. I named it "have gmail adress"

For reasons caused by how things are in my forum, i did not want to set that usergroup as a primary usergroup, but as a secondary.

The following SQL code will put all users with gmail in that secondary usergroup. BUT! It is ugly and will remove the users from any other SECONDARY usergroups.
Such as "facebook connected" or whatever you might use in your forum. So this part might not be suitable for you.

NOTE! Change the 19 to the usergroup ID that you have in your own forum!

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

The following patch of admincp/user.php will make the move/prune users script use the secondary group instead of the primary
Both patches are below the // ##### start list users for pruning ##### part of the script.

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'] . ' ';
	}

By editing admincp/user.php, changing ">" to "<", I get the search to filter by "join date is AFTER" instead of "join date is BEFORE"

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

to

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

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.

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 (or just use "All usergroups" if you are not doing the "gmail filter" that I needed)
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!

[Trevor Hannant]

I am sorry but we don't allow PHP code changes to be posted here. You would need to post this at vBulletin.org.