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, '