Page Menu
Home
Phabricator
Search
Configure Global Search
Log In
Paste
P238
ACC checkuser aggregation query
Active
Public
Actions
Authored by
stwalkerster
on Jan 28 2019, 9:18 PM.
Edit Paste
Archive Paste
View Raw File
Subscribe
Mute Notifications
Tags
ACC
Subscribers
None
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/
-- network = CIDR mask; ASN = AS Number; Org = organisation name
/
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
),
data
as
(
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
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
/
Event Timeline
stwalkerster
created this paste.
Jan 28 2019, 9:18 PM
2019-01-28 21:18:59 (UTC+0)
stwalkerster
created this object with visibility "Public (No Login Required)".
stwalkerster
edited the content of this paste.
(Show Details)
Jan 28 2019, 9:24 PM
2019-01-28 21:24:44 (UTC+0)
stwalkerster
edited the content of this paste.
(Show Details)
Jan 28 2019, 11:40 PM
2019-01-28 23:40:28 (UTC+0)
stwalkerster
edited the content of this paste.
(Show Details)
Jan 28 2019, 11:43 PM
2019-01-28 23:43:12 (UTC+0)
Log In to Comment