Page Menu
Home
Phabricator
Search
Configure Global Search
Log In
Paste
P225
(An Untitled Masterwork)
Active
Public
Actions
Authored by
stwalkerster
on Nov 10 2018, 4:56 PM.
Edit Paste
Archive Paste
View Raw File
Subscribe
Mute Notifications
Tags
None
Referenced Files
F105229:
Nov 10 2018, 4:56 PM
2018-11-10 16:56:36 (UTC+0)
Subscribers
None
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
),
ipcidr
AS
(
SELECT
ipb
.
id
,
ipb
.
forwardedip
,
rpad
(
substr
(
ipb
.
IP_BIN
,
0
,
16
),
32
,
'0'
)
ip_16
,
rpad
(
substr
(
ipb
.
IP_BIN
,
0
,
17
),
32
,
'0'
)
ip_17
,
rpad
(
substr
(
ipb
.
IP_BIN
,
0
,
18
),
32
,
'0'
)
ip_18
,
rpad
(
substr
(
ipb
.
IP_BIN
,
0
,
19
),
32
,
'0'
)
ip_19
,
rpad
(
substr
(
ipb
.
IP_BIN
,
0
,
20
),
32
,
'0'
)
ip_20
,
rpad
(
substr
(
ipb
.
IP_BIN
,
0
,
21
),
32
,
'0'
)
ip_21
,
rpad
(
substr
(
ipb
.
IP_BIN
,
0
,
22
),
32
,
'0'
)
ip_22
,
rpad
(
substr
(
ipb
.
IP_BIN
,
0
,
23
),
32
,
'0'
)
ip_23
,
rpad
(
substr
(
ipb
.
IP_BIN
,
0
,
24
),
32
,
'0'
)
ip_24
,
rpad
(
substr
(
ipb
.
IP_BIN
,
0
,
25
),
32
,
'0'
)
ip_25
,
rpad
(
substr
(
ipb
.
IP_BIN
,
0
,
26
),
32
,
'0'
)
ip_26
,
rpad
(
substr
(
ipb
.
IP_BIN
,
0
,
27
),
32
,
'0'
)
ip_27
,
rpad
(
substr
(
ipb
.
IP_BIN
,
0
,
28
),
32
,
'0'
)
ip_28
,
rpad
(
substr
(
ipb
.
IP_BIN
,
0
,
29
),
32
,
'0'
)
ip_29
,
rpad
(
substr
(
ipb
.
IP_BIN
,
0
,
30
),
32
,
'0'
)
ip_30
,
rpad
(
substr
(
ipb
.
IP_BIN
,
0
,
31
),
32
,
'0'
)
ip_31
FROM
reqipbin
ipb
),
iprangecounts
AS
(
SELECT
c
.
id
,
c
.
forwardedip
,
COUNT
(
c
.
ip_16
)
OVER
(
PARTITION
BY
c
.
ip_16
)
AS
count16
,
COUNT
(
c
.
ip_17
)
OVER
(
PARTITION
BY
c
.
ip_17
)
AS
count17
,
COUNT
(
c
.
ip_18
)
OVER
(
PARTITION
BY
c
.
ip_18
)
AS
count18
,
COUNT
(
c
.
ip_19
)
OVER
(
PARTITION
BY
c
.
ip_19
)
AS
count19
,
COUNT
(
c
.
ip_20
)
OVER
(
PARTITION
BY
c
.
ip_20
)
AS
count20
,
COUNT
(
c
.
ip_21
)
OVER
(
PARTITION
BY
c
.
ip_21
)
AS
count21
,
COUNT
(
c
.
ip_22
)
OVER
(
PARTITION
BY
c
.
ip_22
)
AS
count22
,
COUNT
(
c
.
ip_23
)
OVER
(
PARTITION
BY
c
.
ip_23
)
AS
count23
,
COUNT
(
c
.
ip_24
)
OVER
(
PARTITION
BY
c
.
ip_24
)
AS
count24
,
COUNT
(
c
.
ip_25
)
OVER
(
PARTITION
BY
c
.
ip_25
)
AS
count25
,
COUNT
(
c
.
ip_26
)
OVER
(
PARTITION
BY
c
.
ip_26
)
AS
count26
,
COUNT
(
c
.
ip_27
)
OVER
(
PARTITION
BY
c
.
ip_27
)
AS
count27
,
COUNT
(
c
.
ip_28
)
OVER
(
PARTITION
BY
c
.
ip_28
)
AS
count28
,
COUNT
(
c
.
ip_29
)
OVER
(
PARTITION
BY
c
.
ip_29
)
AS
count29
,
COUNT
(
c
.
ip_30
)
OVER
(
PARTITION
BY
c
.
ip_30
)
AS
count30
,
COUNT
(
c
.
ip_31
)
OVER
(
PARTITION
BY
c
.
ip_31
)
AS
count31
FROM
ipcidr
c
)
SELECT
*
FROM
iprangecounts
rc
WHERE
count16
>
1
ORDER
BY
count16
desc
,
count17
desc
,
count18
desc
,
count19
desc
,
count20
desc
;
Event Timeline
stwalkerster
created this paste.
Nov 10 2018, 4:56 PM
2018-11-10 16:56:36 (UTC+0)
stwalkerster
created this object with visibility "Public (No Login Required)".
Log In to Comment