Page MenuHomePhabricator
Files F251264

raw.txt
No One
Actions

Authored By
stwalkerster
Oct 21 2019, 5:59 PM
Size
4 KB
Referenced Files
None
Subscribers
None

raw.txt
View Options

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&amp;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&amp;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;

File Metadata

Mime Type
text/plain; charset=utf-8
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
161231
Default Alt Text
raw.txt (4 KB)

Event Timeline

stwalkerster.co.uk · helpmebot.org.uk · eyeinthesky.im · Phabricator · TeamCity · Nexus · Cloud