SQL Select gesucht - IP Adresse zwischen IP Min und IP Max

[Piro]

Moin zusammen,

ich habe 2 MS SQL Tabelle. Die 1. Tabelle hält Computer Information mit der IP Adresse (z.B. 192.168.29.29). Die 2. Tabelle hat die Informationen über die Bereiche mit Zusatzinformation, wie dem Standort (z.B.: Gebäude, Raum).

Definition der 1. Tabelle für die IP:

Code:

name varchar(20) null,
ip varchar(15) null

// Beispieldaten
Name        | IP
----------------------------------
Computer1  | 192.168.99.29
Computer2  | 192.168.99.250
Computer3  | 192.168.100.29
Computer4  | 192.168.100.225

Definition der 2. Tabelle:

Code:

ip_min varchar(15) null,
ip_max varchar(15) null,
location varchar(50) null

// Beispieldaten
IP Min           | IP Max           | Location
--------------------------------------------
192.168.99.1    | 192.168.99.125  | Raum 1
192.168.99.126  | 192.168.100.125 | Raum 2
192.168.100.126 | 192.168.100.254 | Raum 3

Leider fehlt mir das Wissen, wie man sowas machen kann. Mit Google habe ich einige Sachen gefunden aber nicht nachvollziehen können.
Ein Ansatz wäre, dass man die IP Adressen in Integer umwandelt und dann folgendes macht.

Code:

Select t1.name, t1.ip, t2,location from table1 as t1, table2 as t2 where t1.ip between t2.ip_min and t2.ip_max

Nur wie wandle ich die IPs richtig um? Das 3. Oktett ist zum Beispiel 2stellig und könnte auch 3stellig sein.

Folgendes Ergebnis benötige ich, wenn das Select Statement gefunden ist.

Code:

Computer1 | 192.168.99.29   | Raum 1
Computer2 | 192.168.99.250  | Raum 2
Computer3 | 192.168.100.29  | Raum 2
Computer4 | 192.168.100.225 | Raum 3

Vielen Dank im Voraus für eure Ideen oder Anregungen.

Sven

[Sir Rufo]

Die IP-Adresse (so wie landläufig bekannt) ist ja nur ein wenig human readable gestaltet. Es sind schlicht 4 Bytes. Diese Bytes sind dezimal dargestellt und jeweils mit Punkten getrennt. Du musst die IP-Adresse also an den Punkten auseinander pflücken und dann bekommst du die 4 Byte-Werte und kannst daraus einen Integer-Wert bauen.

Sinnvoller wäre die Adresse gleich als Integer zu speichern, denn aus dem echten Wert lässt sich immer einfacher die Darstellung ableiten, als aus der Darstellung der echte Wert.

[Valle]

Zitat von Sir Rufo:

Sinnvoller wäre die Adresse gleich als Integer zu speichern

Und noch sinnvoller wäre es, wenn man außerdem die Adressbereiche mit Subnetzmasken speichern würde.

Aber ich vermute du hast keinen Einfluss darauf?

[Uwe Raabe]

Zitat von Valle:

Und noch sinnvoller wäre es, wenn man außerdem die Adressbereiche mit Subnetzmasken speichern würde.

Na dann, viel Spaß bei

Zitat:

192.168.99.126 | 192.168.100.125 | Raum 2

[Valle]

Deshalb meinte ich ja, dass er da wohl keinen Einfluss drauf haben wird.

Kommt halt davon, wenn man sich nicht an etablierte Standards hält. Jetzt muss man da halt durch.

Aber wie schon erwähnt wurde, kann man die IPs einfach in Ints umrechnen und dann ganz leicht vergleichen.

Edit: Übrigens nennt man die Funktionen dafür inet_ntoa und inet_aton. Hilft sicher beim googlen!

[hathor]

http://delphi.cjcsoft.net/viewthread.php?tid=43860

RtlIpv4StringToAddress function

http://msdn.microsoft.com/en-us/libr...=vs.85%29.aspx
-----------------------
inet_addr function

http://msdn.microsoft.com/en-us/libr...8VS.85%29.aspx

http://blogs.msdn.com/b/johnbreakwel...ord-value.aspx

[jobo]

Code:

select 
  right('000'+ cast(
  substring(ip,1, charindex('.',ip)-1)
  as varchar),3),
  right('000'+ cast(
  substring(ip, charindex('.',ip)+1, 
                charindex('.',ip,charindex('.',ip)+1)-charindex('.',ip)-1)
  as varchar),3)
from (select '92.68.1.122' as ip) x

Den 3. und 4. Wert kannst Du sicher selbst konstruieren.
Eine Funktion wäre natürlich elegantger.
Am Ende die 4 Werte verketten und man kann mit <> between arbeiten. Schnell wird das aber wahrscheinlich nicht ohne weiteres, Standardindex greift da nicht.

[jensw_2000]

Schnell mal gebastelt ..


SQL Funktion "IPAddressBetween(Adresse, Bereich-Beginn, Bereich-Ende)"

Delphi-Quellcode:

SELECT dbo.IPAddressBetween('192.168.10.10','192.168.10.11','192.168.10.100');
 >> 0

SELECT dbo.IPAddressBetween('192.168.10.10','192.168.10.1','192.168.10.100');
 >> 1

In der DB musst Du dafür einmalig 2 Skalarwertfunktionen anlegen.
Vorab .. Ja. Das Errorhandling bei Übergabe falscher Werte kannst Du selbst einbauen.

Code:

CREATE FUNCTION [dbo].[IPStringToInt](
   @IPAddressString VARCHAR(15)
   )
RETURNS BIGINT
BEGIN

   declare @Oktett1 TinyInt;
   declare @Oktett2 TinyInt;
   declare @Oktett3 TinyInt;
   declare @Oktett4 TinyInt;

   declare @OktettWert1 BIGINT;
   declare @OktettWert2 BIGINT;
   declare @OktettWert3 BIGINT;
   declare @OktettWert4 BIGINT;

   declare @result BIGINT = -1;

    SELECT @Oktett1 = PARSENAME(@IPAddressString,4);
   SELECT @Oktett2 = PARSENAME(@IPAddressString,3);
   SELECT @Oktett3 = PARSENAME(@IPAddressString,2);
   SELECT @Oktett4 = PARSENAME(@IPAddressString,1);

   SET @OktettWert1 = POWER(@Oktett1,4)
   SET @OktettWert2 = POWER(@Oktett2,3)
   SET @OktettWert3 = POWER(@Oktett3,2)
   SET @OktettWert4 = POWER(@Oktett4,1)

   SET @result = @OktettWert1 + @OktettWert2 + @OktettWert3 + @OktettWert4;

return @result;
   
END

Code:

CREATE FUNCTION [dbo].[IPAddressBetween]
(
   @IPAddress Varchar(15),
   @RangeStart VARCHAR(15),
   @RangeEnd VARCHAR(15)
)
RETURNS Bit
AS
BEGIN
  declare @IPValue BIGINT = dbo.IPStringToInt(@IPAddress)
  declare @RangeStartValue BIGINT = dbo.IPStringToInt(@RangeStart)
  declare @RangeEndValue BIGINT = dbo.IPStringToInt(@RangeEnd)

  declare @result BIT

  IF (@IPValue >= @RangeStartValue) AND (@IPValue <= @RangeEndValue)
    set @result = 1
  ELSE 
    set @result = 0

  RETURN @result
END

[Piro]

Vielen Dank für die ganzen Anregungen und Hilfestellungen. Dann werde ich mich mal ran machen und die beste Lösung für mich versuchen zu finden.

[Piro]

Mal ein anderer Ansatz.

Wie wäre es denn am Besten, die IP Adresse in einem MS SQL Server 2008 zu speichern?

Meine App ermittelt die IP Adresse des Rechners im Format xxx.xxx.xxx.xxx (kann auch mal xxx.xx.xxx.xx sein).

Byte (4)? Welchem MS SQL 2008 Datentyp entspricht es?

Wenn ja wie wandelt man denn dann zum Beispiel 172.28.111.29 um? Oder doch lieber ein BigInt?

Vielen Dank im Voraus.

Sven