(An Untitled Masterwork)
ActivePublic
Actions

Authored by stwalkerster on Oct 21 2019, 5:59 PM.

Tags

Referenced Files

	F251264: raw.txt
	Oct 21 2019, 5:59 PM

Subscribers

None

	create table REQUEST ( ID NUMBER(10), FORWARDEDIP VARCHAR2(20) )
	/

	truncate table REQUEST
	/

	-- import data from ACC: select id, forwardedip from request where status = 'Checkuser' and emailconfirm = 'Confirmed' and forwardedip not like '%,%';

	commit
	/

	create table ASN ( NETWORK VARCHAR2(18) not null, ASN NUMBER(30) not null, ORG VARCHAR2(4000) )
	/

	create table blocks (target varchar2(18), anononly varchar2(1), reason varchar2(4000))
	/

	-- import data from ASN: https://dev.maxmind.com/geoip/geoip2/geolite2/

	commit
	/

	create materialized view asndata as
	with asnextra as (
	select network, org, regexp_substr(network, '^[0-9.]+', 1, 1) baseip, regexp_substr(network, '[0-9.]+$', 1, 1) cidr from asn
	)
	select network, org, baseip, cidr, lpad(to_bin(to_number(regexp_substr(BASEIP, '\d+', 1, 1))), 8, '0')
	\|\| lpad(to_bin(to_number(regexp_substr(BASEIP, '\d+', 1, 2))), 8, '0')
	\|\| lpad(to_bin(to_number(regexp_substr(BASEIP, '\d+', 1, 3))), 8, '0')
	\|\| lpad(to_bin(to_number(regexp_substr(BASEIP, '\d+', 1, 4))), 8, '0') as ipbin
	from asnextra;
	/
	create materialized view blockdata as
	with blockextra as (
	select
	reason
	, anononly
	, case when target like '%/%' then regexp_substr(target, '^[0-9.]+', 1, 1) else target end as baseip
	, case when target like '%/%' then regexp_substr(target, '[0-9.]+$', 1, 1) else '32' end as cidr
	from blocks
	)
	select
	reason
	, anononly
	, baseip \|\| '/' \|\| cidr as target
	, baseip, cidr, lpad(to_bin(to_number(regexp_substr(BASEIP, '\d+', 1, 1))), 8, '0')
	\|\| lpad(to_bin(to_number(regexp_substr(BASEIP, '\d+', 1, 2))), 8, '0')
	\|\| lpad(to_bin(to_number(regexp_substr(BASEIP, '\d+', 1, 3))), 8, '0')
	\|\| lpad(to_bin(to_number(regexp_substr(BASEIP, '\d+', 1, 4))), 8, '0') as ipbin
	from blockextra;
	/



	call dbms_mview.refresh('asndata');
	/

	call dbms_mview.refresh('blockdata');
	/

	CREATE OR REPLACE VIEW vw_asn AS
	WITH
	reqipbin AS
	(
	SELECT
	r.ID
	, r.FORWARDEDIP
	, lpad(to_bin(to_number(regexp_substr(r.FORWARDEDIP, '\d+', 1, 1))), 8, '0')
	\|\| lpad(to_bin(to_number(regexp_substr(r.FORWARDEDIP, '\d+', 1, 2))), 8, '0')
	\|\| lpad(to_bin(to_number(regexp_substr(r.FORWARDEDIP, '\d+', 1, 3))), 8, '0')
	\|\| lpad(to_bin(to_number(regexp_substr(r.FORWARDEDIP, '\d+', 1, 4))), 8, '0') AS IP_BIN
	FROM request r
	),
	reqprefixes as (
	SELECT ipb.id, ipb.FORWARDEDIP,ipb.IP_BIN, rpad(substr(ipb.IP_BIN, 0, pf.column_value), 32, '0') binprefix, pf.column_value as cidr
	FROM reqipbin ipb CROSS JOIN table(sys.odcinumberlist(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)) pf
	),
	data as (
	select id, FORWARDEDIP, NETWORK, count(*) over (partition by NETWORK) as number_in_range, org
	from reqprefixes rp
	inner join asndata a on a.ipbin = rp.binprefix and a.CIDR = rp.cidr
	and rp.cidr >= 16
	)
	select
	id, FORWARDEDIP, NETWORK, number_in_range, org,
	'<tr><td>' \|\| id \|\|
	'</td><td>' \|\| FORWARDEDIP \|\|
	'</td><td>' \|\| network\|\|
	'</td><td>' \|\| number_in_range\|\|
	'</td><td>' \|\| org\|\|'</td><td>' \|\| '<a href="https://accounts.wmflabs.org/acc.php?action=zoom&id=' \|\| id \|\| '">' \|\| id \|\| '</a>' \|\|
	'</td></tr>' as html
	from data
	where number_in_range > 1
	order by number_in_range desc, FORWARDEDIP asc
	;

	CREATE OR REPLACE VIEW vw_block AS
	WITH
	reqipbin AS
	(
	SELECT
	r.ID
	, r.FORWARDEDIP
	, lpad(to_bin(to_number(regexp_substr(r.FORWARDEDIP, '\d+', 1, 1))), 8, '0')
	\|\| lpad(to_bin(to_number(regexp_substr(r.FORWARDEDIP, '\d+', 1, 2))), 8, '0')
	\|\| lpad(to_bin(to_number(regexp_substr(r.FORWARDEDIP, '\d+', 1, 3))), 8, '0')
	\|\| lpad(to_bin(to_number(regexp_substr(r.FORWARDEDIP, '\d+', 1, 4))), 8, '0') AS IP_BIN
	FROM request r
	),
	reqprefixes as (
	SELECT ipb.id, ipb.FORWARDEDIP,ipb.IP_BIN, rpad(substr(ipb.IP_BIN, 0, pf.column_value), 32, '0') binprefix, pf.column_value as cidr
	FROM reqipbin ipb CROSS JOIN table(sys.odcinumberlist(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)) pf
	),
	data as (
	select id, FORWARDEDIP, target, count(*) over (partition by target) as number_in_range, anononly, reason
	from reqprefixes rp
	inner join blockdata a on a.ipbin = rp.binprefix and a.CIDR = rp.cidr
	and rp.cidr >= 16
	)
	select
	id, FORWARDEDIP, target, number_in_range, anononly, reason,
	'<tr><td>' \|\| id \|\|
	'</td><td>' \|\| FORWARDEDIP \|\|
	'</td><td>' \|\| target\|\|
	'</td><td>' \|\| number_in_range\|\|
	'</td><td>' \|\| anononly\|\|'</td><td>' \|\| '<a href="https://accounts.wmflabs.org/acc.php?action=zoom&id=' \|\| id \|\| '">' \|\| id \|\| '</a>' \|\|
	'</td></tr>' as html
	from data
	where number_in_range > 1
	order by number_in_range desc, FORWARDEDIP asc
	;

	select * from vw_block order by number_in_range desc, target;

	select * from vw_asn;

Event Timeline

stwalkerster created this paste.Oct 21 2019, 5:59 PM

stwalkerster created this object with visibility "Public (No Login Required)".

stwalkerster updated the paste's language from autodetect to sql.

(An Untitled Masterwork)ActivePublicActions

Event Timeline

(An Untitled Masterwork)
ActivePublic
Actions