Cleaning Up Member Database

[PopUpSteve]

Phil,

What I would like to do is purge some inactive members from the system. Since mid-January of 2013 we have been getting a lot of new members but I believe a good number of these are spoofs. Many register, confirm and are never seen again. Most have an on-time of zero (0). I've spot checked the IPs and the user's stated location (I.E. State & Country), and they match. So whomever was doing this was being very detailed. Some were created within minutes of each other so it is very suspicious. I think the selection script below will weed out these spoofs and some older inactive members as well. I know you loaded a mod to clean out those old inactive members and clear the logs but I don’t know if it will take the parameters I've come up with. What do you think? Can I just use these same parameters to delete the records? Or would that mess-up the logs?

Using these parameters today would purge some 13,432 members.

SELECT *
FROM `smf_members`
WHERE `posts` = 0
AND `date_registered` > 1230789599
AND `date_registered` < 1385856000
AND `last_login` < 1385856000
AND `total_time_logged_in` <= 3600
AND `id_group` = 0

So it's where posts = 0
AND date_registered Greater than 12/31/2008
AND date_registered Less than 12/1/2013
AND last_login Less than 12/1/2013
AND total_time_logged_in is Less than or Equal to 1 Hour
AND id_group = 0

 

[pgordemer]

Line 5 should be < not >

 

[pgordemer]

I would caution you on doing it this way as all you are going is deleting the user record from the table, but not all the other related records that hold the pointers to messages, themes, settings. So there is going to be a bunch of orphaned records in other databases. I do not know if the internal routines will clean up them.

 

[PopUpSteve]

Line 5 should be < not >

Thanks, that is how I have it in the working model. Don't know how I reversed it in the example.

I'll have to look around for the "Delete Member" routine, copy it, and automate it.

One thought would be to mark the 13,432 members as being in the "To Be Purged" member group and then use that clean-up routine to remove all members in that group.

 

[pgordemer]

take a look in /home/popupportal/public_html/tools/cleanup.php and look at that code, it does all the cleanup, so you can probably hard code it.

 

[PopUpSteve]

Thanks

 

[PopUpSteve]

What I'm thinking is to use my parameters and put all those members who meet them in a new member-group called "To Be Purged". Then use the "purge inactive members" to purge only the members in that member-group.

I put one of these members under the "To Be Purged" member-group (RustyParts). If I use the settings as shown in the attachment, then only that one member should be deleted, correct?

I think the one thing that is putting me off is the "Process a maximum of XXXX members". Does that mean it will only process that number of members (the default being 4000) or that number in the first pass? And if it needs the total number of members (currently 37,562), will that blow-out the system resources?

 

[pgordemer]

I will have to look at the code, but I think it means that it will only delete 4000 of that group.

 

[PopUpSteve]

I posted in the thread on SMF for the mod: http://www.simplemachines.org/community/index.php?topic=383900.msg3656307#msg3656307