Hi everyone,

I am relatively new to xoops, and I am trying to help a slightly absent site admin to delete spam user accounts. I am in the webmasters group, and also have direct access and full admin rights on the linux server where the website is hosted from.

We have 9000+ user accounts.
Of these, maybe 4000 are spam accounts, and another 4000 are accounts that are registered but never posted anything.

Therefore, to clean up the site, we want to delete all users who have never posted anything (never made a post to the forum).

I have used the "Find Users" section in the 'administrators menu' to go through and delete a ton of users already. However, this is a major PAIN, because it only displays a max of 50 users at a time (even if you put in 99 users as the number to display [and 99 is the max for that field anyway]), AND after you delete those 50, it doesn't even take you back to the list of users, so you have to do the freaking search again.


So, here are my questions:

How can I delete lots of users at a time?
Specifically, how to delete all users who have never posted before?

I know some SQL so if I can get access to the database that would be awesome. However, I do not see any links anywhere to see the database. (I do not see a link to 'phpMyAdmin' anywhere, even though i see it mentioned a couple times on this forum.)


And secondly, after I have deleted all these spam users accounts, how can I delete all forum threads by "Anonymous" users?
(ie: users who no longer exist in the system because I have deleted them)


I would like to avoid searching for the users by hand, avoid deleting them in small groups of 50 or 100 (if I could show 1000+ users per page on the "find users" screen that would solve it for me).


many thanks,
Veqryn


(i think we are using version 2.4._ of xoops)

Don't worry too much about those spamusers. Spam posters use an account once, after that in general they don't come back.

deleting user accounts is bad approach.
Quote:

Also you can run some SQL commands to set all users with no post to that banned group.
This is the right way.

What do you mean by no post? Since in many systems, anonymous user groups do not have posting privileges so they might end up in the "banned" user group.

Or did you mean those who refuse to post... like they are only there to quietly take notes and report on what others are posting?

The Xoops 'users' table has a row 'actkey' which would normally be populated when a new user clicks the confirmation email, so for starters you could delete the records for unconfirmed accounts. The SQL would be something like:



In PHPMyAdmin you can then just tick all the selected records and delete them. You might wind up with UID 1 being followed by UID 99 etc. but this should not be a problem.

You could also run a similar query on confirmed accounts to see if the 'posts' row was empty. You could delete those users too, but as Xoobaru points out, a lack of posts doesn't mean a user is a problem.

Accounts that are actively spamming your forum should be added to a banned group as Irmtfan mentions, but it is routine maintenance task really. Moving thousands of users into a banned group via SQL is well beyond me!

If you decide to tackle this, you will need access to PHPMyAdmin or some similar database tools. Your web host should provide this.

Obviously, you will do a full DB backup first.

Quote:

He want to delete users with no post and it is his decision not mine.
every webmaster have his own rules. im against of any delete from database.
In my website all kinds of "delete" = "remove from database" is prohibited.
Therefore for example 30 forum moderators should not delete any post or topic permanently (even spam posts) and must use "soft delete" newbb option for sending them to deleting area. Also accounts will be send to "banned" group.
Quote:
I think the above is wrong because the actkey is filled when a user is registered but is still inactive.

I remember i wrote a script 6 years ago with the help of some developers to do it automatically in a form.

see here:
https://xoops.org/modules/newbb/viewtopic.php?post_id=229033#forumpost229033

maybe still work in 2.5.5 but i dont test.

Quote:

Sorry. I didn't realise that. How are activated users identified in the DB?

I remember seeing your script a long time ago. Be interesting to know if it still works.

xoops core use "level" field for that purpose.
level=0 => user is inactive (before click on activation link)
level=1 => user is active (after click on activation link)
level=2 => user is disable
i used the above in that script.

but because the webmaster (not a good way at all) can change the above setting for each user in admin area IMO now the above is not completely reliable if you want to delete users that never click on activation link

IMO now the best way to find users that never activated is using "last_login" field.
"last_login" =0 => user never login to your website.
you can use both level=0 and last_login=0 to insure that user never activate and never login. that is the perfect way to insure you dont delete real users.

Anyway, Im against of deleting any user from database because you will free that email address if the user was spammer.

Any delete from database is dangerous because you have not any way to restore it.

Many thx for that update Irmtfan. Really useful info.

I agree you should avoid freeing up spammers email addresses, but although running a SQL query for 'level' and 'last login' isn't a problem, in the OP's case it may return several thousand rows of non-sequential user IDs.

I'm struggling to work out how you would get those ID numbers into the users group link table to associate them with a 'banned' group?. I also can't help thinking it would be better to delete those records to improve efficiency of the database?

A 'User Purge' (with a banned/delete option) would be a good core feature IMHO.

I dont know deleting inactive users will reduce queries and enhance the website performance or not.

assigning inactive users to "banned" group instead of deleting them is good idea and it can implement very easy.
Now im focus on newbb bug finding. then i can take a look at that script.