SELECT user.userid , user.username , posts,signature,count(usernote.userid) as notes
FROM user,usertextfield LEFT JOIN usernote on usertextfield.userid=usernote.userid where user.userid=usertextfield.userid AND user.usergroupid=2 AND usertextfield.signature like '%www.%' AND (usertextfield.signature not like '%www.avforums.com%'
AND usertextfield.signature not like '%www.flickr%'
AND usertextfield.signature not like '%www.youtube%'
AND usertextfield.signature not like '%www.invelos%'
AND usertextfield.signature not like '%www.myspace%')
AND membergroupids not like '%31%' GROUP BY user.userid ORDER BY userid DESC
To show the signatures containing external links (with a few exceptions) and provide links through to see their posts, user notes and edit their profiles.
This is a very handy tool because while some signatures are still valid (links to personal web pages etc.), those with spammy signatures are very obvious and can be dispensed with easily.
A better system which alerted moderators would be a great bonus.
Obviously spammers register with links in their signatures. Some post a few times and only then edit their signatures to add the links. They try to be crafty. Most are from India or China.