How can I check which users have signatures?

[Lone Wolf]

I've noticed today that about 10% of my 2000 registered users are spam accounts which have unsavoury signatures or signatures with spam links.

Is there any way to do a search which allows me to only review those users with signatures so I know which ones to delete without checking each user individually?

 

[borbole]

I've noticed today that about 10% of my 2000 registered users are spam accounts which have unsavoury signatures or signatures with spam links.

Is there any way to do a search which allows me to only review those users with signatures so I know which ones to delete without checking each user individually?

Go to your phpmyadmin and choose the database that you have installed your xenforo forum. Then at the SQL tab run this query:

SELECT user.username, signatures.signature
FROM xf_user_profile AS signatures
LEFT JOIN xf_user AS user ON ( user.user_id = signatures.user_id )
ORDER BY user.username;

This will show a list of all your users and their signatures. If you want to delete signatures of users with 0 posts who are usually spammers, run this query:

UPDATE xf_user_profile 
LEFT JOIN xf_user ON (xf_user.user_id = xf_user_profile.user_id)
SET signature = ''
WHERE xf_user.message_count = 0;

If you are using another prefix for your tables, replace xf_ with your custom prefix at the queries above. Hope it helps.

 

[Paul B]

Then set up user group promotion so members with less than x posts can't enter a signature.

http://xenforo.com/help/user-group-promotions/

 

[Lone Wolf]

THanks Borbole and Brogan, I will be doing both those things

 

[DRE]

Awesome.

 

[Toxic]

is this possible to reset just the Registered User Group signatures to blank?

would it be something like this?

UPDATE xf_user_profile
LEFT JOIN xf_user ON (xf_user.user_id = xf_user_profile.user_id)
SET signature = ''
WHERE xf_user_group = 2;

 

[borbole]

is this possible to reset just the Registered User Group signatures to blank?

UPDATE xf_user_profile
LEFT JOIN xf_user ON (xf_user.user_id = xf_user_profile.user_id)
SET signature = ''
WHERE xf_user_group = 2;

would it be something like this?

The last line in your query is not correct. Try this:

UPDATE xf_user_profile
LEFT JOIN xf_user ON (xf_user.user_id = xf_user_profile.user_id)
SET signature = ''
WHERE xf_user.user_group_id = 2;

 

[Toxic]

Ty for the quick reply!

i've also done

UPDATE xf_user_profile
LEFT JOIN xf_user ON (xf_user.user_id = xf_user_profile.user_id)
SET about = ''
WHERE xf_user.user_group_id = 2;

UPDATE xf_user_profile
LEFT JOIN xf_user ON (xf_user.user_id = xf_user_profile.user_id)
SET homepage = ''
WHERE xf_user.user_group_id = 2;

so Registered users dont advertise until they are Members after 5 posts

 

[borbole]

You are welcome

Note that that would only clear the current about and the homepage fields. But it won''t prevent new users from filling those fields again. To do that you will need a custom mod.