ブログ - XOOPSに登録してあるユーザをドメイン別に集計する
ユーザをドメイン毎に集計してみた．
目的は，スパムユーザの抽出．URLクリックに承認メールでログインできているユーザの中でも，大量登録によるスパムユーザと思われるユーザを推測． xoops_usersテーブルのemailフィールドにある@以降を抽出し，それをorderでソート，同じドメイン名を用いている行の件数をカウントする．ここでは便宜上，区切り文字の.を..に置き換えています．
どうみても日本人が使ってなさそうなので，削除．
目的は，スパムユーザの抽出．URLクリックに承認メールでログインできているユーザの中でも，大量登録によるスパムユーザと思われるユーザを推測．
mysql> select substring_index(email,'@',-1) as domain,count(uid) as count
-> from XOOPS_users group by domain order by count desc limit 20;
+--------------------------------------+-------+
| domain | count |
+--------------------------------------+-------+
| pazarlamadahisi..com | 57 |
| dust..harboxed..com | 16 |
| rambler..ru | 13 |
| pro..highnom..com | 12 |
| list..ru | 10 |
| alastermymann..ceramiccoffecups..com | 9 |
| gaofanglanqiuxie..com | 9 |
| sheep..insurple..com | 9 |
| diypillow..com | 7 |
| inbox..lv | 7 |
| seznam..cz | 7 |
| agent..recorated..com | 5 |
| onsalemall..top | 5 |
| o2..pl | 5 |
| bk..ru | 5 |
| filme-2015..eu | 5 |
| firststage1..info | 5 |
| marloni..com..pl | 5 |
| medispot..net | 5 |
| filmhd720p..co | 4 |
+--------------------------------------+-------+
20 rows in set (0.00 sec)
mysql>
どうみても日本人が使ってなさそうなので，削除．
delete from XOOPS__users where email like '%pazarlamadahisi..com';
delete from XOOPS_users where email like '%dustAharboxed..com';
delete from XOOPS__users where email like '%rambler..ru';
delete from XOOPS__users where email like '%pro..highnom..com';
delete from XOOPS__users where email like '%list..ru';
delete from XOOPS__users where email like '%alastermymann..ceramiccoffecups..com';
delete from XOOPS__users where email like '%gaofanglanqiuxie..com';
delete from XOOPS__users where email like '%sheep..insurple..com';
delete from XOOPS__users where email like '%diypillow..com';
delete from XOOPS__users where email like '%inbox..lv';
delete from XOOPS__users where email like '%seznam..cz';
delete from XOOPS__users where email like '%agent..recorated..com';
delete from XOOPS__users where email like '%onsalemall..top';
delete from XOOPS__users where email like '%o2..pl';
delete from XOOPS__users where email like '%bk..ru';
delete from XOOPS__users where email like '%filme-2015..eu';
delete from XOOPS__users where email like '%firststage1..info';
delete from XOOPS__users where email like '%marloni..com..pl';
delete from XOOPS__users where email like '%medispot..net';
delete from XOOPS__users where email like '%filmhd720p..co';
