Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
Fork of
Last 30d IPv6 vandalism ratio per subnets 2019 (fiwiki)
by
Zache
This query is marked as a draft
This query has been published
by
Jmk
.
Based on similar tool by Zache for IPv4 vandalism. distinct_addresses_c = how many different addresses in the IPv6 range contributed edits.
Toggle Highlighting
SQL
use fiwiki_p; SELECT reverts_c, edits_c, distinct_addresses_c, reverts_c/edits_c AS ratio, reverts_ip_range AS ip_range FROM ( SELECT SUM(1) AS reverts_c, CONCAT(SUBSTR(REGEXP_SUBSTR(comment_text,'/([0-9A-F]{1,4}:[0-9A-F]{1,4}:)'),2),"*") AS reverts_ip_range FROM recentchanges, comment WHERE rc_comment_id=comment_id AND comment_text REGEXP "(ontributions|uokkaukset)/([0-9A-F]{1,4}:){7,7}[0-9A-F]{1,4}" AND rc_source="mw.edit" GROUP BY reverts_ip_range ) AS reverts LEFT JOIN ( SELECT SUM(1) AS edits_c, COUNT(DISTINCT rc_user_text) AS distinct_addresses_c, CONCAT(SUBSTR(REGEXP_SUBSTR(rc_user_text,'^([0-9A-F]{1,4}:[0-9A-F]{1,4}:)'),1),"*") AS edits_ip_range FROM recentchanges WHERE rc_user_text REGEXP "^([0-9A-F]{1,4}:){7,7}[0-9A-F]{1,4}" AND rc_user = 0 AND rc_source="mw.edit" group by edits_ip_range ) AS edits ON edits_ip_range=reverts_ip_range ORDER BY reverts_c desc LIMIT 100;
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...