raw.txt
No One
Actions

Authored By

	stwalkerster
	Jan 28 2019, 9:24 PM

Size

1 KB

Referenced Files

None

Subscribers

None

raw.txt
View Options

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

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

	commit
	/

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

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

	alter table asn add baseip varchar2(15)
	/
	alter table asn add cidr varchar2(2)
	/

	update asn set baseip = regexp_substr(network, '^[0-9.]+', 1, 1), cidr = regexp_substr(network, '[0-9.]+$', 1, 1) where 1=1;
	/
	commit
	/

	alter table asn add ipbin varchar2(32)
	/
	update asn set ipbin = 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')
	where 1=1
	/
	commit
	/


	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
	)
	select id, FORWARDEDIP, NETWORK, count(*) over (partition by NETWORK) as number_in_range, org
	from reqprefixes rp
	inner join asn a on a.ipbin = rp.binprefix and a.CIDR = rp.cidr
	order by 4 desc
	;

File Metadata

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

raw.txtNo OneActions

raw.txtView Options

File Metadata

Event Timeline

raw.txt
No One
Actions

raw.txt
View Options