Chad, I get this message for the 'trivial solution': Msg 537, Level 16, State 3, Line 1 Invalid length parameter passed to the LEFT or SUBSTRING function.
Thx Reto Von: [email protected] [mailto:[email protected]] Im Auftrag von Chad Simmons Gesendet: Freitag, 31. Juli 2015 05:05 An: [email protected] Betreff: RE: [mssms] SQL guru needed... Kehl Try this for the select statement: SELECT CAST(SUBSTRING(IPAddress0, p3 + 1, 3) AS tinyint) as Octet4, IPAddress0, DNSHostName0, ResourceID If you like the "Trivial Solution" method better, this should take care of it. SELECT Left(IPAddress0,CHARINDEX(',',IPAddress0)-1) as FirstIP , CAST(SUBSTRING(Left(IPAddress0,CHARINDEX(',',IPAddress0)-1), CHARINDEX('.', Left(IPAddress0,CHARINDEX(',',IPAddress0)-1), CHARINDEX('.', Left(IPAddress0,CHARINDEX(',',IPAddress0)-1), CHARINDEX('.', Left(IPAddress0,CHARINDEX(',',IPAddress0)-1)) + 1) + 1) + 1, 3) AS tinyint) as Octet4 , IPAddress0, DNSHostName0, ResourceID FROM v_GS_NETWORK_ADAPTER_CONFIGURATION WHERE (v_GS_NETWORK_ADAPTER_CONFIGURATION.IPEnabled0 = 1) ORDER BY CAST(SUBSTRING(IPAddress0, 1, CHARINDEX('.', IPAddress0) - 1) AS tinyint), CAST(SUBSTRING(IPAddress0, CHARINDEX('.', IPAddress0) + 1, CHARINDEX('.', IPAddress0, CHARINDEX('.', IPAddress0) + 1) - CHARINDEX('.', IPAddress0) - 1) AS tinyint), CAST(SUBSTRING(IPAddress0, CHARINDEX('.', IPAddress0, CHARINDEX('.', IPAddress0) + 1) + 1, CHARINDEX('.', IPAddress0, CHARINDEX('.', IPAddress0, CHARINDEX('.', IPAddress0) + 1) + 1) - CHARINDEX('.', IPAddress0, CHARINDEX('.', IPAddress0) + 1) - 1) AS tinyint) ,CAST(SUBSTRING(Left(IPAddress0,CHARINDEX(',',IPAddress0)-1), CHARINDEX('.', Left(IPAddress0,CHARINDEX(',',IPAddress0)-1), CHARINDEX('.', Left(IPAddress0,CHARINDEX(',',IPAddress0)-1), CHARINDEX('.', Left(IPAddress0,CHARINDEX(',',IPAddress0)-1)) + 1) + 1) + 1, 3) AS tinyint) Chad Simmons | Microsoft System Center Configuration Manager Consultant | linkedin.com/in/chadsimmons From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Kehl Reto Sent: Thursday, July 30, 2015 1:28 AM To: '[email protected]' <[email protected]<mailto:[email protected]>> Subject: AW: [mssms] SQL guru needed... Hi Chad, yes this works. many thanks for your help. although the other query would be easier for me to adjust/extend to make it work I "only" need a way to get the part between the third dot and the comma: 192.168.2.124, fe80::284c:ddc5:as0b:8dc8:61df maybe someone has a solution Von: [email protected]<mailto:[email protected]> [mailto:[email protected]] Im Auftrag von Chad Simmons Gesendet: Mittwoch, 29. Juli 2015 20:23 An: [email protected]<mailto:[email protected]> Betreff: RE: [mssms] SQL guru needed... Try this... it worked in my ConfigMgr 2012 lab --Create an in-memory temp table with all desired data --using only the leftmost IP address from the IPAddress array CREATE TABLE #cmIPs(resourceID int not null, IPAddress0 varchar(15) NOT NULL, DNSHostName0 nvarchar(255)); INSERT INTO #cmIPs -- (resourceID, IPAddress0) select ResourceID, IPAddress0, DNSHostName0 from v_GS_NETWORK_ADAPTER_CONFIGURATION WHERE v_GS_NETWORK_ADAPTER_CONFIGURATION.IPEnabled0=1 and CHARINDEX(',',IPAddress0)=0 UNION select ResourceID, Left(IPAddress0,CHARINDEX(',',IPAddress0)-1), DNSHostName0 from v_GS_NETWORK_ADAPTER_CONFIGURATION WHERE v_GS_NETWORK_ADAPTER_CONFIGURATION.IPEnabled0=1 and CHARINDEX(',',IPAddress0)>0 --Use the Derived Tables solution from http://sqlmag.com/t-sql/sorting-ip-addresses to sort by IPv4 IPAddress SELECT IPAddress0, DNSHostName0, ResourceID FROM (SELECT *, CHARINDEX('.', IPAddress0, p2+1) AS p3 FROM (SELECT *, CHARINDEX('.', IPAddress0, p1+1) AS p2 FROM (SELECT IPAddress0, CHARINDEX('.', IPAddress0) AS p1, DNSHostName0, ResourceID FROM #cmIPs) AS D1) AS D2) AS D3 ORDER BY CAST(SUBSTRING(IPAddress0, 1, p1 - 1 ) AS tinyint), CAST(SUBSTRING(IPAddress0, p1 + 1, p2 - p1 - 1) AS tinyint), CAST(SUBSTRING(IPAddress0, p2 + 1, p3 - p2 - 1) AS tinyint), CAST(SUBSTRING(IPAddress0, p3 + 1, 3 ) AS tinyint); Chad Simmons | Microsoft System Center Configuration Manager Consultant | linkedin.com/in/chadsimmons From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Kehl Reto Sent: Wednesday, July 29, 2015 9:52 AM To: '[email protected]' <[email protected]<mailto:[email protected]>> Subject: AW: [mssms] SQL guru needed... that's the one I'm using ("Trivial Solution") but it is not working when there is an ipv6 address the length of the last octet should end at the comma 192.168.2.4, fe80::284c:ddc5:as0b:8dc8:61df Von: [email protected]<mailto:[email protected]> [mailto:[email protected]] Im Auftrag von Chad Simmons Gesendet: Mittwoch, 29. Juli 2015 16:40 An: [email protected]<mailto:[email protected]> Betreff: RE: [mssms] SQL guru needed... Checkout http://sqlmag.com/t-sql/sorting-ip-addresses which has several methods of accomplishing it. Hopefully one will work for you. Chad Simmons | Microsoft System Center Configuration Manager Consultant | linkedin.com/in/chadsimmons From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Kehl Reto Sent: Wednesday, July 29, 2015 9:23 AM To: [email protected]<mailto:[email protected]> Subject: [mssms] SQL guru needed... need sql query for ip addresses sorted ascending but can't get the 4th octet SELECT IPAddress0, DNSHostName0 FROM v_GS_NETWORK_ADAPTER_CONFIGURATION WHERE (v_GS_NETWORK_ADAPTER_CONFIGURATION.IPEnabled0 = 1) ORDER BY CAST(SUBSTRING(IPAddress0, 1, CHARINDEX('.', IPAddress0) - 1) AS tinyint), CAST(SUBSTRING(IPAddress0, CHARINDEX('.', IPAddress0) + 1, CHARINDEX('.', IPAddress0, CHARINDEX('.', IPAddress0) + 1) - CHARINDEX('.', IPAddress0) - 1) AS tinyint), CAST(SUBSTRING(IPAddress0, CHARINDEX('.', IPAddress0, CHARINDEX('.', IPAddress0) + 1) + 1, CHARINDEX('.', IPAddress0, CHARINDEX('.', IPAddress0, CHARINDEX('.', IPAddress0) + 1) + 1) - CHARINDEX('.', IPAddress0, CHARINDEX('.', IPAddress0) + 1) - 1) AS tinyint) --CAST(SUBSTRING(IPAddress0, CHARINDEX('.', IPAddress0, CHARINDEX('.', IPAddress0, CHARINDEX('.', IPAddress0) + 1) + 1) + 1, 3) AS tinyint) the last CAST(SUBSTRING(.... gives error when activated. I can't get it, way too complicated for me.. anybody?
