Page MenuHomePhabricator
Files F105229

No One
Actions

Authored By
stwalkerster
Nov 10 2018, 4:56 PM
Size
2 KB
Referenced Files
None
Subscribers
None

View Options

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;

File Metadata

Mime Type
text/plain; charset=utf-8
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
95906
Default Alt Text
(2 KB)

Event Timeline

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