Utilizador:RobinHood70/Abuse Filter Spam Procedure

This will document all procedures and relevant queries used to block spammers based on the Abuse Filter logs.

1. Find prolific IP spammers since last check:<pre style="width:; white-space:-moz-pre-wrap; white-space:-pre-wrap; white-space:-o-pre-wrap; white-space:pre-wrap; word-wrap:break-word; ">SELECT afl_ip, afl_user_text, COUNT(*) Cnt FROM abuse_filter_log WHERE afl_timestamp >= 20140305000000 #Date of last check GROUP BY afl_ip, afl_user_text HAVING Cnt >= 50 #May need to be adjusted depending on frequency of check ORDER BY afl_ip;
2. Scan list for patterns of IPs that may need range-blocked. Check against existing blocks and/or re-run above query going back further to determine if a wider block range is appropriate.
3. If desired, enter IP into RIPE Database Search to determine CIDR range for ISP (see "route" entry at bottom of results).
4. Check proposed range blocks against, for example, Special:Contributions/192.168.0.0/16 to ensure there are no legitimate edits coming from the range. If there are, narrow the range or use specific IP blocks. (Requires ContribsRange gadget to be enabled.)
5. Block remaining IPs normally (do not block user names from those IPs unless checked in CheckUser logs).
6. Check for prolific named spammers since last check:<pre style="width:; white-space:-moz-pre-wrap; white-space:-pre-wrap; white-space:-o-pre-wrap; white-space:pre-wrap; word-wrap:break-word; ">SELECT afl_user_text, COUNT(*) Cnt FROM abuse_filter_log WHERE afl_ip != afl_user_text AND afl_timestamp >= 20140305000000 #Date of last check GROUP BY afl_user_text HAVING Cnt >= 10 #May need to be adjusted depending on frequency of check ORDER BY afl_user_text;
7. Double-check edits in case a user has triggered the filter while attempting legitimate edits.
8. Block remaining users normally.
9. Occasionally check for patterns of single blocks that may need a range block:<pre style="width:; white-space:-moz-pre-wrap; white-space:-pre-wrap; white-space:-o-pre-wrap; white-space:pre-wrap; word-wrap:break-word; ">SELECT CAST(LEFT(ipb_address, LOCATE('.', ipb_address, LOCATE('.', ipb_address) + 1) - 1) AS char (7)) Address16, COUNT(*) Cnt FROM ipblocks WHERE ipb_user = 0 AND ipb_range_start = ipb_range_end GROUP BY Address16 ORDER BY Cnt DESC;