Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
Fork of
Ranges /24 per number of open proxy blocks
by
MarioGom
This query is marked as a draft
This query has been published
by
Blablubbs
.
Toggle Highlighting
SQL
SET STATEMENT max_statement_time = 600 FOR WITH proxy_blocks AS ( SELECT ip24, c FROM ( SELECT ip24, count(*) AS c FROM ( SELECT DISTINCT ipb_address, REGEXP_REPLACE(ipb_address, '\.[0-9]+(/[0-9]+)?$', '.0/24') AS ip24 FROM ipblocks INNER JOIN comment ON (ipblocks.ipb_reason_id = comment.comment_id) WHERE TRUE AND ipb_user = 0 AND ipb_address NOT LIKE '%:%' -- exclude IPv6 AND ipb_address NOT LIKE '%/24' AND ( FALSE OR comment_text LIKE '%roxy%' OR comment_text LIKE '%roxies%' OR comment_text LIKE '%NOP%' OR comment_text LIKE '%VPN%' OR comment_text LIKE '%colo%' OR comment_text LIKE '%webhost%' ) AND ipb_address NOT LIKE '49.228.%' -- exclude problematic Thai range AND ipb_address NOT LIKE '175.177.%' -- exclude problematic Japanese range ) AS q1 GROUP BY ip24 ) AS q2 WHERE TRUE AND c >= 2 ), recent_ranges AS ( SELECT DISTINCT REGEXP_REPLACE(actor_name, '\.[0-9]+$', '.0/24') AS ip24 FROM recentchanges_userindex AS recentchanges INNER JOIN actor_recentchanges AS actor ON recentchanges.rc_actor = actor.actor_id WHERE TRUE AND actor_user IS NULL -- exclude registered users AND actor_name NOT LIKE '%:%' -- exclude IPv6 ) SELECT proxy_blocks.ip24, c FROM proxy_blocks INNER JOIN recent_ranges ON proxy_blocks.ip24 = recent_ranges.ip24 WHERE TRUE -- AND proxy_blocks.ip24 NOT IN (SELECT ipb_address FROM ipblocks WHERE ipb_user = 0 AND ipb_address LIKE '%/24') ORDER BY c DESC, proxy_blocks.ip24 ASC
By running queries you agree to the
Cloud Services Terms of Use
and you irrevocably agree to release your SQL under
CC0 License
.
Submit Query
Stop Query
All SQL code is licensed under
CC0 License
.
Checking query status...