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:

Zitat von Sir Rufo (Beitrag 1282685)

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:

Zitat von Valle (Beitrag 1282697)

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. :stupid:

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. 8-)

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

[jensw_2000]

Wenn es Dir um die Datenbankgröße geht, dann nimm BigInt (feste Größe 4 Byte).
VarChar(n) benötigt einen Speicherplatz von (2 * n Byte) + 2 Byte.

Wenn es nicht um die DB Größe geht ist das reine Geschmackssache.
Überhaupt in der DB speichern? Ja klar. Immer rein damit.

Bei den Minigrößen ist es performanceseitig absolut egal ob BigInt oder VarChar. Auch das bisschen Rechnen in den Funktionen macht den MSSQL garantiert nicht lahm.

Ich würde die Werte vermutlich nicht als BigInt speichern, wenn die IP Adressen aus der Anwendung oft abgerufen und dargestellt werden müssen.

Du kannst meine SQL Funktion IPStringToInt verwenden, um IP Adressen lesbar per String Parameter zu übergeben und als BigInt zu speichern (oder Dir eine eigene, bessere UDF bauen).


PS:
Huch, falsch...
NVarChar(n) benötigt (2 * n Byte) + 2 Byte. Bei VarChar(n) ist es nur (n Byte) + 2 Byte.

[jobo]

Zitat:

Zitat von jensw_2000 (Beitrag 1282799)

BigInt, VarChar, NVarChar, VarChar

Also wenn man sich schon darüber Gedanken macht, möchte ich dann noch den guten alten Char vorschlagen und mindestens von NVarchar abraten, das wäre wirklich Verschwendung. (Außer man komm in die Verlegenheit, die IP in chinesischen Zahlzeichen notieren zu müssen)

[jensw_2000]

Zitat:

Zitat von jobo (Beitrag 1282808)

Zitat:

Zitat von jensw_2000 (Beitrag 1282799)

BigInt, VarChar, NVarChar, VarChar

Also wenn man sich schon darüber Gedanken macht, möchte ich dann noch den guten alten Char vorschlagen und mindestens von NVarchar abraten, das wäre wirklich Verschwendung. (Außer man komm in die Verlegenheit, die IP in chinesischen Zahlzeichen notieren zu müssen)

Richtig. NVarChar ist Unsinn, stand nie wirklich zur Debatte.
Char(15) wäre bei mir aber auch sofort durchgefallen, weil dort jeder abgerufene Datensatz manuell durch die Trim Funktion laufen müsste, bevor die Daten richtig nutzbar werden.

[Dejan Vu]

Zitat:

Zitat von jensw_2000 (Beitrag 1282799)

Wenn es Dir um die Datenbankgröße geht, dann nimm BigInt (feste Größe 4 Byte).

Hüstel. 8.

Wenn über IP-Adressen gesucht wird, und auch über -Bereiche, schreit das erst einmal nach int, CHAR(4) oder BINARY(4). Allerdings würde ich CHAR wirklich nicht empfehlen, da es -abhängig von der Codepage und der Collation bei der Darstellung zu Problemen führen kann. BINARY und INT werden dagegen nicht interpretiert.

INT hat den Vorteil, das man auch auch (bit-)maskieren kann. BINARY hat den Vorteil, das man eine Ordnung aufbauen kann (Index, ORDER BY) und das in der Darstellung (reines SELECT auf dem Server) die Octets sichtbar werden, da im SSMS dieser Datentyp in hex dargestellt wird. Ich tendiere zur Speicherung als BINARY(4), weil das dem Grundformat am nächsten kommt (4 Bytes und eben kein Integer)

Hier eine T-SQL Funktion, die einen String in ein Binary(4) umwandelt:

Code:

create function IPToBinary (@IP VarChar(15))
returns binary (4)
as
begin
  declare @b1 tinyint, @b2 tinyint, @b3 tinyint, @b4 tinyint
  set @b4= cast(PARSENAME(@IP, 4) as tinyint)
  set @b3= cast(PARSENAME(@IP, 3) as tinyint)
  set @b2= cast(PARSENAME(@IP, 2) as tinyint)
  set @b1= cast(PARSENAME(@IP, 1) as tinyint)
  return cast(char(@b4)+char(@b3)+char(@b2)+char(@b1) as binary(4))
end

In deiner Tabelle deklarierst Du dann z.B. so:

Code:

CREATE TABLE [dbo].[MyComputers](
   [IPAddress]  AS ([dbo].[IPToBinary]([IPAddressString])),
   [IPAddressString] [varchar](15) NULL
) ON [PRIMARY]

Du findest dann alle PC im Bereich '123.123.123.0' und '123.124.123.255' mit

Code:

select * 
  from MyComputers
  where IPAddress between dbo.IPToBinary('123.123.123.0') and dbo.IPToBinary ('123.124.123.255')

[jensw_2000]

Ja. Char(4) ist wegen der Codepage und vieler anderer Sachen indiskutabel.
INT ist zu klein. Da passen laut Adam Ries keine 4 Byte rein.

Bei BIGINT vs BINARY(4) musst Du mir mal helfen.
Die sind ja beide exakt 4 Byte groß. Platz spart man damit also nicht (auch wenn die Speicherplatzdiskussion eigentlich nur ein Randthema ist).
Was kann das BINARY Format belegbar besser als das BIGINT Format?
Schneller indizierbar? Schneller abrufbar? Schneller konvertierbar? Besser lesbar?
Da stehe ich ein bisschen auf dem Schlauch ...
Sag mal, warum Du das bevorzugst.

[Zacherl]

Hat er doch schon geschrieben :stupid:

Zitat:

Zitat von jensw_2000 (Beitrag 1282814)

Bei BIGINT vs BINARY(4) musst Du mir mal helfen.
Die sind ja beide exakt 4 Byte groß. Platz spart man damit also nicht (auch wenn die Speicherplatzdiskussion eigentlich nur ein Randthema ist).

Zitat:

Zitat von Dejan Vu (Beitrag 1282812)

Zitat:

Zitat von jensw_2000 (Beitrag 1282799)

Wenn es Dir um die Datenbankgröße geht, dann nimm BigInt (feste Größe 4 Byte).

Hüstel. 8.

Zitat:

Zitat von jensw_2000 (Beitrag 1282814)

Was kann das BINARY Format belegbar besser als das BIGINT Format? [..] Besser lesbar?

Zitat:

Zitat von Dejan Vu (Beitrag 1282812)

BINARY hat den Vorteil, das man eine Ordnung aufbauen kann (Index, ORDER BY) und das in der Darstellung (reines SELECT auf dem Server) die Octets sichtbar werden, da im SSMS dieser Datentyp in hex dargestellt wird. Ich tendiere zur Speicherung als BINARY(4), weil das dem Grundformat am nächsten kommt (4 Bytes und eben kein Integer)

[jensw_2000]

Zitat:

Zitat von Zacherl (Beitrag 1282815)

Hat er doch schon geschrieben :stupid:

Ja, ich brauche noch einen Kaffee.

Dann ist der Vorteil also, dass die binär gespeicherte IP Adresse der abgefragten Ergebnismenge in einigen SQL Editoren als A3.8B.23.F4 angezeigt wird, in Anderen als BLOB(binary) und in wieder Anderen als "1100100...101011101"?
Sortieren und Indizieren mann man Integer, glaube ich, auch ganz gut :stupid:.

[Dejan Vu]

Zitat:

Zitat von jensw_2000 (Beitrag 1282814)

INT ist zu klein. Da passen laut Adam Ries keine 4 Byte rein.

:gruebel: Bist Du dir da wirklich sicher? ;-)

Zitat:

Was kann das BINARY Format belegbar besser als das BIGINT Format?

(BigInt=8 Bytes, siehe Link :mrgreen:) Ich muss hier ja eigentlich nichts belegen, aber... IP-Adressen sind als 4 einzelne Bytes definiert, nicht als eine Zahl. Und für die Repräsentation liegt es daher nahe, genau diese 4 einzelnen Bytes abzulegen und eben nicht eine Zahl. Ob das Format irgend etwas 'belegbar' (oder unbelegbar) besser kann, ist mir hier egal, denn auf beiden Formaten kann ich eine totale Ordnung aufbauen, ergo Bereiche abfragen, sortieren usw.

Wenn Dir Int (bzw. BigInt) lieber ist, bitte sehr. Ich schrieb doch bereits, warum mir der binary-Datentyp hier eher zusagt. Oder vielleicht nicht: INT ist eine Interpretation der 4 Bytes, Binary(4) dagegen nicht. Vielleicht wird es jetzt klarer: Ein rein ästhetischer Grund. Mich persönlich stört bei 'int' das Vorzeichen. Was hat ein '-' mit einer IP-Adresse am Hut? :stupid:

Du kannst auch NChar(2) nehmen, wenn Du es lustig findest. Oder SmallDateTime. Oder Float(1)...Float(24), geht auch (müssten auch 4 Bytes sein). Such Dir was aus, das sind alles Interpretationen von 4-Byte großen Informationen.
Ich würde Binary nehmen. So wie ich Milch und Zucker zum Kaffee nehme (wo wir schon beim Thema sind).

[jensw_2000]

Wirklich, mir ist es auch egal für welche Lösung er sich entscheidet oder ob er überhaupt eine davon nimmt.
Du hast auch recht damit, dass Binary(4) das effektivste Format ist, um die 32 Bit große IP in der DB zu speichern.
Das Vergleichen gegen ein extrem teuer indizierbares berechnetes Feld ist aber ineffektiver Bockmist.

Zitat:

Zitat von Dejan Vu

In deiner Tabelle deklarierst Du dann z.B. so:
markieren Code:

Code:

CREATE TABLE [dbo].[MyComputers](
   [IPAddress] AS ([dbo].[IPToBinary]([IPAddressString])),
   [IPAddressString] [varchar](15) NULL
) ON [PRIMARY]

Zitat:

Zitat von Dejan Vu

Du findest dann alle PC im Bereich '123.123.123.0' und '123.124.123.255' mit
markieren Code:

Code:

select * 
  from MyComputers
  where IPAddress between dbo.IPToBinary('123.123.123.0') and dbo.IPToBinary ('123.124.123.255')
[/QUOTE]

Zudem glaube ich (ohne das jetzt prüfen oder nachlesen zu wollen), dass Delphi ein Binary Feld nicht direkt auslesen kann. Falls die Daten mal außerhalb der DB, in seiner Delphi Anwendung genutzt werden sollen, dann wird er das Feld vermutlich (wie ein Blob) krampfig über einen Stream auslesen müssen. BigInt ginge direkt.

Ich werde mein DP Konto nach diesem Post löschen.
Jedesmal wenn ich mir die Arbeit mache, um für irgendwelche Fragestellungen eine funktionierende Lösung zu erarbeiten und zu posten, habe ich danach stundenlange Klarstellungen und Längenvergleiche am Hals. Seit Jahren findet sich immer jemand, der in allen Posts irgendwelche Bagatellfehler sucht und die investierte Arbeit danach ins Absurdum dreht.
Ich will meine Zeit zukünftig nicht mehr so sinnlos verschwenden.

Ein paar kleine Sachen noch:

Zitat:

Zitat von Dejan Vu (Beitrag 1282857)

Zitat:

Zitat von jensw_2000 (Beitrag 1282814)

INT ist zu klein. Da passen laut Adam Ries keine 4 Byte rein.

:gruebel: Bist Du dir da wirklich sicher? ;-)

Zitat:

Zitat von Dejan Vu (Beitrag 1282857)

Mich persönlich stört bei 'int' das Vorzeichen. Was hat ein '-' mit einer IP-Adresse am Hut? :stupid:

Zitat:

Zitat von Dejan Vu (Beitrag 1282857)

(BigInt=8 Bytes, siehe Link :mrgreen:) Ich muss hier ja eigentlich nichts belegen, aber... IP-Adressen sind als 4 einzelne Bytes definiert, nicht als eine Zahl.

Du hast den richtigen MSDN Artikel gefunden, hast gesehen das der INT Datentyp 4 Byte groß ist, hast auch gesehen INT nicht vorzeichenlos ist, und, Dir ist bewusst, dass IP Adressen per se nicht negativ werden können.

Dann ist deine Aussage "INT geht" fachlich einfach falsch, wenn Du nicht zeitgleich auf die passenden Shift Funktionen verweist.
Der INT hat 2 Byte im positiven und 2 Byte im negativen Bereich.
In dem 2 Byte großen positiven Bereich kann man nun mal keine 4 Byte große positive Zahl hineinschreiben.
Probiere es aus, schau Dir die Exception an und lerne.

Wenn Dein "4 Byte Binary passt natürlich easy in einen 4 Byte INT Datentyp" ein Einsteiger liest, dann bringst Du den zum Verzweifeln. Der glaubt er macht was Anderes falsch, wenn der SQL Server ihm eine "Value out of Range" Exception an den Kopf wirft.
Und ehrlich, alle berechneten Werte nochmal shiften, damit sie doch irgendwie in den INT hineinpassen ... Das ist für mich eher eine Notlösung als eine Lösung.

So, zerreiß meine Klarstellung in der Luft. Ich bin dann mal weg.

[Piro]

Danke an alle.
Ich werde die Lösung von jensw_2000 nehmen, da ich nämlich tatsächlich sehr oft auf die IP Werte in lesbarer Form zugreifen muss.

Auch wenn jensw_2000 leider weg ist, finde ich diese Forum sehr hilfreich.

Schönen Abend noch.

[jobo]

Zitat:

Zitat von jensw_2000 (Beitrag 1282943)

Ich werde mein DP Konto nach diesem Post löschen.
Ich will meine Zeit zukünftig nicht mehr so sinnlos verschwenden.

Auch wenn ich genau in diesem Thread selbst vielleicht ein Sargnagel war, überleg es Dir nochmal.
Es ist sicher in 95% der Fälle Zeitverschwendung, sich um ein paar Bits zu streiten oder deswegen zu rechtfertigen. Erst Recht, wenn dem "Gegenüber" überhaupt nicht bewusst ist, wie unangemessen dessen Einwände sind. Sogar wenn es ihm klar ist, er es aber fürs Ego braucht.

[Dejan Vu]

Zitat:

Zitat von jensw_2000 (Beitrag 1282943)

Das Vergleichen gegen ein extrem teuer indizierbares berechnetes Feld ist aber ineffektiver Bockmist.

Na, der Index ist jetzt nicht direkt teuer, denn er ist ja sozusagen statisch. Und 'Bockmist' finde ich jetzt unangebracht, oder willst du nochmal ausholen? Ich ging auch davon aus, das der TE nicht sämtliche IP-Adressen dieser Welt speichern will. Soweit ich weiß, sind berechnete Felder eh materialisiert, aber ich kann mich da irren.

Zitat:

Zudem glaube ich (ohne das jetzt prüfen oder nachlesen zu wollen), dass Delphi ein Binary Feld nicht direkt auslesen kann.

Das glaube ich auch. Delphi ist nicht mehr mein Thema, muss ich zugeben.

Zitat:

Ich werde mein DP Konto nach diesem Post löschen. ...Jedesmal wenn ich mir die Arbeit mache,

Das nennt sich in einigen Kulturen 'Diskussion' bzw. 'Streit um die beste Lösung'. Zugegebenermaßen nicht jedermanns Sache, aber das ist eigentlich im Leben normal (Streiten um bessere Argumente). Mir ist jetzt auch nicht bewusst, das hier irgend etwas aus dem Ruder gelaufen ist.

Zitat:

...hast gesehen das der INT Datentyp 4 Byte groß ist, hast auch gesehen INT nicht vorzeichenlos ist, und, Dir ist bewusst, dass IP Adressen per se nicht negativ werden können.

Na ja. Es gibt diverse Artikel, die das vorschlagen. Ich würde es nicht machen, aber -nun ja-. Vielleicht ist Int auch keine gute Idee.

Zitat:

In dem 2 Byte großen positiven Bereich kann man nun mal keine 4 Byte große positive Zahl hineinschreiben.
Probiere es aus, schau Dir die Exception an und lerne.

Da eine IP-Adresse keine vorzeichenlose 4-Byte-Zahl ist, sondern '4 Bytes', verstehe ich das nicht. Ich schreibe keine 4 Byte große positive Zahl dort hinein sondern caste 4 Bytes auf ein INT. Aber wie gesagt: Die 'Beweislast' (ist Quatsch, aber du wolltest -glaube ich- belegbare Gründe für irgendwas) liegt bei Dir, das INT besser ist. Oder eben nicht. Auch wurscht. Wichtig ist, das es -wenn man drüber nachdenkt- eine gute Lösung gibt, bei der es eigentlich nur Gewinner gibt.

Zitat:

Ich bin dann mal weg.

Schade, überflüssig, aber na ja: "Tschüss denn".