Announcement

Collapse
No announcement yet.

SQL question about vB db

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

  • Panzer Max
    replied
    Blast, I just now saw your query, Retro. Sorry, man, I guess you have tackled this by now. I will say yes, we were able to write and test a query that allowed us to move all members who had signatures, with less than 2 posts, and had not logged in after xx number of years into the Spammer usergroup, and then delete them after browsing and checking.

    Leave a comment:


  • Lynne
    replied
    That would be a question better asked over on vbulletin.org, the modification site.

    Leave a comment:


  • RetroDreams
    replied
    I hate bumping old threads, but I'm starting to consider upgrading one of my older 3.x forums to 4.x. However, before I do, I want to get some things in order so my upgrade goes smoothing and my forum can move forward. I've been recently going through my users and found I have about 2,700 signature spammers -- folks that registered and have 0 posts but set a signature. I created a spammer usergroup but I don't want to go through hundreds of pages and manually move them, especially since you can't search on signature in the prune/move options. How could the above mentioned query be modifed so it can move users to the spammer usergroup so I could then easily use the prune/move feature?

    Leave a comment:


  • Panzer Max
    replied
    ...or, better yet, I see I can select from the spam group and delete in the Admin CP -> Users -> Prune / Move Users section, nice!!!

    Leave a comment:


  • Panzer Max
    replied
    Hmmm...Ok, Trevor, that sounds like what I need to do. I enabled the ACP SQL Queries in my config, I see I can use that to delete by usergroup...

    How does this sound: I can create a new usergroup called spamhos. Then I can run my join queries in phpMyAdmin, and see who has spam sigs, spam homepages, and spam visitor messages.
    Something like this
    SELECT U1.userid, U1.username, U1.homepage, S1.signature
    FROM user AS U1, usertextfield AS S1
    WHERE U1.userid = S1.userid
    AND U1.joindate >1291165261
    AND ( U1.lastactivity - U1.joindate ) < 86400
    AND U1.posts < 2
    AND S1. signature NOT LIKE ''
    ORDER BY U1.username
    I think that query will isolate the spammers who sign up with spam sigs and homepages and rarely return after joining, and real people who may not post but do return.

    Then I run a query to change their userid from 2 (registered) to 99 (spamhos). And then I can go into the ACP and delete all members of that usergroup.

    Would that be a good method? Will it also get their visitor messages?

    thanks!!

    Leave a comment:


  • Trevor Hannant
    replied
    It will remvoe them from all tables rather than what you choose to delete from - it's also supported...

    Leave a comment:


  • Panzer Max
    replied
    Nice, Steve. Is there a benefit to using Prune in ACP over just deleting from the db? I'm assuming using ACP will remove all records from all tables with the same userid ?
    I guess I could assign all the results I get with SQL queries to a "Spam" usergroup, then prune with ACP.

    Leave a comment:


  • Steve Machol
    replied
    Unless those account has something in common that you can search for in the Admin CP - there is no option to do that.


    Admin CP -> Users -> Prune / Move Users

    Leave a comment:


  • Panzer Max
    replied
    I'm guessing there are 2000+ spam acounts in our user table of 60,000, maybe as much as 10,000 spam accounts. Can I query and delete in mass via the ACP?

    Leave a comment:


  • Lynne
    replied
    Why are you not deleting the users via the admincp?

    Leave a comment:


  • Panzer Max
    replied
    Originally posted by Lynne View Post
    The signature is stored in usertextfield.signature.
    Oh, thanks, Lynne, you are right as usual!

    Zach, anything in particular I should be wary of? We are trying to ID all the spam accounts, and will be deleting this directly from the db. Is there a better way to do this? Cheers!

    Leave a comment:


  • Lynne
    replied
    The signature is stored in usertextfield.signature.

    Leave a comment:


  • Zachery
    replied
    He is adding it manually to every post and does not have an actual signature.

    Also please be insanely careful with messing with the db directly.

    Leave a comment:


  • Panzer Max
    started a topic SQL question about vB db

    SQL question about vB db

    Strange thing we came across today. While looking through the DB for spammers with spam signatures: I can see a spam member in the ACP, he has a spam signature. When I query the sigparsed table for that userid, it returns 0 results. In the User table, a query shows him. How can he have a signature yet it is not stored in sigparsed?

Related Topics

Collapse

Working...
X