Announcement

Collapse
No announcement yet.

Mass Delete Users through PhpMyAdmin

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • hqarrse
    replied
    Just thought I'd add to an old thread the queries I used to get rid of spam users based on a homepage or signature set, but no other activity. It is basically what was said by meissen above, but a little more thorough. It assumes that everyone gets a single welcome PM when they join. If you don't do that then change the pmcount in the query to 0. The select is not perfect but I did quite a few iterations and changes before settling on it. For me at least I think there were very few false positives.

    Backup your users table and create a new spammers usergroup.

    run this and download the output, then using a text editor put in the format "name","name","name". I used username rather than userid as it gave me a warmer feeling.

    Code:
    SELECT u.* FROM mydb.vb_user u join mydb.vb_usertextfield utf on u.userid = utf.userid where u.posts = 0 and u.lastpost = 0 and u.pmunread = 1 and (length(utf.signature) > 0 or length(u.homepage) >0) and utf.signature like "%[URL%" limit 10000;
    once these are in a nice long quoted string, run:

    Code:
    update mydb.vb_user set usergroupid = MY_NEW_USERGROUP where userid in (COPY AND PASTE IN LONG LIST FROM ABOVE);
    now go to admincp->users->prune as described above and 1000 at a time delete them.

    Leave a comment:


  • meissen
    replied
    Figured it out on my own. Thanks.

    Leave a comment:


  • meissen
    replied
    How about a compromise to Andy and Zachery --

    Instead of a query to delete the users, could you help me build a query which will add users to an additional custom usergroup if their post count is 0 and their signature is like "[url=" ? That way I can then use the Mass Move / Prune function in AdminCP to view all the users that are in that custom usergroup and prune them through AdminCP.

    Leave a comment:


  • John Lester
    replied
    Did you look at vbulletin.org to see if there were any mods or plugins that might do what you want?

    Leave a comment:


  • Mr_Running
    replied
    I agree, it's silly.
    Maybe provide a notice for logged in users with zero posts to post within a certain amount of time, say within the next 30 days or the account will be deleted. If there is an Introduction/welcome forum...it may become very active.

    Leave a comment:


  • meissen
    replied
    Maybe your experience in forum administration is different, but I've been running another forum which I started in 2005 and every time I deleted inactive users I would always get multiple e-mails from the lurkers who tried to login and couldn't anymore. I guess I'll have to do it as Andy described, just seems silly to me that I have to delete a ton of innocent accounts.

    Leave a comment:


  • Mr_Running
    replied
    Originally posted by meissen View Post
    Right so delete 4800 users just to clean out 1000 spammers.....
    Chances are the other 3800 are s p a m accounts if they have not logged in the pass 90 days and have zero posts.
    Think of it like this a forum is mainly for discussion and yes lurkers read. If they haven't logged in over 90 days and have zero post... so be it ! Clean up as the new Admin and get in under control and maybe it will only need to be done once.

    Leave a comment:


  • meissen
    replied
    Right so delete 4800 users just to clean out 1000 spammers.....

    Leave a comment:


  • Andy
    replied
    Originally posted by meissen View Post

    I don't want to delete legitimate users who happened to register on the site and just haven't posted yet.

    If I do a Prune / Move search of all users who registered Prior to September 1st 2012 with 0 posts and haven't logged in in the past 5 days, it brings up 4800 users. With the Signature like [ url= criteria, that would only be 1000 users to sift through...
    Change the criteria to delete all those with 0 posts and not logged in for the past 90 days. Then do the same thing in another 90 days from now.

    Also here's a thread explaining the best way to prevent future spammers.

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

    Leave a comment:


  • meissen
    replied
    Originally posted by Zachery View Post
    at least 5, and that is besides the point.

    Why can't you find these users in prune / move?
    The criteria for finding them is based on what's in their signature which is not available function in Prune / Move... It'd be nice if the Prune / Move page had the same search capabilities as the "Search for Users" page. Likewise I realize I can go through one by one and select who to delete and who not to delete, but again this is just as tedious since I would have to either open each user's profile individually to verify they're legit or a spammer and then (un)check the box accordingly.

    Originally posted by Andy View Post
    Why not just delete all with 0 posts and older than say 30 days, or some other number of days since last activity?
    I recently took the forum over and upon going through the settings I realized they did not have E-mail Verification set up... users were able to register and immediately post and/or edit their profile. I've resolved the settings issue to lock the forum down, but even after deleting a few hundred users manually I still have over a thousand to still delete ....

    I don't want to delete legitimate users who happened to register on the site and just haven't posted yet.

    If I do a Prune / Move search of all users who registered Prior to September 1st 2012 with 0 posts and haven't logged in in the past 5 days, it brings up 4800 users. With the Signature like [url= criteria, that would only be 1000 users to sift through...
    Last edited by meissen; Fri 7th Sep '12, 3:34am.

    Leave a comment:


  • Andy
    replied
    Originally posted by meissen View Post
    Unfortunately the Move / Prune Users does not have the ability for me to search and find these users. My criteria for searching for the members is post count = 0 and Signature like [ URL= -- I need the Signature search which isn't available on the Move / Prune Users page.

    Again - these users have zero posts, how many tables can they be tied into?
    Why not just delete all with 0 posts and older than say 30 days, or some other number of days since last activity?

    Leave a comment:


  • Zachery
    replied
    at least 5, and that is besides the point.

    Why can't you find these users in prune / move?

    Leave a comment:


  • meissen
    replied
    Unfortunately the Move / Prune Users does not have the ability for me to search and find these users. My criteria for searching for the members is post count = 0 and Signature like [URL= -- I need the Signature search which isn't available on the Move / Prune Users page.

    Again - these users have zero posts, how many tables can they be tied into?

    Leave a comment:


  • Andy
    replied
    As Zachery indicated, use the Prune Users function in the Admin CP to delete these members.

    AdminCP > Users > Move / Prune Users

    Leave a comment:


  • Zachery
    replied
    Please do not delete users directly from the database, you're going to cause yourself a huge headache and support nightmare.

    Have you considered pruning or just banning these users?

    AdminCP > Users > Move / Prune Users

    Leave a comment:

Related Topics

Collapse

Working...
X