ORACLE SQL: IP Adresinin Dahil Olduğu IP Bloğunu Bulma

  Şubat 03, 2023      Serkan YILDIZ

 Database'de bulunan bir tablonun ilgili kolonunda listelenmiş network blokları içinde (IP_Adresi/Subnet) bir IP adresinin hangi bulağa ait olduğunu bulmak için aşağıdaki sorgu kullanılabilir

Burada database kolonunda bulunan veri ör:IP_RANGE=192.168.1.0/30 şeklinde arana IP adresi ör:IP_Adresi=192.168.1.3 olabilir. Bu durumda sorgumuz;

WITH subnet_table AS (
  SELECT IP_RANGE,  regexp_substr(IP_RANGE, '(.*)/', 1, 1, null, 1) as network, 
         to_number(regexp_substr(IP_RANGE, '/(.*)', 1, 1, null, 1)) as subnet_mask_length
  FROM Tablo_adi
)
SELECT IP_RANGE, network, subnet_mask_length
FROM subnet_table
WHERE 
to_number(regexp_substr('IP_Adresi', '\d+', 1, 1)) * 16777216 +
to_number(regexp_substr('IP_Adresi', '\d+', 1, 2)) * 65536 +
to_number(regexp_substr('IP_Adresi', '\d+', 1, 3)) * 256 +
to_number(regexp_substr('IP_Adresi', '\d+', 1, 4))
BETWEEN 
to_number(regexp_substr(network, '\d+', 1, 1)) * 16777216 +
to_number(regexp_substr(network, '\d+', 1, 2)) * 65536 +
to_number(regexp_substr(network, '\d+', 1, 3)) * 256 +
to_number(regexp_substr(network, '\d+', 1, 4))
AND 
to_number(regexp_substr(network, '\d+', 1, 1)) * 16777216 +
to_number(regexp_substr(network, '\d+', 1, 2)) * 65536 +
to_number(regexp_substr(network, '\d+', 1, 3)) * 256 +
to_number(regexp_substr(network, '\d+', 1, 4)) + power(2, 32 - subnet_mask_length) - 1
ORDER BY subnet_mask_length DESC

Bu sorguda;

subnet_table : Tablonun kolonunda bulunan IP_RANGE=192.168.1.0/30 şeklindeki veriyi network=192.168.1.0 ve subnet_mask_length=30 şekline dönüştürür.

Sorgu ise IP_Adresi ve subnet_table'dan gelen network bilgilerini Decimal IP adresine dönüştürerek en küçün network IP'sinden en büyük network IP'si aralığında IP_Adresi bilgisinin olup olmadığına bakar.


subnet_table kısmı aşağıdaki gibi de yazılabilir.

select substr(IP_RANGE,1,length(IP_RANGE)-3) AS network, substr(IP_RANGE,-2,2) AS subnet_mask_length FROM Tablo_adi

veya

select substr(IP_RANGE, 1, INSTR(IP_RANGE, '/') - 1) as network, SUBSTR(IP_RANGE, INSTR(IP_RANGE, '/') + 1) as subnet_mask_length FROM Tablo_adi



Google