Bulk delete users by email address?

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Soliloquy
    New Member
    • Jan 2007
    • 5

    Bulk delete users by email address?

    I just took over a forum where the previous owner did not bother to delete spam accounts and am trying to clean it up. I tried to use the Prune tool but the long list of results crashes my browser. I can search for email addresses that match my criteria (for instance, I found over 1,000 spammers from a .cn domain alone) but each has to be deleted individually. Is there any way to speed this up, or at least paginate the Prune tool results?
  • Lats
    Senior Member
    • Mar 2002
    • 3671

    #2
    Try this query...
    Code:
    UPDATE
     user
    SET
     usergroupid = X
    WHERE 
     email
    LIKE '%.cn'
    Change X to a new usergroup you're going to prune.

    You will then be able to Prune/Move users in that usergroup in ACP -> Users -> Prune/Move Users ->.
    Lats...

    Comment

    • Dunhamzzz
      Senior Member
      • Jul 2008
      • 140
      • 3.8.x

      #3
      Great stuff, I just use the above query to delete all spammers using the mail.ru email service, out of 1012 of them none of them had any posts! GRRRR.

      All gone now thankfully Here was the query I used, 18 was my new usergroup ID:
      Code:
      UPDATE
       `vb_user`
      SET
       `usergroupid` = 18
      WHERE 
       `email`
      LIKE '%@mail.ru'

      Comment

      • Pocket Aces
        Member
        • Mar 2007
        • 99
        • 4.2.x

        #4
        I have similar concern and its better to dig this up rather than create a new topic to have a better reference.

        Code:
        UPDATE
         user
        SET
         usergroupid = X
        WHERE
          email
        LIKE '%.cn'
        We have hotmail aliases users spamming our forums. How do we set a join date filter in the above code so only those who registered in the last 7 days will be included?
        vBulletin 4.2.5 | PHP 7.0.31 | MariaDB 10.2.17

        Comment

        • Lats
          Senior Member
          • Mar 2002
          • 3671

          #5
          Try this...
          Code:
          UPDATE
          user
          SET
          usergroupid = X
          WHERE
            email
          LIKE '%.cn'
          AND
           joindate > UNIX_TIMESTAMP('2013-10-07 : 18:15:00')
          Lats...

          Comment

          • markoroots
            Member
            • May 2014
            • 42
            • 4.2.x

            #6
            Hi guys,
            I have a problem...
            My old forum used the PHPBB board.
            So when it was imported in VB board, maybe the users folder in the database wasn't changed (by the webmaster) with the default VB folder, so that now I have the users folder named in this way: "seo_phpbb_users"

            For this I launched this query:

            UPDATE
            `seo_phpbb_users`
            SET
            `group_id` = 38
            WHERE
            `user_email`
            LIKE '%@mail.ru'


            but it say: "Interested lines 0"

            So nothing it happens.

            Can someone help me please

            Comment

            • Lynne
              Former vBulletin Support
              • Oct 2004
              • 26255

              #7
              There would not be a table called "seo_phpbb_users" that would be used by vbulletin. It would be "seo_phpbb_user" (no 's' on the end) AND the field would be called just 'email' not 'user_email'. So, are you sure you are looking at the right table?

              Please don't PM or VM me for support - I only help out in the threads.
              vBulletin Manual & vBulletin 4.0 Code Documentation (API)
              Want help modifying your vbulletin forum? Head on over to vbulletin.org
              If I post CSS and you don't know where it goes, throw it into the additional.css template.

              W3Schools <- awesome site for html/css help

              Comment

              • markoroots
                Member
                • May 2014
                • 42
                • 4.2.x

                #8
                Hi Linne, I have the table with the "s" and not without... strange...
                but is this a table (without "s") of Vulletin or is an old table imported from phpbb you think?

                Comment

                • Lynne
                  Former vBulletin Support
                  • Oct 2004
                  • 26255

                  #9
                  If you have a user table with an s, then it isn't a vbulletin table at all. vBulletin would not work without a user table without an s (it requires a "user" table).

                  Please don't PM or VM me for support - I only help out in the threads.
                  vBulletin Manual & vBulletin 4.0 Code Documentation (API)
                  Want help modifying your vbulletin forum? Head on over to vbulletin.org
                  If I post CSS and you don't know where it goes, throw it into the additional.css template.

                  W3Schools <- awesome site for html/css help

                  Comment

                  • Tourmeister
                    Member
                    • Sep 2005
                    • 80
                    • 3.0.8

                    #10
                    I have been getting massive waves of spammers registering on my site recently, like hundreds per day. When a user registers on my site, I have a few extra profile fields that MUST be filled out before they can complete the registration. ONE of the patterns for spammers is that they will often put in a single capital letter for the required field that simply asks, "What is 2 + 2?" How do I figure out the column name in the database so I can run a query like this,

                    Code:
                    UPDATE
                    user
                    SET
                    usergroupid = 36
                    WHERE
                      (desired column name)
                    LIKE 'A'
                      OR 'B'
                      OR 'C'
                    ...
                      OR 'Z'
                    so that any user that has a single capital letter in that field will get moved to the usergroupid = 36 so that I can do a bulk delete rather than having to delete each account manually one at a time?
                    Scott Friday
                    Two Wheeled Texans

                    Comment

                    • Tourmeister
                      Member
                      • Sep 2005
                      • 80
                      • 3.0.8

                      #11
                      Upon a bit of poking around, I have figured out that I am dealing with two different tables in the database. First is the "user" table and second is the "userfield" table, the latter being the place where extra profile fields are stored. I have also figured out that the column name I want to check in the "userfield" table is called "field23". However, I am unsure how the query above needs to be modified so I can check the "field23" column in the "userfield" table and then update the "usergroupid" for the same users in the "user" table. No doubt, they are tied to each other using the "userid" field in both tables, I just don't know how to get the correct syntax because I don't really know much about MySQL beyond the general concept of how a database works.
                      Scott Friday
                      Two Wheeled Texans

                      Comment

                      • Paul M
                        Former Lead Developer
                        vB.Com & vB.Org
                        • Sep 2004
                        • 9886

                        #12
                        Try
                        Code:
                        UPDATE user
                        JOIN userfield
                        USING (userid)
                        SET usergroupid = 36
                        WHERE  
                        field23 LIKE 'A'
                        OR field23 LIKE 'B'  
                        OR field23 LIKE 'C'
                        ...  
                        OR field23 LIKE 'Z'
                        Baby, I was born this way

                        Comment

                        • Lats
                          Senior Member
                          • Mar 2002
                          • 3671

                          #13
                          Or a slightly shorter version...
                          Code:
                          UPDATE user
                          JOIN userfield
                          USING (userid)
                          SET usergroupid = 36
                          WHERE  
                          field23 
                          REGEXP BINARY '[A-Z]'
                          Lats...

                          Comment

                          widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                          Working...