Batch deleting spam accounts

[Jon12345]

Due to a recent influx of spam, I am considering batch deleting users with zero posts, ising the ACP>Users>Batch update users section. The ones I want to target are those who registered this year, with zero posts and who put something in the occupation field. it is because the spammer has been sticking links in their profile.

I cannot see anyway to search based on the website link being filled, or anything like that. So, can you use a wildcard filter in the Occupation field, so that it will show any user who has filled it in? Is there any other way to filter based on if the website field is filtered?

 

[chillibear]

We typically do that kind of thing from the database and pop our victims into new temporary group, which we can then search on. Then purge them after checking through.

However depending on exactly what set of conditions you want you might find the extra criteria in this (paid) add-on: https://xenforo.com/community/resources/user-criteria-by-xon.9073/ will be enough for you.

DB query wise if you say wanted to get a list of members who have 0 posts and have a website set and registered in the last 12 months you would want a query like this (EDIT - although I think occupation is now in the fields table not user profile, I was copying from some old notes):

select
  xf_user_profile.user_id,
  xf_user.username,
  DATE_FORMAT(FROM_UNIXTIME(xf_user.register_date),'%Y-%m-%d') as registered,
  DATE_FORMAT(FROM_UNIXTIME(xf_user.last_activity),'%Y-%m-%d') as last_active,
  xf_user.message_count as posts,
  xf_user_profile.website
from
  xf_user_profile left join xf_user on xf_user_profile.user_id = xf_user.user_id
where
  xf_user_profile.website != ""
and
  xf_user.message_count = 0
and
  xf_user.register_date > UNIX_TIMESTAMP(DATE_ADD(NOW(), INTERVAL -12 MONTH))
and
  xf_user.user_state = 'valid';

I've made it a bit more verbose for you, should be fairly intuitive however. You could tweak the occupation/website condition as needed. Output will be like:

+---------+----------------------+------------+-------------+-------+----------------------------------------------+
| user_id | username             | registered | last_active | posts | website                                      |
+---------+----------------------+------------+-------------+-------+----------------------------------------------+
|   94083 | bob                  | 2024-02-19 | 2024-02-20  |     0 | https://ewr1.example.com/police/vehicles/    |
|   94091 | sally                | 2024-02-20 | 2024-02-22  |     0 | https://example.com                          |
|   94094 | dan                  | 2024-02-21 | 2024-02-21  |     0 | https://sjc1.example.com/caliblog/index.html |

...

If I get a minute later I'll update with how to then pop that list into a group (sorry meeting to go to) - which you can then search on to apply your batch editing to in the ACP. Still as it stands it'll get you a list which may be of interest![/code]

 

[cdub]

it is because the spammer has been sticking links in their profile.

I stopped allowing new members to edit and post in their profile. They get a promotion when they post a couple of threads and receive some likes.