docusnap-dude
Goto Top

MSSQL Substring

Hallo,

heute habe ich folgende Hearusforderung:

Tabelle1 (Netzwerkbereichsdefintionen)
Ip-Bereiche Name
192.168.0.0/24 Standardnetz
10.27.0.0/16 Servernetz
172.16.4.0/24 DMZ

Tabelle 2 (Geräteliste)
Gerätename IP-Adresse
Server1 192.168.0.16
Server2 10.27.0.12
Server3 172.16.4.4
Server4 10.27.0.13

Ziel nun in SQL: gib mir Gerätenamen und Netzwerkname der Geräte aus, welche sich im gleichen Netz befinden.

Es sollte also wie folgt aussehen im Resultat:
Netzwerkname Gerätename
Standardnetz Server1
Servernetz Server2
Servernetz Server4
DMZ Server3


Jetzt habe ich aber gerade eine Barrikade im Kopf, bin auch nicht so Deep-Dive SQL-Spezi. Hier mal mein Ansatz:

Select 
	 Table01.Spalte2 AS NetzwerkName
	,Table2.Spalte1 AS Gerätename
FROM
	Table1 TAB1	LEFT JOIN Table2 TAB2
		ON TAB1.Spalte1 =** //???//**

Wo ich jetzt komplett im Kopf fest hänge:
  • Ich müsste jetzt eine Unterabfrage bauen, in welchem ich teile des Netzwerkbereiches gegen die IP des Geräte vergleiche.
    • Problem: die Schreibweise der IP-Adressen, ich muss ausschliessen das jemand "010.027.x.x" schreibt anstelle "10.27..x.x" (volle IPSchreibweise vs. Kurzform)
    • Sicherlich Substring's, aber wie kombinieren?
  • als Trennkriterium könnte man in Tabelle 1 Spalte 1 die Punkte oder den Slash nutzen (also /24) z.b. aber wie am besten
  • Im Join vergleichen: was gegen was

Wie gesagt, komplett irgendwie im Kopf verrant gerade dabei, eventuell sehe ich den Wald vor Bäumen nicht. Daher danke für jegliche Unterstützung beim Baumfällen face-smile

F.

Content-Key: 576189

Url: https://administrator.de/contentid/576189

Ausgedruckt am: 19.03.2024 um 06:03 Uhr

Mitglied: em-pie
em-pie 31.05.2020 aktualisiert um 12:19:22 Uhr
Goto Top
Moin,

kurzte Frage:
welches DBMS ist im EInsatz, auch welche Version.
  • MS SQL 2008R2 fehlen Beispielsweise ggü. MS SQL 2012 manche integrierten Funktionen.
  • mySQL ist dann an manchen Stellen noch mal ganz anders.
  • 'ne DB2 kennt z.B. kein CONCAT()

Mit den Angaben kann man gezielter helfen...


€dit:
bei MS SQL gibt es die Funktion PARSENAME().
Schaue dir die einmal an:
https://docs.microsoft.com/de-de/sql/t-sql/functions/parsename-transact- ...
https://stackoverflow.com/questions/33717815/how-to-compare-ip-addresses ...
€dit2:
RegEx wäre auch ggf. eine Möglichkeit:
https://www.sqlshack.com/t-sql-regex-commands-in-sql-server/

Gruß
em-pie
Mitglied: ukulele-7
ukulele-7 02.06.2020 um 09:56:28 Uhr
Goto Top
Mit Substring ist das sehr müßig, du müsstest für jeden Punkt wieder die Substrings verschachteln, du kannst nicht einfach sagen 2ter Punkt bis 3ter Punkt. Aber bitte erstmal SQL Version posten bevor man sich die Arbeit macht.
Mitglied: DocuSnap-Dude
DocuSnap-Dude 04.06.2020 um 10:21:26 Uhr
Goto Top
Zitat von @em-pie:

Moin,

kurzte Frage:
welches DBMS ist im EInsatz, auch welche Version.
  • MS SQL 2008R2 fehlen Beispielsweise ggü. MS SQL 2012 manche integrierten Funktionen.

Hi, es ist ne SQL2017 (mindestens aber immer 2014) eben weil ich Concat etc benötige.

Dnake dir, ich lese mich mal rein.

Grüße!
Mitglied: DocuSnap-Dude
DocuSnap-Dude 04.06.2020 um 10:22:40 Uhr
Goto Top
Zitat von @ukulele-7:

Mit Substring ist das sehr müßig, du müsstest für jeden Punkt wieder die Substrings verschachteln, du kannst nicht einfach sagen 2ter Punkt bis 3ter Punkt. Aber bitte erstmal SQL Version posten bevor man sich die Arbeit macht.

Jepp, Substring habe ich bemerkt wird zu üppig und wirr. Daher halt mein Balken vor dem Kopf face-wink. MS-SQL Version >2014, in der regel 2017 aufwärts.

Grüße!
Mitglied: ukulele-7
Lösung ukulele-7 04.06.2020 um 22:22:42 Uhr
Goto Top
Zitat von @DocuSnap-Dude:

Jepp, Substring habe ich bemerkt wird zu üppig und wirr. Daher halt mein Balken vor dem Kopf face-wink.

Ich muss zugeben ich habe das unterschätzt, eine in meinem Kopf erstmal total elegante Lösung führt zu, sagen wir, einer show of force in Sachen SQL mit cte, xml, cross apply und irgendwas was ich selbst noch nichtmal checke mir aber binäre Ziffern liefert. Das in einem Abwasch zu "joinen" geht nicht wenn man da noch durchblicken will, daher habe ich eine Funktion gebaut.
CREATE FUNCTION dbo.test_convert_ipv4(@ipv4cidr_decimal VARCHAR(18))
RETURNS @t TABLE (
	ipv4_binary VARCHAR(32),
	ipv4_binary_net VARCHAR(32),
	ipv4_binary_host VARCHAR(32),
	ipv4cidr_decimal_net SMALLINT
)
AS
BEGIN
	DECLARE	@ipv4cidr_decimal_net SMALLINT,
			@ipv4_decimal VARCHAR(15)

	IF		@ipv4cidr_decimal LIKE '%/%'  
	BEGIN
		SET		@ipv4_decimal = left(@ipv4cidr_decimal,charindex('/',@ipv4cidr_decimal)-1) + '.'  
		SET		@ipv4cidr_decimal_net = convert(SMALLINT,right(@ipv4cidr_decimal,charindex('/',reverse(@ipv4cidr_decimal))-1))  
	END
	ELSE
	BEGIN
		SET		@ipv4_decimal = @ipv4cidr_decimal + '.'  
	END;

	WITH cte(ipv4,octet,number) AS (
		SELECT	right(@ipv4_decimal,datalength(@ipv4_decimal)-charindex('.',@ipv4_decimal)),  
				left(@ipv4_decimal,charindex('.',@ipv4_decimal)-1),  
				1
		UNION ALL
		SELECT	right(ipv4,datalength(ipv4)-charindex('.',ipv4)),  
				left(ipv4,charindex('.',ipv4)-1),  
				number + 1
		FROM	cte
		WHERE	number <= 3
		)
	INSERT INTO @t(ipv4_binary)
	SELECT	stuff((	SELECT	( CASE convert(INT,octet) & 128 WHEN 128 THEN '1' ELSE '0' END ) +  
							( CASE convert(INT,octet) & 64 WHEN 64 THEN '1' ELSE '0' END ) +  
							( CASE convert(INT,octet) & 32 WHEN 32 THEN '1' ELSE '0' END ) +  
							( CASE convert(INT,octet) & 16 WHEN 16 THEN '1' ELSE '0' END ) +  
							( CASE convert(INT,octet) & 8 WHEN 8 THEN '1' ELSE '0' END ) +  
							( CASE convert(INT,octet) & 4 WHEN 4 THEN '1' ELSE '0' END ) +  
							( CASE convert(INT,octet) & 2 WHEN 2 THEN '1' ELSE '0' END ) +  
							( CASE convert(INT,octet) & 1 WHEN 1 THEN '1' ELSE '0' END )  
					FROM	cte
					ORDER BY number
					FOR XML PATH (''))  
					,1,1,'')  

	IF		@ipv4cidr_decimal_net IS NOT NULL
	BEGIN
		UPDATE	@t
		SET		ipv4_binary_net = left(ipv4_binary,@ipv4cidr_decimal_net),
				ipv4_binary_host = right(ipv4_binary,32-@ipv4cidr_decimal_net),
				ipv4cidr_decimal_net = @ipv4cidr_decimal_net
	END

	RETURN;
END
Zu der Funktion muss man ganz klar sagen das noch eine Syntax- und Plausibilitätsprüfung sinnvoll wäre. Weil sonst Shit in, Fehler out.

Mit der Funktion lassen sich deine Testdaten erweitern und dann gegeneinander abgleichen:
WITH Netzwerkbereichsdefintionen(IP_Bereich,Name) AS (
	SELECT	'192.168.0.0/24','Standardnetz' UNION ALL  
	SELECT	'10.27.0.0/16','Servernetz' UNION ALL  
	SELECT	'172.16.4.0/24','DMZ'  
	), Geräteliste(Gerätename,IP_Adresse) AS (
	SELECT	'Server1','192.168.0.16' UNION ALL  
	SELECT	'Server2','10.27.0.12' UNION ALL  
	SELECT	'Server3','172.16.4.4' UNION ALL  
	SELECT	'Server4','10.27.0.13'  
	), Netzwerkbereichsdefintionen_erweitert(IP_Bereich,Name,ipv4_binary_net,ipv4cidr_decimal_net) AS (
	SELECT	IP_Bereich,Name,ipv4_binary_net,ipv4cidr_decimal_net
	FROM	Netzwerkbereichsdefintionen
	CROSS APPLY dbo.test_convert_ipv4(IP_Bereich)
	), Geräteliste_erweitert(IP_Adresse,Gerätename,ipv4_binary) AS (
	SELECT	IP_Adresse,Gerätename,ipv4_binary
	FROM	Geräteliste
	CROSS APPLY dbo.test_convert_ipv4(IP_Adresse)
	)
SELECT	*
FROM	 Netzwerkbereichsdefintionen_erweitert nets
LEFT JOIN Geräteliste_erweitert hosts
ON		nets.ipv4_binary_net = left(hosts.ipv4_binary,nets.ipv4cidr_decimal_net)
Theoretisch könnten sich Adressen in mehreren Netzen befinden wenn diese sich überschneiden. Oder zu Adressen gibt es kein Netz dann würden sie wegen LEFT JOIN nicht gelistet, ich vermute aber mal das kommt beides nicht vor in deinen Daten.
Mitglied: DocuSnap-Dude
DocuSnap-Dude 08.06.2020 um 13:29:23 Uhr
Goto Top
Danke dir, du bist mein Held! Damit komme ich weiter!